CODEWITHSUNDEEP
mysqltransactionsinnodb
bragboy
bragboy

Reputation: 35562

Mysql InnoDB - Locking scenario

I am a developer and have only fair knowledge about databases. I need to understand the transaction level locking mechanism in InnoDB.

I read that InnoDB uses row level locking? As far as I understand, it locks down a particular row within a transaction. What will happen to a select statement when a table update is going on ?

For Example, assume there is transaction and a select statement both triggered from two different processes and assume Transaction1 starts before the select statement is issued.

Transaction1 : Start
Update table_x set x = y where 1=1
Transaction1 : End

Select Query

Select x from table_x

What will happen to the select statement. Will it return values "during" Transaction1 takes place or "after" it completes? And if it can begin only after Transaction1 ends, where is Row level locking in this picture?

Am I making sense or my fundamental understanding itself is wrong? Please advise.

Upvotes: 1

Views: 1905

Answers (4)

Maxim Krizhanovsky
Maxim Krizhanovsky

Reputation: 26739

It does not depend only on the locking involved, but on the isolation level, which uses locking to provide the transaction isolation as defined by ACID standards. InnoDB uses not only locking, but also multiversioning of the rows to speed up transactions.

In serializable isolation level it would use read-lock with the update, so the select will have to wait for first transaction to be completed. On lower isolation levels however the lock will be write, and selects won't be blocked. In repeatable read and read committed it will scan the rollback log to get the previous value of the record, if it is updated, and in read uncommitted in will return the current value.

The difference between table-level locking and row-level locking is when you have 2 transactions that run update query. In table-level locking, the 2nd will have to wait the first one, as the whole table is locked. In row-level locking, only the rows that match the where clause* (as well as some gaps between them, but this is another topic) will be locked, which means that different transactions can update different parts of the table without need to wait for each other.

*assuming that there is index covering the where clause

Upvotes: 2

edze
edze

Reputation: 3015

It depends on the Isolation level.

SERIALIZABLE 
REPEATABLE READS 
READ COMMITTED 
READ UNCOMMITTED

Good explained on wikipedia

And the mySQL docu

Upvotes: 3

Matt MacLean
Matt MacLean

Reputation: 19656

The select will not wait for the transaction to complete, instead it will return the current value of the rows (aka, before the transaction started).

If you want the select to wait for the transaction to finish you can use "LOCK IN SHARE MODE":

Select x from table_x LOCK IN SHARE MODE;

This will cause the select to wait for any row(s) that are currently lock by a transaction holding an exclusive (update/delete) lock on them.

A read performed with LOCK IN SHARE MODE reads the latest available data and sets a shared mode lock on the rows read. A shared mode lock prevents others from updating or deleting the row read. Also, if the latest data belongs to a yet uncommitted transaction of another session, we wait until that transaction ends.

http://dev.mysql.com/doc/refman/5.0/en/innodb-lock-modes.html

Upvotes: 2

Mchl
Mchl

Reputation: 62395

SELECT started from outside of a transaction will see the table as it was before transaction started. It will see updated values only after transsaction is commited.

Upvotes: 1

Related Questions