List:Internals« Previous MessageNext Message »
From:Sergei Golubchik Date:November 12 2007 8:15am
Subject:Re: Row-based replication and InnoDB locks
View as plain text  
Hi!

On Nov 11, Eric Bergen wrote:
> 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.

No, Baron is right here. In the sense that the row doesn't necessarily
need to be locked - there's handler::unlock_row call, and MySQL uses it
to tell InnoDB that the row didn't match WHERE expression and can be
unlocked right away.

At least, that's what the rumor most propably was referring to, I assume
:)

I didn't check, though, whether it really works or not - whether MySQL
calls unlock_row everywhere where appropriate, whether InnoDB actually
implements this method and unlocks the row, and under what conditions,
if yes.
 
> 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
> >
Regards / Mit vielen Grüssen,
Sergei

-- 
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik <serg@stripped>
 / /|_/ / // /\ \/ /_/ / /__  Principal Software Developer
/_/  /_/\_, /___/\___\_\___/  MySQL GmbH, Dachauer Str. 37, D-80335 München
       <___/                  Geschäftsführer: Kaj Arnö - HRB
München 162140
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