infinitewarp/mysql exclusive lock.md

## mysql exclusive lock.md

      
    Raw
  

              mysql exclusive lock.md
            
          
open shell 1 to the database and run the following to create a table and start a new transaction:

create table foo (id integer primary key);
commit;
begin;

open shell 2 to the database and run the following to ensure you see the table and start a new transaction:

describe foo;
begin;

in shell 1, insert a row and do not commit the transaction:

insert into foo values (1);

in shell 2, try to update the contents of the table:

update foo set id = id + 1

shell 2 should now hang because of a lock.
while shell 2 is hanging, open shell 3 (this one needs to be the root/priviledged user) and run:

select * from information_schema.innodb_locks;

you should see something like this to indicate you have a lock!

+-----------------+-------------+-----------+-----------+--------------+------------+------------+-----------+----------+-----------+
| lock_id         | lock_trx_id | lock_mode | lock_type | lock_table   | lock_index | lock_space | lock_page | lock_rec | lock_data |
+-----------------+-------------+-----------+-----------+--------------+------------+------------+-----------+----------+-----------+
| 126241:5646:3:2 | 126241      | S         | RECORD    | `test`.`foo` | PRIMARY    |       5646 |         3 |        2 | 1         |
| 126240:5646:3:2 | 126240      | X         | RECORD    | `test`.`foo` | PRIMARY    |       5646 |         3 |        2 | 1         |
+-----------------+-------------+-----------+-----------+--------------+------------+------------+-----------+----------+-----------+


lock_mode X means that transaction has an exclusive lock on the row and nobody else can read it until the lock is released.