List:Internals« Previous MessageNext Message »
From:Baron Schwartz Date:November 10 2007 2:13pm
Subject:Row-based replication and InnoDB locks
View as plain text  
I heard a rumor that InnoDB locks only the rows it needs in MySQL 5.1 
when row-based replication and READ COMMITTED are used.  I'm not able to 
verify this change with 5.1.22, though.  Is it slated for a future version?

Example to reproduce:

Session 1:

set @@binlog_format := 'row'; show variables like 'binlog_format';
+---------------------+-------------------+
| Variable_name       | Value             |
+---------------------+-------------------+
| binlog_format       | ROW               |
+---------------------+-------------------+
set transaction isolation level read committed;
set auto_commit = 0;
begin;
SELECT actor_id FROM sakila.actor WHERE actor_id < 5 AND actor_id <> 1 
FOR UPDATE;
+----------+
| actor_id |
+----------+
|        2 |
|        3 |
|        4 |
+----------+

Session 2:
-- set binlog_format, etc etc...
SELECT actor_id FROM sakila.actor WHERE actor_id = 1 FOR UPDATE;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
Thread
Row-based replication and InnoDB locksBaron Schwartz10 Nov
  • Re: Row-based replication and InnoDB locksEric Bergen12 Nov
    • Re: Row-based replication and InnoDB locksSergei Golubchik12 Nov
  • Re: Row-based replication and InnoDB locksMarko Mäkelä12 Nov