MySQL Bugs: #39843: DELETE requires write access to table in subquery in where clause

Bug #39843	DELETE requires write access to table in subquery in where clause
Submitted:	3 Oct 2008 17:57	Modified:	23 Mar 2009 2:02
Reporter:	Scott Noyes	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S3 (Non-critical)
Version:	5.1.28	OS:	Any
Assigned to:	Kristofer Pettersson	CPU Architecture:	Any

Description:
Using a READ-locked table in a subquery in the WHERE clause of a DELETE results in the following:

ERROR 1099 (HY000): Table 'temp_table2' was locked with a READ lock and can't be updated

How to repeat:
CREATE TABLE temp_table1 (
	table1_rowid SMALLINT NOT NULL
);

CREATE TABLE temp_table2 (
	table2_rowid SMALLINT NOT NULL
);

INSERT INTO temp_table1 VALUES (1);
INSERT INTO temp_table2 VALUES (1);

LOCK TABLES temp_table1 WRITE, temp_table2 READ; 

DELETE FROM temp_table1 
WHERE EXISTS 
( 
	SELECT 'x' 
	FROM temp_table2
 	WHERE temp_table1.table1_rowid = temp_table2.table2_rowid
) ; 
 
UNLOCK TABLES;

Suggested fix:
The table in the subquery does not need to be updated; a read lock should suffice.

Workaround is possible by rewriting query so it does not use a subquery:

DELETE FROM temp_table1
USING 
  temp_table1
  JOIN temp_table2 ON temp_table1.table1_rowid = temp_table2.table2_rowid

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/56568

2668 Kristofer Pettersson	2008-10-20
      Bug#39843 DELETE requires write access to table in subquery in where clause
      
      An unnecessarily restrictive lock were taken on sub-SELECTs during DELETE.
      
      During parsing a global structure is reused for sub-SELECTs and the variable
      controlling lock options were not reset properly. This patch sets the default
      value so that a sub-SELECT will try to acquire a READ lock if possible
      instead of a WRITE lock as inherited from the outer DELETE statement.

There are two methods in st_select_lex for adding tables with a lock option:
::set_lock_for_tables and
::add_table_to_list

I find it risky to extract lock_option from st_lex to st_select_lex because of the dependencies in the parser rules, but if we don't want to reset lex->lock_option inside the st_select_lex class, we can perhaps append a set_lock_for_tables at the end of the subselect-rule.

Illustration of the parser rules causing the issue.

Attachment: delete-parsing.png (image/png, text), 115.97 KiB.

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/68260

2825 Kristofer Pettersson 2009-03-04
Bug#39843 DELETE requires write access to table in subquery in where clause

An unnecessarily restrictive lock were taken on sub-SELECTs during DELETE.

During parsing, a global structure is reused for sub-SELECTs and the attribute
keeping track of lock options were not reset properly.
This patch introduces a new attribute to keep track on the syntactical lock
option elements found in a sub-SELECT and then sets the lock options accordingly.

Now the sub-SELECTs will try to acquire a READ lock if possible
instead of a WRITE lock as inherited from the outer DELETE statement.
@ mysql-test/r/lock.result
Added test case for bug39843
@ mysql-test/t/lock.test
Added test case for bug39843
@ sql/sql_lex.cc
* Reset member variable lock_option on each new query.
@ sql/sql_lex.h
* Introduced new member variable 'lock_option' which is keeping track of the syntactical
lock option of a (sub-)select query.
@ sql/sql_parse.cc
* Wrote comments to functions.
@ sql/sql_yacc.yy
* Introduced an attribute to keep track of syntactical lock options in sub-selects.
* Made sure that the default value TL_READ_DEFAULT is at the begining of each
subselect-rule.

Solution looks fine to me.

As this introduces another lock_option field (in select_lex), you may add a comment that the use of it overlaps with the lock_option field in the Lex.

Is it possible to encapsulate setting of lock_option in set_lock_for_tables()?
in sql_yacc.yy, there is a call to set_lock_for_tables(), immediately followed by assigning value to lock_option.

I don't think moving Lex->current_select->lock_option= X into set_lock_for_tables is a good idea although it would reduce the silly copy-paste code. The reason is that I think that lock_option is variable for a syntactic expression without any semantic meaning, and set_lock_for_tables operates on a list of table-objects which later will be execution elements of sorts. Maybe we should consider moving set_lock_for_tables out of st_select_lex instead?

Do you agree?

No, I do not think that. However, let us close this case now and re-open it if we get to re-design the parser data structures.

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/68388

2825 Kristofer Pettersson	2009-03-05
      Bug#39843 DELETE requires write access to table in subquery in where clause
      
      An unnecessarily restrictive lock were taken on sub-SELECTs during DELETE.
      
      During parsing, a global structure is reused for sub-SELECTs and the attribute
      keeping track of lock options were not reset properly.
      This patch introduces a new attribute to keep track on the syntactical lock
      option elements found in a sub-SELECT and then sets the lock options accordingly.
      
      Now the sub-SELECTs will try to acquire a READ lock if possible
      instead of a WRITE lock as inherited from the outer DELETE statement.
     @ mysql-test/r/lock.result
        Added test case for bug39843
     @ mysql-test/t/lock.test
        Added test case for bug39843
     @ sql/sql_lex.cc
        * Reset member variable lock_option on each new query.
     @ sql/sql_lex.h
        * Introduced new member variable 'lock_option' which is keeping track
          of the syntactical lock option of a (sub-)select query.
     @ sql/sql_parse.cc
        * Wrote comments to functions.
     @ sql/sql_yacc.yy
        * Introduced an attribute to keep track of syntactical lock options
          in sub-selects.
        * Made sure that the default value TL_READ_DEFAULT is at the begining
          of each subselect-rule.

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/68591

2837 He Zhenxing	2009-03-09 [merge]
      Auto merge

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/69036

3109 Kristofer Pettersson	2009-03-12
      Bug#39843 DELETE requires write access to table in subquery in where clause
      
      Reverting patch for 6.0-bugteam as it breaks an assert in set_handler_table_locks

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/69161

3113 Kristofer Pettersson	2009-03-13
      Bug#39843 DELETE requires write access to table in subquery in where clause
      
      An unnecessarily restrictive lock were taken on sub-SELECTs during DELETE.
      
      During parsing, a global structure is reused for sub-SELECTs and the attribute
      keeping track of lock options were not reset properly.
      This patch introduces a new attribute to keep track on the syntactical lock
      option elements found in a sub-SELECT and then sets the lock options accordingly.
      
      Now the sub-SELECTs will try to acquire a READ lock if possible
      instead of a WRITE lock as inherited from the outer DELETE statement.
     @ mysql-test/r/lock.result
        Added test case for bug39843
     @ mysql-test/t/lock.test
        Added test case for bug39843
     @ sql/sql_lex.cc
        * Reset member variable lock_option on each new query.
     @ sql/sql_lex.h
        * Introduced new member variable 'lock_option' which is keeping track
          of the syntactical lock option of a (sub-)select query.
     @ sql/sql_parse.cc
        * Wrote comments to functions.
     @ sql/sql_yacc.yy
        * Introduced an attribute to keep track of syntactical lock options
          in sub-selects.
        * Made sure that the default value TL_READ_DEFAULT is at the begining
          of each subselect-rule.

Pushed into 5.1.33 (revid:joro@sun.com-20090313111355-7bsi1hgkvrg8pdds) (version source revid:zhou.li@sun.com-20090311061050-ihp0g77znonq1tuq) (merge vers: 5.1.33) (pib:6)

Pushed into 6.0.11-alpha (revid:joro@sun.com-20090318122208-1b5kvg6zeb4hxwp9) (version source revid:joro@sun.com-20090317133112-41qn6aly7arljtlq) (merge vers: 6.0.11-alpha) (pib:6)

Noted in 5.1.33, 6.0.11 changelogs.

DELETE tried to acquire write (not read) locks for tables accessed
within a subquery of the WHERE clause.

Pushed into 5.1.34-ndb-6.2.18 (revid:jonas@mysql.com-20090508185236-p9b3as7qyauybefl) (version source revid:jonas@mysql.com-20090508100057-30ote4xggi4nq14v) (merge vers: 5.1.33-ndb-6.2.18) (pib:6)

Pushed into 5.1.34-ndb-6.3.25 (revid:jonas@mysql.com-20090509063138-1u3q3v09wnn2txyt) (version source revid:jonas@mysql.com-20090508175813-s6yele2z3oh6o99z) (merge vers: 5.1.33-ndb-6.3.25) (pib:6)

Pushed into 5.1.34-ndb-7.0.6 (revid:jonas@mysql.com-20090509154927-im9a7g846c6u1hzc) (version source revid:jonas@mysql.com-20090509073226-09bljakh9eppogec) (merge vers: 5.1.33-ndb-7.0.6) (pib:6)