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 <greenl...@gmail.com> 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-committed-and-repeatable-read-transaction-isolation-levels/ > > > > > On Sat, Feb 8, 2014 at 11:45 AM, AskMonty KB > <nore...@askmonty.org<javascript:_e(%7B%7D,'cvml','nore...@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-of-the-mysql-reference-ma/ >> >> _______________________________________________ >> Mailing list: https://launchpad.net/~maria-discuss >> Post to : >> maria-discuss@lists.launchpad.net<javascript:_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
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
