Deadlock detection in PostgreSQL and MySQL
7 Sep 2020
PostgreSQL
We need two user sessions running. We will distinguish between them by customizing their `psql` prompts.
User one:
$ psql -X -U postgres -d postgres # \set PROMPT1 'ONE___ %x%# ' # \set PROMPT2 '' # \pset linestyle unicode # \pset border 2 # set idle_in_transaction_session_timeout to '5min';
User two:
$ psql -X -U postgres -d postgres # \set PROMPT1 '___TWO %x%# ' # \set PROMPT2 '' # \pset linestyle unicode # \pset border 2 # set idle_in_transaction_session_timeout to '5min';
Note that %x in the prompt shows * for an open (non-committed,
non-rolled-back) transaction, and ! for a transaction that had
an error and needs to be abandoned (either with a commit or a rollback,
it doesn't matter).
From here forward, user one and user two are identified by their prompts.
First, we need to disable autocommit, to give us manual control over transaction commits and rollbacks:
ONE___ # \set AUTOCOMMIT off
___TWO # \set AUTOCOMMIT off
Now we need to create and populate a table with data:
ONE___ # create table accounts(
acctnum bigint primary key,
balance bigint default 0 not null);
commit;
ONE___ # create table accounts(
acctnum bigint primary key,
balance bigint default 0 not null);
CREATE TABLE
ONE___ *# commit;
COMMIT
ONE___ #
ONE___ # insert into accounts(acctnum, balance)
values (11111, 1000),
(22222, 1000);
INSERT 0 2
ONE___ *# commit;
COMMIT
ONE___ #
ONE___ # select * from accounts;
┌─────────┬─────────┐
│ acctnum │ balance │
├─────────┼─────────┤
│ 11111 │ 1000 │
│ 22222 │ 1000 │
└─────────┴─────────┘
(2 rows)
ONE___ *# commit;
COMMIT
ONE___ #
Now let's set up a race condition.
ONE___ # update accounts set balance = balance + 100 where acctnum = 11111; UPDATE 1 ONE___ *#
___TWO # update accounts set balance = balance + 100 where acctnum = 22222; UPDATE 1 ___TWO *# update accounts set balance = balance + 100 where acctnum = 11111;
No prompt is not returned here! The `psql` session is blocking!
So let's continue on in session one with another update...
ONE___ *# update accounts set balance = balance + 100 where acctnum = 22222; ERROR: deadlock detected DETAIL: Process 3517 waits for ShareLock on transaction 580; blocked by process 3428. Process 3428 waits for ShareLock on transaction 579; blocked by process 3517. HINT: See server log for query details. CONTEXT: while updating tuple (0,2) in relation "accounts" ONE___ !#
Session one's prompt returns right away with the error. Notice the exclamation point at the prompt to indicate this transaction has had an error and must be committed or rolled back.
Meanwhile, back at user two, we get our prompt back, and we have an asterisk at our prompt, meaning we are still in a (valid) transaction that we can commit:
UPDATE 1 ___TWO *#
So let's do that.
___TWO *# commit; COMMIT ___TWO #
And let's roll back session one's bad transaction:
ONE___ !# rollback; ROLLBACK ONE___ #
Let's check our table:
___TWO # select * from accounts; ┌─────────┬─────────┐ │ acctnum │ balance │ ├─────────┼─────────┤ │ 22222 │ 1100 │ │ 11111 │ 1100 │ └─────────┴─────────┘ (2 rows) ___TWO *# commit; COMMIT ___TWO #
Excellent.
MySQL!
We need two user sessions running. We will distinguish between them by customizing their `mysql` prompts.
User one:
$ mysql --user=root --password --database=mysql mysql> prompt ONE___>\_
User two:
$ mysql --user=root --password --database=mysql mysql> prompt ___TWO>\_
There seems to be no way to make the `mysql` prompt show the status of the current transaction. :-(
From here forward, user one and user two are identified by their prompts.
First, we need to disable autocommit, to give us manual control over transaction commits and rollbacks:
ONE___> set autocommit=0; Query OK, 0 rows affected (0.00 sec) ONE___>
___TWO> set autocommit=0; Query OK, 0 rows affected (0.00 sec) ___TWO>
Now we need to create and populate a table with data:
ONE___> create table accounts(acctnum bigint primary key,
-> balance bigint default 0 not null);
Query OK, 0 rows affected (0.02 sec)
ONE___> commit;
Query OK, 0 rows affected (0.00 sec)
ONE___>
ONE___> insert into accounts(acctnum, balance) values
-> (11111, 1000),
-> (22222, 1000);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
ONE___> commit;
Query OK, 0 rows affected (0.01 sec)
ONE___>
ONE___> select * from accounts;
+---------+---------+
| acctnum | balance |
+---------+---------+
| 11111 | 1000 |
| 22222 | 1000 |
+---------+---------+
2 rows in set (0.00 sec)
ONE___> commit;
Query OK, 0 rows affected (0.00 sec)
ONE___>
Now let's set up a race condition.
ONE___> update accounts set balance = balance + 100 where acctnum = 11111; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 ONE___>
___TWO> update accounts set balance = balance + 100 where acctnum = 22222; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 ___TWO> update accounts set balance = balance + 100 where acctnum = 11111;
NO prompt is not returned here! The `mysql` session is blocking!
So let's continue on in session one with another update...
ONE___> update accounts set balance = balance + 100 where acctnum = 22222; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction ONE___>
Session one's prompt returns right away with the error. Presumably, we need to roll back.
Meanwhile, back at user two, we get our prompt back. Presumably, we can commit.
Query OK, 1 row affected (35.47 sec) Rows matched: 1 Changed: 1 Warnings: 0 ___TWO>
In session two, let's try to commit:
___TWO> commit; Query OK, 0 rows affected (0.01 sec) ___TWO>
In session one, let's try to rollback:
ONE___> rollback; Query OK, 0 rows affected (0.00 sec) ONE___>
Let's check our table:
___TWO> select * from accounts; +---------+---------+ | acctnum | balance | +---------+---------+ | 11111 | 1100 | | 22222 | 1100 | +---------+---------+ 2 rows in set (0.01 sec) ___TWO> commit; Query OK, 0 rows affected (0.00 sec) ___TWO>
That's reassuring.