Bug #76728 | reduce lock_sys->mutex contention for transaction that only contains SELECT | ||
---|---|---|---|
Submitted: | 17 Apr 2015 4:48 | Modified: | 3 Jun 2016 19:58 |
Reporter: | zhai weixiang (OCA) | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
Version: | 5.7.7 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[17 Apr 2015 4:48]
zhai weixiang
[27 Jan 2016 17:13]
MySQL Verification Team
Hi Zhai, Thank you for your bug report. Unfortunately, this is not a bug. In spite of the fact that only read queries are utilized, many mutexes still have to be locked. First of all, some of your queries might not be in auto-commit mode or might have locking used due to the choice of the isolation level used. In that case, many locks are used. Next, regardless of the queries being read-only, bin-logging might be turned on and that would carry couple of other mutex locking / unlocking. Even if you do not need anything of this, a program might access some global data, just for reading, and during those reads, no writes could be possible permitted !!!! How is that achieved ?? By mutex locking and unlocking. Next, thread-local data are definitely changed and again we have locking / unlocking. A SELECT query resolution might need the utilization of temporary tables(s), whose creation and removal again require locking / unlocking. If you only read from InnoDB table, you have to lock a specific mutex, which would prevent that the same set of pages is written over by some other connection. You have other mutexes here. Like using adaptive hash index, must also prevent even partial writes of it until reading is over. If the query utilizes indices, then each index tree used must not be written to, until reading is over. This can of course depend on isolation level. There are other examples, but I think this is enough ....
[28 Jan 2016 0:00]
zhai weixiang
hI, Sinisa I guess what you want to point out is read-only transaction could hold transaction locks too . But the diff will check this condition to reduce lock requirement of lock_sys->mutex. if ((trx->lock.n_rec_locks == 0) ----- doesn't hold any record locks + && (trx->lock.table_locks.size() == 0) ---- doesn't hold any table locks + && (trx->rsegs.m_redo.rseg == 0)) { only these conditions be satisfied, we will skip requiring lock_sys->mutex and return directly from function lock_trx_release_locks
[28 Jan 2016 14:40]
MySQL Verification Team
Hi! Actually, your patch, as is, can not be applied, because read-only queries might require temporary table(s) in order to be resolved. And temporary table require locks for the undo logs. However, it is possible to add additional conditions, under which no locks would be used. This would be a small improvement that we can work on. Hence, this bug is now verified.
[3 Jun 2016 19:58]
Daniel Price
Posted by developer: Fixed as of the upcoming 5.7.14 release, and here's the changelog entry: In READ COMMITTED isolation level, InnoDB unnecessarily acquired the lock_sys mutex at COMMIT for a transaction block consisting of read-only SELECT statements. Thanks to Zhai Weixiang for the patch.