List:Internals« Previous MessageNext Message »
From:Karen Abgarian Date:May 31 2011 3:49pm
Subject:Re: InnoDB locks during updates and deletes
View as plain text  
Hi, 

I am also interested in an answer.   In the past, the InnoDB would lock every row it read
if the binary logging was enabled.  
That was done because it is quite possible that somebody could otherwise squeeze in a
transaction which would change 
the table, producing a different amount of rows with b=1, and that would cause the
statement to incorrectly recover/replicate. 
So the rows read would be locked out for any updates. 

But that was at the time when only STATEMENT binary logging was available.  Did the things
change with ROW and MIXED
options?  

Tx
Karen.


On May 31, 2011, at 8:34 AM, Zardosht Kasheff wrote:

> Hello all,
> 
> Suppose I have a table with NO indexes:
> create table foo (a int, b int, c int) engine=InnoDB;
> 
> and then I run:
> begin;
> update foo set c=5 where b=1; <-- updates one row
> 
> 
> Because there is no index, InnoDB must read the entire table to find
> the single row to update.
> 
> Here is my question: what type of lock is grabbed for each row read?
> Is it a shared (read) lock or an exclusive (write) lock? I know the
> row that is modified gets a write lock, but what about the rest of the
> rows and the gaps in between the rows that are read but not modified?
> Are those exclusive locks or shared locks?
> 
> Also, I assume the behavior is similar with respect to deletes?
> 
> Thanks
> -Zardosht
> 
> -- 
> MySQL Internals Mailing List
> For list archives: http://lists.mysql.com/internals
> To unsubscribe:    http://lists.mysql.com/internals?unsub=1
> 

Thread
InnoDB locks during updates and deletesZardosht Kasheff31 May
  • Re: InnoDB locks during updates and deletesKaren Abgarian31 May