question to locks on an update statement

[Reto Breitenmoser]

Hi
I saw following example in the documentation on chapter 15.5 when you 
look at the property "innodb_locks_unsafe_for_binlog":

Example from the docu 
START-------------------------------------------------------------------------------

CREATE TABLE A(A INT NOT NULL, B INT);
INSERT INTO A VALUES (1,2),(2,3),(3,2),(4,3),(5,2);
COMMIT;
If one connection executes a query:
SET AUTOCOMMIT = 0;
UPDATE A SET B = 5 WHERE B = 3;
and the other connection executes after the first one a query:
SET AUTOCOMMIT = 0;
UPDATE A SET B = 4 WHERE B = 2;
Then query two has to wait for a commit or rollback of query one, 
because query one has an exclusive lock to a row (2,3), and query two 
while scanning rows also tries to take an exclusive lock to the row 
(2,3) which it cannot have. This is because query two first takes an 
exclusive lock to a row and then checks does this row belong to the 
result set and if not then releases the unnecessary lock when option 
innodb_locks_unsafe_for_binlog is used.

Therefore, query one is executed as follows:
x-lock(1,2)
unlock(1,2)
x-lock(2,3)
update(2,3) to (2,5)
x-lock(3,2)
unlock(3,2)
x-lock(4,3)
update(4,3) to (4,5)
x-lock(5,2)
unlock(5,2)
and then query two is executed as follows:
x-lock(1,2)
update(1,2) to (1,4)
x-lock(2,3) - wait for query one to commit or rollback
example from the docu 
END------------------------------------------------------------------------------------

If there would be an index on the second column (B int) would then the 
query look like this?

x-lock(2,3)
update(2,3) to (2,5)
x-lock(4,3)
update(4,3) to (4,5)
and the second query
x-lock(1,2)
update(1,2) to (1,4)
x-lock(3,2)
update(3,2) to (3,4)
x-lock(5,2)
update(5,2) to (5,4)
In this case the second query had not to wait on the first query. Is 
this correct?

Thanks
Reto
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]