In MySQL 8.0 there are two new features designed to support lock handling: NOWAIT and SKIP LOCKED. In this post, we’ll look at how MySQL 8.0 handles hot rows. Up until now, how have you handled locks that are part of an active transaction or are hot rows? It’s likely that you have the application attempt to access the data, and if there is a lock on the requested rows, you incur a timeout and have to retry the transaction. These two new features help you to implement sophisticated lock handling scenarios allowing you to handle timeouts better and improve the application’s performance.

To demonstrate I’ll use this product table.

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.11    |
+-----------+
1 row in set (0.00 sec)

CREATE TABLE `product` (
`p_id` int(11) NOT NULL AUTO_INCREMENT,
`p_name` varchar(255) DEFAULT NULL,
`p_cost` decimal(19,4) NOT NULL,
`p_availability` enum('YES','NO') …

Recently, I was working on one of the issue related to locks and deadlocks with InnoDB tables and I found very interesting details about how InnoDB locks and deadlocks works with or without index for different Isolation levels.

Here, I would like to describe a small test case about how SELECT ..FOR UPDATE (with and without limit) behave with INSERT/UPDATE and with READ-COMMITED and REPEATABLE-READ Isolation levels. I’m creating a small table data_col with few records. Initially, this test case was written by Bill Karwin to explain details to customer, but here I have used a bit modified test case.

CREATE TABLE data_col (dataname VARCHAR(10), period INT, expires DATE, host VARCHAR(10));

INSERT INTO data_col VALUES (‘med1′, …

With the introduction of metadata locking in MySQL 5.5, it is much more common to see queries stuck in the “Waiting for table metadata lock” state.

If you see a query stuck in the “Waiting for table metadata lock” state, waiting for a MyISAM table, then it is because this table has been included as part of some uncommitted transaction (whether intentionally or not).

In fact, it could be as simple as issuing SET AUTOCOMMIT=0 followed by a SELECT against a MyISAM table (a transaction can be started 3 ways: START TRANSACTION, BEGIN, and SET AUTOCOMMIT=0). After that, the table will be locked to other DDL statements until the transaction is committed (or ended).

This metadata lock is meant for DDL statements, and so that is what it blocks. However, once a DDL statement is blocked and waiting, then all queries …

One common cause for deadlocks when using InnoDB tables is from the existence of foreign key constraints and the shared locks (S-lock) they acquire on referenced rows.

The reason I want to discuss them though is because they are often a bit tricky to diagnose, especially if you are only looking at the SHOW ENGINE INNODB STATUS output (which might be a bit counter-intuitive since one would expect it to contain this info).

Let me show a deadlock error to illustrate (below is from SHOW ENGINE INNODB STATUS\g):

------------------------
LATEST DETECTED DEADLOCK
------------------------
111109 20:10:03
*** (1) TRANSACTION:
TRANSACTION 65839, ACTIVE 19 sec, OS thread id 4264 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1024, 3 row lock(s), undo log entries 1
MySQL thread id 3, query id 74 localhost 127.0.0.1 root Updating
UPDATE parent SET age=age+1 WHERE id=1
*** (1) WAITING FOR THIS LOCK TO BE …

Yesterday I was going through the locks related code; and found that in 5.0; when you have a global read lock; then the SHOW TABLE STATUS actually gets blocked when it had its own read lock on the new session; and this is not the case with MySQL 5.1

For example; lets execute the following set of statements in two sessions with MySQL 5.0 (latest bazaar version with InnoDB enabled):

Session 1:

mysql> use test;
Database changed
mysql> drop table if exists t1;
Query OK, 0 rows affected (0.07 sec)
 
mysql> create table test.t1(c1 int)Engine=InnoDB;
Query OK, 0 rows affected (0.00 sec)
 
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

Session 2:

mysql> use test
Database changed
mysql> lock table t1 read;
Query OK, 0 rows affected (0.01 sec)
 
mysql> show …