MySQL hates you …

… if it is badly treated 😉

This post will show something about MySQL locks, transactions and autocommit.

Before we start, here’s some useful stuff to know:

  • You get the MySQL connection id with SELECT CONNECTION_ID();
  • You get information about use status of tables with SHOW OPEN TABLES FROM the_database;

And here’s the test setup for database and table:

The test setup

Open two tabs in a terminal and connect to MySQL in each of them. We will name those connections Acon and Bcon. Execute SELECT CONNECTION_ID(); and you will see that both connections have different ids.


LOCKS

Acon:
LOCK TABLE data_base WRITE;
INSERT INTO data_base ( data ) VALUES ( 'hurz' );
SHOW OPEN TABLES FROM the_database;

You will see that table data_base is now marked as in use. The new data is inserted into table data_base and has id 5.

Bcon:
INSERT INTO data_base ( data ) VALUES ( 'asdf' );

… and while you wait, grab some lunch, get a coffee, have chat with colleagues …
Forget it, the other connection holds the write lock on that table.

Acon:
UNLOCK TABLES;

And in Bcon the pending insert is immediately executed and data is in there with id 6 now. Write lock is also released when the connection holding the lock crashes (worksforme locally).


AUTOCOMMIT

Acon:
SET AUTOCOMMIT = 0;
INSERT INTO data_base ( id, data ) VALUES ( '5', 'hurz' );
SELECT id, data FROM data_base ORDER BY id DESC LIMIT 1;

Answer: ‘5’, ‘hurz’

Bcon:
SELECT id, data FROM data_base ORDER BY id DESC LIMIT 1;

Answer: ‘4’, ‘wahoo’. Means: Acon sees its own inserted data but as autocommit is turned off, Bcon does not yet see the new entries.

Bcon:
INSERT INTO data_base ( id, data ) VALUES ( '5', 'qwer' );

… and while you wait, grab some lunch, get a coffee, have chat with colleagues …
And when you waited long enough, the answer is

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Problem here is: Acon already inserted data for key ‘5’, but as autocommit is turned off, that data can now yet be seen by Bcon. But as Bcon tries to insert data with an already existing id, it gets stuck and waits. Good thing it has some timeout set, bad thing is the data might not be inserted.

Try again to insert data via Bcon, you’ll start waiting again. As soon as you turn auto commit on in Acon, Bcon tries to execute the query and gives an error message:

ERROR 1062 (23000): Duplicate entry ‘5’ for key ‘PRIMARY’

What do we learn from here?
Do a full reset and do the same again but when inserting, do not explicitly set the primary key:

INSERT INTO data_base ( data ) VALUES ( 'hurz' );
SELECT LAST_INSERT_ID(); => answer is 5

In Bcon (that still only sees 4 entries) now do:

SELECT LAST_INSERT_ID(); => answer is 0 as there was nothing yet inserted.
INSERT INTO data_base ( data ) VALUES ( 'qwer' );
SELECT LAST_INSERT_ID(); => answer is 6, query was executed immediately.
SELECT * FROM data_base; => you see id 1 to 4 and 6, but no entry yet with id 5.

The ‘not yet visible for other connections’ insert from Acon can still not be seen in Bcon but as no insert was done with supplying an explicit primary key, the insert did not block the insert in Bcon.

Connections might block each other if they both try to insert data for the same primary key. This will lead to slower query execution or maybe even data loss.


TRANSACTIONS

When you have a bunch of queries that should be executed together or not at all, start a transaction, execute the queries, if all is well say: commit, if there’s a problem do rollback.

Note: transactions can not be used for all kinds of queries. If you have one that affects table structure, like DROP/ADD KEY, DROP/ADD COLUMN, RENAME …, when called inside a transaction will trigger an autocommit.

Acon:
START TRANSACTION;
INSERT INTO data_base ( id, data ) VALUES ( '5', 'hurz' );

Bcon cannot see this inserted data yet, as transaction is still pending. Trying to insert data with the same primary key in Bcon gives us a query that fails with

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

as we could expect.

The transaction in Acon can be ended by either COMMIT or ROLLBACK. If the connection crashes while a transaction is still in progress, no autocommit is triggered. All inserts that were done inside the transaction in Acon are lost in case of Acon crashing while the transaction is open.

If you do not explicitly supply the primary key when inserting data, you will notice that your inserts in Bcon get primary keys that are not yet taken by inserts that were done in not yet finished transactions in Acon.
So far as you don’t insist on choosing the primary keys in inserts, you will not have any inserts forced to wait until other connections finished their transactions.

This entry was posted in howto, mysql. Bookmark the permalink.

Comments are closed.