Bug #63730 Update different rows of table in Function will cause 'Lock Wait Timeout'

• Submitted: 13 Dec 2011 9:53
• Modified: 19 Dec 2011 6:57
• Reporter: wei liu
• Status: Duplicate
• Category: MySQL Server: Locking
• Severity: S3 (Non-critical)
• Version: mysql-5.1.56 ndb-7.1.15
• OS: Solaris (sol10)
• CPU Architecture: Any
• Tags: 1205, err, FUNCTION, lock, non-index, UPDATE

[13 Dec 2011 9:53] wei liu

Description:
In ndbcluster engine,2 and above processes call a function to update a table on different rows concurrently in a high repeat frequency, 'error 1205: lock wait timeout' error will pop out.
The logical of function only do two things:
1. select value;
2. update table without use index;

If add a proper index on the column for update using, or use procedure replace of function,the error will be avoided. 

How to repeat:
1. create test table test and insert test data.
2. create test function func_seq_get_next_number.
3. create test procedure prc_lock_test and prc_lock_test2.
4. call prc_lock_test and prc_lock_test2 on two separate client concurrently.
(these two procedures will call the function to update different rows of non-index table test repeatedly in a 200 times loop)
5. 'error 1205: lock wait timeout' error pop out

Suggested fix:
1. Create proper index
or
2. Use procedure replace of function
or
3. Use innodb replace ndbcluster

[13 Dec 2011 9:59] wei liu

test procedure 2

Attachment: prc_lock_test2.sql (application/octet-stream, text), 528 bytes.

[16 Dec 2011 18:15] Sveta Smirnova

Thank you for the report.

This is duplicate of bug #44613 fix for which will not be backported into 5.1. Please use workaround until Cluster based on 5.5 will be General Available.

[19 Dec 2011 6:57] wei liu

It's different with bug#44613:
1. the engine of this issue is NDB, and it will not happen in innodb.
2. it seems that it caused by the 'update' of function, but not 'select'.
3. it did not use 'transaction' in the test function.
please review it again, thanks very much.