I think next release will "export" this locks at information schema There's some information about innodb there, and there's a plugin for MDL (lock table) information too, it's a nice information when you should understand what's locking queries :) Em sábado, 8 de fevereiro de 2014, Justin Swanhart escreveu:

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-comm...

On Sat, Feb 8, 2014 at 11:45 AM, AskMonty KB javascript:_e(%7B%7D,'cvml','noreply@askmonty.org');

...

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-o...

_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.netjavascript:_e(%7B%7D,'cvml','maria-discuss@lists.launchpad.net'); Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

-- Roberto Spadim SPAEmpresarial Eng. Automação e Controle