maria-discuss team mailing list archive

[Justin Swanhart <greenlion@xxxxxxxxx>]

Hi,

In InnoDB, the behavior depends on the transaction isolation level.

First, SELECT FOR UPDATE acquires exclusive (write) locks, while SELECT ...
LOCK IN SHARE MODE creates shared (read) locks.  The MySQL manual InnoDB
manual has a tranditional "row lock compatibility" table which shows how
read and exclusive locks interact.

There are actually four types of locks: IS, IX, S, X (intention shared,
intention exclusive, shared, exclusive) and locks can be taken at the row
level or at the table level(ie, LOCK TABLES can be used to lock an entire
table with an X lock).  IX and IS locks are obtained BEFORE an X or S lock.
 For example, certain types of ALTER TABLE must lock the entire table for
reads and writes.  In this case, there will be an X lock on the table, and
all IX and IS locks will block, meaning no row locks can be acquired during
the alter.

Now that that is covered, you have to think about how locks are actually
acquired, which is actually through INDEX TRAVERSAL.  You see, rows aren't
really locked, but index entries are.  This is why it is very important to
index your queries well.  For example, if you do "SELECT count(*) FROM
my_table WHERE order_status='open' FOR UPDATE", and `order_status` is not
indexed, then the PRIMARY KEY will be traversed to answer the query.  ALL
THE ROWS OF THE PK WILL BE LOCKED, because the rows which are traversed are
locked.

In REPEATABLE-READ, these locks are HELD FOR THE DURATION OF THE CONNECTION
(or until commit/rollback is issued).   If you are using READ-COMMITED,
then locks THAT DID NOT MATCH, are released after the STATEMENT completes
(locks for rows that did match are kept for the duration of the
transaction, like all other locks.

READ-COMMITTED has certain advantages too, such as not having to take extra
"gap" locks and "next key locks" in order to make sure DML statements such
as SELECT .. FOR UPDATE and INSERT .. SELECT return repeatable results
(this is not necessary in READ-COMMITTED, or lower).

See:
http://www.mysqlperformanceblog.com/2012/08/28/differences-between-read-committed-and-repeatable-read-transaction-isolation-levels/




On Sat, Feb 8, 2014 at 11:45 AM, AskMonty KB <noreply@xxxxxxxxxxxx> wrote:

> Hello,
>
> A new question has been asked in "MariaDB FAQ" by robsilver:
> --------------------------------
> For example I am trying to determine regarding using implicit and or
> explicit Locking
> e.g Issolation Levels , Locking  , transactions processing  via
>
> [FOR UPDATE | LOCK IN SHARE MODE]
>
> I have not found a lot out there but if there is an equivalent PDF file
> that would be great. I currently have the book:
> MariaDB Crash Course which I can not find one reference to LOCK IN SHARE
> MODE or FOR UPDATE.
>
>
> --------------------------------
>
> To view or answer this question please visit:
> http://mariadb.com/kb/en/i-am-trying-to-find-the-latest-mariadb-equivelent-of-the-mysql-reference-ma/
>
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-discuss
> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
>