Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
simple steps to cause an exclusive lock in mysql
  • 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.
@infinitewarp
Copy link
Author

infinitewarp commented Sep 6, 2016

More info in the MySQL docs: InnoDB Locking

Loading

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment