transaction isolation levels

gistfile1.txt

mysql> describe account;
+----------+------------------+------+-----+-------------------+-------------------+
| Field    | Type             | Null | Key | Default           | Extra             |
+----------+------------------+------+-----+-------------------+-------------------+
| id       | int(10) unsigned | NO   | PRI | NULL              | auto_increment    |
| username | varchar(255)     | NO   |     | NULL              |                   |
| ts       | timestamp        | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+----------+------------------+------+-----+-------------------+-------------------+
3 rows in set (0.01 sec)

mysql> select * from account;
+----+----------+---------------------+
| id | username | ts                  |
+----+----------+---------------------+
|  1 | John     | 2019-06-14 14:42:59 |
+----+----------+---------------------+
1 row in set (0.00 sec)

Isolation level is REPEATABLE READ

Client #1
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update account set username = 'Mary' where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from account;
+----+----------+---------------------+
| id | username | ts                  |
+----+----------+---------------------+
|  1 | Mary     | 2019-06-14 14:42:59 |
+----+----------+---------------------+
1 row in set (0.00 sec)

Client #2
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+----+----------+---------------------+
| id | username | ts                  |
+----+----------+---------------------+
|  1 | John     | 2019-06-14 14:42:59 |
+----+----------+---------------------+
1 row in set (0.00 sec)

Client #1
mysql> commit;
Query OK, 0 rows affected (0.01 sec)

Client #2
mysql> select * from account;
+----+----------+---------------------+
| id | username | ts                  |
+----+----------+---------------------+
|  1 | John     | 2019-06-14 14:42:59 |
+----+----------+---------------------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+----+----------+---------------------+
| id | username | ts                  |
+----+----------+---------------------+
|  1 | Mary     | 2019-06-14 14:42:59 |
+----+----------+---------------------+
1 row in set (0.00 sec)

Isolation level is READ COMMITTED

mysql> select * from account;
+----+----------+---------------------+
| id | username | ts                  |
+----+----------+---------------------+
|  1 | Mary     | 2019-06-14 14:42:59 |
+----+----------+---------------------+
1 row in set (0.00 sec)

Client #1

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update account set username = 'Joseph';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Client #2

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+----+----------+---------------------+
| id | username | ts                  |
+----+----------+---------------------+
|  1 | Mary     | 2019-06-14 14:42:59 |
+----+----------+---------------------+
1 row in set (0.00 sec)

Client #1

mysql> select * from account;
+----+----------+---------------------+
| id | username | ts                  |
+----+----------+---------------------+
|  1 | Joseph   | 2019-06-14 14:42:59 |
+----+----------+---------------------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

Client #2

mysql> select * from account;
+----+----------+---------------------+
| id | username | ts                  |
+----+----------+---------------------+
|  1 | Joseph   | 2019-06-14 14:42:59 |
+----+----------+---------------------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)