maria-discuss team mailing list archive

[Roberto Spadim <roberto@xxxxxxxxxxxxx>]

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>
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 <noreply@xxxxxxxxxxxx<javascript:_e(%7B%7D,'cvml','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<javascript:_e(%7B%7D,'cvml','maria-discuss@xxxxxxxxxxxxxxxxxxx');>
>> Unsubscribe : https://launchpad.net/~maria-discuss
>> More help   : https://help.launchpad.net/ListHelp
>>
>
>

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