maria-discuss team mailing list archive
Mailing list archive
Re: New Question: I am trying to find the latest MariaDB equivelent of the MySQL reference manual
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 <greenlion@xxxxxxxxx>
> 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
> 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).
> > wrote:
>> 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:
>> Mailing list: https://launchpad.net/~maria-discuss
>> Unsubscribe : https://launchpad.net/~maria-discuss
>> More help : https://help.launchpad.net/ListHelp
Eng. Automação e Controle