Bug #72005 Update table using join clause
Submitted: 11 Mar 2014 10:17 Modified: 12 Mar 2014 19:13
Reporter: Vinod Sugur Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Locking Severity:S3 (Non-critical)
Version:5.2.3-falcon-alpha-community-nt OS:Any
Assigned to: CPU Architecture:Any
Tags: Update multitable, update statement

[11 Mar 2014 10:17] Vinod Sugur
Description:
Multiple-table UPDATE statement puts an exclusive lock on referenced table rows (index locks), even if the columns of referenced table are not updated. This also applies to UPDATE statement referenced by derived table (sub-query).

How to repeat:
-- Steps to reproduce:

-- Prerequisite:

CREATE TABLE `upd1` (
  `id` int(11) NOT NULL,
  `status` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `upd2` (
  `id` int(11) NOT NULL,
  `status` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

-- Insert into UPD1 table
INSERT INTO UPD1 VALUES(1,'A');

-- Insert into UPD2 table
INSERT INTO UPD2 VALUES(1,'B');
INSERT INTO UPD2 VALUES(2,'D');

-- Execute below sql queries from two sessions:

-- Session 1:
SET AUTOCOMMIT=0;
Update upd1 join upd2 ON upd1.id = upd2.id set upd1.status = upd2.status;

-- The above statement locks rows with value of column id as 1, from both the tables. Ideally, it should lock only rows from UPD1 table for id=1.

-- Session 2:
SET AUTOCOMMIT=0;
UPDATE UPD2 SET STATUS = 'K' WHERE ID =1;
 
-- Session 2 update statement waits till locks are released from Session 1.

Suggested fix:
Rows of the table being updated should only get locked, referenced table, if its only part of SELECT should not be consider for locking.
In the above case only UPD1 row with id column value "1" should get locked, referenced table rows should not be considered for exclusive locks.
[12 Mar 2014 16:27] MySQL Verification Team
Thank you for reporting this behavior. However, this behavior is totally expected and it is so by design.

What you see is only a read lock, not a write lock. It was erroneously a write lock, until a bug # 4118 was fixed in MySQL version 4.1. Since then, it is only a read lock that is taken on a referenced table.

To help you understand it better, let me tell you that, although you did not use a classical multiple-table update syntax:

update table t1,t2 ....

any UPDATE with a join is considered a multiple-table update. In that case, a referenced table has to be read-locked, because rows must not be changed in the referenced table during UPDATE  until it has finished. There can not be concurrent changes of the rows, nor DELETE of the rows, nor, much less, exempli gratia any DDL on the referenced table. The goal is simple, which is to have all tables with consistent contents when UPDATE finishes, particularly since multiple-table UPDATE can be executed with several passes.
[12 Mar 2014 16:53] Vinod Sugur
Thank for the update. The issue here is that exclusive lock is not granted to other session till the shared (read lock) is released. 

Why cannot we implement Multi versioning concurrency control (MVCC) for referenced table in update statement?, Before starting with update get the state of the referenced table/derived query and then proceed. This is only for the table that are not going to be updated. 

In Oracle table rows referenced in Update statement are not locked (shared/read). I understand that Oracle does not support multitable in similar way as that of MySQL.

Is there a plan to take this as enhancement?
[12 Mar 2014 17:20] MySQL Verification Team
I completely understand both, your question and your grievance. 

If this was a read-only statement, like SELECT, there would not have been any locks taken, but instead, we would have relied entirely on MVCC. That is how things function in multi-versioning.

You have to take a read lock on the table involved in a multiple-table DML, just as much as you have to take a write lock on the table that is to be updated / deleted. Imagine that in the middle of DML, a table is DROPped. That could not be handled with MVCC, which simply has its limitations. 

Read lock is there in order to have tables fully consistent after multiple-table DML is committed. It is the same situation as with foreign keys. If you are updating rows in a child table, the corresponding rows in the parent table have to be read locked, in order to achieve consistency.

Regarding the entire locking system, it is planned to make a revision, but not in the immediate future.
[12 Mar 2014 19:13] Vinod Sugur
One more clarification, you mentioned that the read lock are placed till UPDATE is finished OR is it held till transaction boundary? 

In my example read locks are not released after completion of UPDATE statement their scope is transaction boundary.
[12 Mar 2014 19:20] MySQL Verification Team
The answer to your question is of course that locks are held till the end of transaction. Which is what I said in other words, when talking about committing. 

One small addition regarding using read locks in situation such as you describe. InnoDB relies on read locks, also to prevent newer records from being read during the UPDATE.
[13 Mar 2014 9:08] Hartmut Holzgraefe
Version:	5.2.3-falcon-alpha-community-nt

SRSLY????
[13 Mar 2014 13:46] MySQL Verification Team
Hartmut, my dear friend,

Yes, that one is a totally unknown quantity to me as well ...... ;-)

BTW, that failed project used one interesting feature of which I will tell you more in the more appropriate place ...