Bug #18326 Do not lock table for writing during prepare of statement
Submitted: 18 Mar 2006 12:02 Modified: 13 Apr 2007 17:47
Reporter: Mats Kindahl Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Prepared statements Severity:S3 (Non-critical)
Version:5.1 OS:Any (ALL)
Assigned to: Tomash Brechko CPU Architecture:Any
Tags: locking, prepared statement, rt_q1_2007

[18 Mar 2006 12:02] Mats Kindahl
Description:
During the prepare phase of preparing a statement, all tables are locked for writing even though it's not necessary.

How to repeat:
Read the code

Suggested fix:
Use TL_READ when locking the table during the prepare phase.
[18 Mar 2006 12:06] Mats Kindahl
This bug affects the current implementation of row-based replication as well, since a table map is written whenever a table is opened for writing. It causes the generation of extreneous table maps in the binlog, which can easily be avoided by fixing this bug.
[6 Feb 2007 16:23] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/19412

ChangeSet@1.2417, 2007-02-06 19:15:26+03:00, kroki@moonlight.home +1 -0
  BUG#18326: Do not lock table for writing during prepare of statement
  
  During statement prepare phase the tables were locked as if the
  statement is being executed, however this is not necessary.
  
  The solution is to not lock tables on statement prepare phase.
  Opening tables is enough to prevent DDL on them, and during statement
  prepare we do not access nor modify any data.
[15 Feb 2007 13:26] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/19942

ChangeSet@1.2417, 2007-02-15 16:18:21+03:00, kroki@moonlight.home +3 -0
  BUG#18326: Do not lock table for writing during prepare of statement
  
  During statement prepare phase the tables were locked as if the
  statement is being executed, however this is not necessary.
  
  The solution is to not lock tables on statement prepare phase.
  Opening tables is enough to prevent DDL on them, and during statement
  prepare we do not access nor modify any data.
[19 Feb 2007 19:50] Konstantin Osipov
Approved by email. Asked to add a few more asserts, otherwise the patch is OK to push.
[19 Feb 2007 19:57] Konstantin Osipov
Approved by email. Asked to add a few more asserts, otherwise the patch is OK to push.
[7 Mar 2007 16:00] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/21376

ChangeSet@1.2417, 2007-03-07 18:51:49+03:00, kroki@moonlight.home +3 -0
  BUG#18326: Do not lock table for writing during prepare of statement
  
  During statement prepare phase the tables were locked as if the
  statement is being executed, however this is not necessary.
  
  The solution is to not lock tables on statement prepare phase.
  Opening tables is enough to prevent DDL on them, and during statement
  prepare we do not access nor modify any data.
[7 Mar 2007 17:07] Tomash Brechko
Queued to 5.1-runtime.
[13 Mar 2007 18:24] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/21834

ChangeSet@1.2464, 2007-03-13 21:15:29+03:00, kroki@moonlight.home +1 -0
  Fix the bug introduced with the push of the fix for bug#18326: Do not
  lock table for writing during prepare of statement.
  
  When single call open_normal_and_derived_tables() was used, we never
  set table_count to the right value.  This patch reverts the part of
  the old code that does open_tables() (and sets table_count), then
  checks if table_list->multitable_view is set (and returns if so, using
  table_count value), and only then it does mysql_handle_derived().
[15 Mar 2007 21:33] Konstantin Osipov
Mats, a note on this bug: multi-update can still issue lock_tables.
Is it critical for replication?
[22 Mar 2007 21:22] Konstantin Osipov
Fixed in 5.1.17
[13 Apr 2007 17:47] Paul Dubois
Noted in 5.1.17 changelog.

While preparing prepared statements, the server acquired unnecessary
table write locks.