<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8"/>
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>MySQL - Lock an entire table with a delete statement</title>
    <link rel="stylesheet" type="text/css" href="/static/theme.css"/>
    <link href="https://fonts.googleapis.com/css?family=Noto+Serif+TC|Roboto" rel="stylesheet">
    <script src="https://cdnjs.cloudflare.com/ajax/libs/showdown/1.9.0/showdown.min.js"></script>
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/prism/1.5.0/themes/prism.min.css">
    <script src="https://cdnjs.cloudflare.com/ajax/libs/prism/1.5.0/prism.min.js"></script>
    <script src="/static/index.js"></script>
</head>
<body>

<div>

</div>

<div style="text-align: center;">
    <a href="/">Home Page</a>
</div>

<div id="article-heading">
    <div id="article-title">MySQL - Lock an entire table with a delete statement</div>
    <div id="article-date"></div>
</div>


<div id="article"></div>

<!--<div id="footer">-->
<!--    <div style="float: left;">-->
<!--        <a href="/">Previous</a>-->
<!--    </div>-->
<!--    <div style="float: right;">-->
<!--        <a href="/">Next</a>-->
<!--    </div>-->
<!--</div>-->

</body>

<script>
    util.renderArticle('article', `Recently I have been trying to investigate some behaviours in MySQL related to locking. As I have been investigating locking mechanisms in MySQL I came across a somewhat funny case where you can lock an entire table
by deleting a row that does not exist in an empty table using the repeatable-read isolation level.

Here is our table.

\`\`\`
create table test
(
    id int auto_increment,
    c  int,
    primary key (id),
    key idx_c (c)
);
\`\`\`

Here's our statements

\`\`\`
tx1> delete from test where c = 500;
tx2> insert into test(c) values (10); <--- blocked.
\`\`\`

In this scenario \`tx2\` is blocked, why? (In fact any insert into the table is blocked)

### Explanation

So given that we are using an isolation level of repeatable read, we are protected against inconsintent analysis (non repeatable read) and phantom reads. \`tx2\` is blocked because MySQL is using a gap lock as a way to stop phantom reads. Gap locks lock parts before or after an index entry (depending on the query) so nobody can insert into the gap, this stops the same query when ran twice from seeing different results.

What's interesting in the example above is that there is no rows in \`test\` so MySQL doesn't have any index entries it can lock (MySQL only locks on indexes not rows), so it uses a special kind of index entry to lock called \`supremum pseudo-record\` using a gap lock. \`supremum pseudo-record\` is defined as pseudo-record in an index, representing the gap above the largest value in that index. We don't have a 'largest value' as have no rows, so the largest value could be from negative infinity all the way to infinity. We have now locked all rows with a \`c\` value below infinity from being inserted. Which means we cannot insert any rows.

We can observe the locks currently active by running the following query.

\`\`\`
SELECT
  ENGINE,
  OBJECT_SCHEMA,
  OBJECT_NAME,
  INDEX_NAME,
  LOCK_TYPE,
  LOCK_MODE,
  LOCK_STATUS,
LOCK_DATA
FROM performance_schema.data_locks;
\`\`\`

![locks.png](https://raw.githubusercontent.com/StephenFox1995/assets/master/assets/mysql-lock-with-delete/locks.png)

Focus on the second row we can see that there is a lock granted on the \`supremum pseudo-record\` in the \`idx_c\` index.
(I lied about the lock type, although it does not matter. A next-key lock is actually being use not a gap lock, however the difference does not matter here as next-key lock is a gap lock including some extra stuff)

### Notes

It seems to me that this is an implementation detail that is visible to clients in MySQL. In reality there would be no violation to the repeatable-read isolation level if we were able to insert rows which had values c=499, c=501, c=2 etc.
`);
    util.setArticleDate('article-date', `2022-03-14T22:53:35Z`);
    util.colorArticleTitle('article-title', `2022-03-14T22:53:35Z`)
</script>
</html>

