Re: InnoDB locks during updates and deletes

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/[email protected]
> 


-- 
MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe:    http://lists.mysql.com/[email protected]