List:Internals« Previous MessageNext Message »
From:Eric Bergen Date:November 12 2007 6:29am
Subject:Re: Row-based replication and InnoDB locks
View as plain text  
Hi Baron,

This query is doing a range scan on the index. Innodb locks all rows
in encounters while trying to resolve the query. Since innodb doesn't
have any knowledge of the actual expression (only ndb uses condition
pushdown as far as I know) only index ranges it still locks the first
row because it satisfied the range that innodb was told to scan (< 5).
actor_id 1 is still returned and locked by innodb but tossed out by
mysql before sending the result.


-Eric

On Nov 10, 2007 6:13 AM, Baron Schwartz <baron@stripped> wrote:
> 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
>
> --
> MySQL Internals Mailing List
> For list archives: http://lists.mysql.com/internals
> To unsubscribe:    http://lists.mysql.com/internals?unsub=1
>
>



-- 
Eric Bergen
eric.bergen@stripped
http://www.provenscaling.com
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