Bug #42751 TRIGGERs cause table locks in Cluster

• Submitted: 11 Feb 2009 7:49
• Modified: 15 Feb 2009 20:18
• Reporter: Johan Andersson
• Status: Closed
• Category: MySQL Cluster: Cluster (NDB) storage engine
• Severity: S3 (Non-critical)
• Version: * (all that has triggers)
• OS: Any
• Assigned to: Tomas Ulin
• CPU Architecture: Any
• Tags: table lock, trigger

[11 Feb 2009 7:49] Johan Andersson

Description:
Related bugs:
http://bugs.mysql.com/bug.php?id=16229
http://bugs.mysql.com/bug.php?id=18135

When a Trigger is fired it is causing a table lock to be taken on the table it is going to (update, in case of a update trigger..).

I have no code to put here at this point in time, but it is easy to reproduce:

create a table  T1
create an update trigger that fires on T1 causing an update on T1.
Insert some random data.
Do updates on the random keys.

Probably you can use the data in bug report:
http://bugs.mysql.com/bug.php?id=18135

SHOW PROCESSLIST and Table_locks_waited will be incremented.

How to repeat:
see above

[11 Feb 2009 7:57] Bernd Ocklin

probably a duplicate of 42474.

[11 Feb 2009 8:32] Jonas Oreland

IRC log 2009-02-10 15:54
jonas dlenev: short question regarding triggers, 1) they seem to use table-locks, is this correct? 2) is it possible to avoid it somehow
dlenev jonas: InnoDb and others simply downgrade those locks to something else
dlenev re correctness it is a tough question
jonas dlenev: thx! that means that we (maybe) can too
dlenev jonas: I think that for NDB it should be fairly safe... as you don't support SBR anyway
dlenev and these locks are there mostly due to SBR
jonas dlenev: ahh, thx again/more
dlenev jonas: so you could report a bug against runtime area if you like

[11 Feb 2009 14:59] Tomas Ulin

=== modified file 'sql/ha_ndbcluster.cc'
--- sql/ha_ndbcluster.cc        2009-02-09 13:28:30 +0000
+++ sql/ha_ndbcluster.cc        2009-02-11 14:58:51 +0000
@@ -4386,8 +4386,11 @@ THR_LOCK_DATA **ha_ndbcluster::store_loc
     /* Since NDB does not currently have table locks
        this is treated as a ordinary lock */
 
+    const bool in_lock_tables = thd_in_lock_tables(thd);
+    const uint sql_command = thd_sql_command(thd);
     if ((lock_type >= TL_WRITE_CONCURRENT_INSERT &&
-         lock_type <= TL_WRITE) && !thd->in_lock_tables)      
+         lock_type <= TL_WRITE) &&
+        !(in_lock_tables && sql_command == SQLCOM_LOCK_TABLES))
       lock_type= TL_WRITE_ALLOW_WRITE;
     
     /* In queries of type INSERT INTO t1 SELECT ... FROM t2 ...

[11 Feb 2009 20:51] Johan Andersson

Yes!
This works very well. 
Thank you!

[12 Feb 2009 17:08] 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/66064

2817 Tomas Ulin	2009-02-12
      bug#42751 : TRIGGERs cause table locks in Cluster

[12 Feb 2009 17:09] Bugs System

Pushed into 5.1.31-ndb-6.2.17 (revid:[e-mail]-20090212170826-3dsaql7f2esrhtmx) (version source revid:[e-mail]-20090212170826-3dsaql7f2esrhtmx) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)

[12 Feb 2009 17:16] Bugs System

Pushed into 5.1.31-ndb-6.3.23 (revid:[e-mail]-20090212171522-7f4wksf9hqmyu7nm) (version source revid:[e-mail]-20090212171522-7f4wksf9hqmyu7nm) (merge vers: 5.1.31-ndb-6.3.23) (pib:6)

[12 Feb 2009 17:19] Bugs System

Pushed into 5.1.31-ndb-6.4.3 (revid:[e-mail]-20090212171733-o0kb9b6bl6scii3j) (version source revid:[e-mail]-20090212171733-o0kb9b6bl6scii3j) (merge vers: 5.1.31-ndb-6.4.3) (pib:6)

[15 Feb 2009 20:18] Jon Stephens

Documented bugfix in the NDB-6.2.17, 6.3.23, and 6.4.3 changelogs as follows:

        Triggers on NDBCLUSTER tables caused such tables to become locked.