Bug #34263 LOCK TABLES throws no error on deadlock, unlocks tables silently
Submitted: 3 Feb 2008 15:14 Modified: 26 May 2010 17:49
Reporter: Baron Schwartz (Basic Quality Contributor) Email Updates:
Status: Unsupported Impact on me:
None 
Category:MySQL Server: Falcon storage engine Severity:S3 (Non-critical)
Version:6.0.3 OS:Any
Assigned to: CPU Architecture:Any
Tags: F_HANDLER, qc
Triage: Triaged: D3 (Medium)

[3 Feb 2008 15:14] Baron Schwartz
Description:
LOCK TABLES with the new IN EXCLUSIVE MODE modifier doesn't warn of a deadlock, and unlocks tables silently.

How to repeat:
Create Falcon tables tbl1 and tbl2.

In connection 1, with the --show-warnings option to mysql,

mysql [localhost] {msandbox} (test) > LOCK TABLES tbl1 IN EXCLUSIVE MODE;
Query OK, 0 rows affected, 1 warning (0.00 sec)

Warning (Code 1598): Converted to non-transactional lock on 'tbl1'

In connection 2,

mysql [localhost] {root} (test) > LOCK TABLES tbl2 IN EXCLUSIVE MODE;
Query OK, 0 rows affected, 1 warning (0.00 sec)

Warning (Code 1598): Converted to non-transactional lock on 'tbl2'
mysql [localhost] {root} (test) > LOCK TABLES tbl1 IN EXCLUSIVE MODE;

[hangs]

Back in connection 1, if I lock tbl2 it should cause a deadlock:

mysql [localhost] {msandbox} (test) > LOCK TABLES tbl2 IN EXCLUSIVE MODE;
Query OK, 0 rows affected, 1 warning (0.00 sec)

Warning (Code 1598): Converted to non-transactional lock on 'tbl2'

[the lock succeeded on connection 2]
[3 Feb 2008 19:17] Valeriy Kravchuk
I had verified the behaviour described. 

I suspect it is intentional in case of Falcon (deadlock was detected and there was a kind of rollback in one of conections...), but let's wait for more comments from developers.
[4 Feb 2008 8:55] Sergei Golubchik
Falcon does not support LOCK TABLES ... IN EXCLUSIVE|SHARE MODE.

You see the warning "Converted to non-transactional lock on 'tbl1'", it means the statement was changed to LOCK TABLES tbl1 WRITE. With all the quirk of the latter, in particular LOCK TABLES ... WRITE performs an implicit UNLOCK TABLES before placing new locks.
[4 Feb 2008 14:03] Baron Schwartz
Does any engine in 6.0.3 support this newer syntax?

Maybe this just needs to be changed to a documentation bug, and a note on manual page can be the fix.
[4 Feb 2008 15:25] Sergei Golubchik
InnoDB does.
[4 Feb 2008 16:14] Kevin Lewis
Docs,  Once this current limitation is documented, please put this bug back to Server:Falcon so that we can work on implementing this interface.
[4 Feb 2008 17:59] Valeriy Kravchuk
OK, so it is a verified documentation request for now.
[18 Jul 2008 15:00] Paul Dubois
Documentation for transactional locks now appears in the 6.0 manual here:

http://dev.mysql.com/doc/refman/6.0/en/lock-tables.html

This section does note that transactional locks are implemented only for InnoDB.

Resetting report to Server:Falcon per Kevin's request. Unassigning myself.