Bug #6150 Incremental LOCKs or stacked LOCKs
Submitted: 18 Oct 2004 23:25 Modified: 3 Oct 2008 16:40
Reporter: Levap Aretnyd Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Locking Severity:S4 (Feature request)
Version: OS:Any
Assigned to: Heikki Tuuri CPU Architecture:Any

[18 Oct 2004 23:25] Levap Aretnyd
Description:
I think that it would be really usefull if some kind of incremental lock command was created. Especialy when working with stored procedures.
My point is that if somebody issues LOCK TABLES, all already present table locks not in this query are released. By incremental locks a mean something that will only add table(s) to already locked tables, without unlocking previous ones. This can be implemented on the client side, when it knows what tables are locked, but in stored procedures it is impossible to simulate this.
Particularly, when one stored procedure that has issued lock tables, calls another that also issues lock tables, the previous locks are released, it can be easily missed when some complicated hiearchy of stored procedures is used.
(It can be walkedaround by not calling stored procedure from stored procedure but by "pasting" the code to the previous and rewriting the locks).
Some way to "substract" locks will be also usefull and it would optimise performance of database.

Another way to implement this, is something what can be called "stacked locks". Locks then would be analogous to BEGIN END statements. This is simplified example of stacked read locks:
LOCK STACK table1 READ, table2 READ; // table1 and table2 are now locked
  LOCK STACK table3 READ; // table1, table2, table3, are now locked
  UNLOCK STACK; // table1 and table2 are now locked (table3 unlocked)
UNLOCK STACK; // all tables unlocked (table1, table2 unlocked)

How to repeat:
see description
[19 Oct 2004 12:26] Heikki Tuuri
Hi!

MySQL's table lock code does not have deadlock detection. Therefore, you cannot 'incrementally' lock tables.

We have in our TODO to implement

LOCK TABLE TRANSACTIONAL ...

that will allow 'incremental' locking. We plan to have the feature available in InnoDB in 2005.

Regards,

Heikki
[3 Oct 2008 16:40] Konstantin Osipov
http://forge.mysql.com/worklog/task.php?id=3561
"LOCK TABLES transactional"