iT邦幫忙

2023 iThome 鐵人賽
DAY 13
0
自我挑戰組

Techschool Goalng Backend Master Class 的學習記錄系列 第 13 篇

[Day 13] Understand isolation levels & read phenomena in MySQL

15th鐵人賽 mysql isolation level transaction golang
286 瀏覽

  • 分享至 

  • xImage
    •  

Isolation Levels in MySQL

  • 建立MySQL Container 與 simple_bank database.

    docker run --name mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=secret --network bank-network -d mysql:8
docker exec -it mysql mysql -u root -psecret -e "CREATE DATABASE simple_bank;"

  • Connect to MySQL and access simple_bankdatabase

    docker exec -it mysql mysql -uroot -psecret simple_bank

  • Get current isolation level in MySQL

    • 不管console session 或是global session 預設都是**Repeatable Read**
    • select @@transaction_isolation; 這個SQL指令時，所獲得的隔離級別是針對當前的console session Level**Repeatable Read**
    mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)
    • select @@global.transaction_isolation 這個SQL指令，用於查詢MySQL伺服器的全局隔離級別設定所獲得的隔離級別**Repeatable Read**
    mysql> select @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| REPEATABLE-READ                |
+--------------------------------+
1 row in set (0.00 sec)

  • Change isolation level in MySQL (current session)

    mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-UNCOMMITTED        |
+-------------------------+
1 row in set (0.00 sec)

  • 建立accounts、entries、transfers Table

    CREATE TABLE `accounts` (
  `id` BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `owner` VARCHAR(255) NOT NULL,
  `balance` BIGINT NOT NULL,
  `currency` VARCHAR(255) NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE `entries` (
  `id` BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `account_id` BIGINT NOT NULL,
  `amount` BIGINT NOT NULL COMMENT 'can be negative or positive',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE `transfers` (
  `id` BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `from_account_id` BIGINT NOT NULL,
  `to_account_id` BIGINT NOT NULL,
  `amount` BIGINT NOT NULL COMMENT 'must be positive',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX `accounts_index_0` ON `accounts` (`owner`);

CREATE UNIQUE INDEX `accounts_index_1` ON `accounts` (`owner`, `currency`);

CREATE INDEX `entries_index_2` ON `entries` (`account_id`);

CREATE INDEX `transfers_index_3` ON `transfers` (`from_account_id`);

CREATE INDEX `transfers_index_4` ON `transfers` (`to_account_id`);

CREATE INDEX `transfers_index_5` ON `transfers` (`from_account_id`, `to_account_id`);

ALTER TABLE `entries` ADD FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`);

ALTER TABLE `transfers` ADD FOREIGN KEY (`from_account_id`) REFERENCES `accounts` (`id`);

ALTER TABLE `transfers` ADD FOREIGN KEY (`to_account_id`) REFERENCES `accounts` (`id`);

  • 插入三筆Account的資訊進到accounts

    INSERT INTO `accounts` (`owner`, `balance`, `currency`) VALUES
('one', 100, 'USD'),
('two', 100, 'USD'),
('three', 100, 'USD');

mysql> select * from accounts;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at          |
+----+-------+---------+----------+---------------------+
|  1 | one   |     100 | USD      | 2023-08-23 07:05:50 |
|  2 | two   |     100 | USD      | 2023-08-23 07:05:50 |
|  3 | three |     100 | USD      | 2023-08-23 07:05:50 |
+----+-------+---------+----------+---------------------+
3 rows in set (0.01 sec)

Read uncommitted isolation level in MySQL

  • 建立兩個Console，並設定兩個Session的Isolation Level為**Read Uncommitted**

    -- Tx1:
mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-UNCOMMITTED        |
+-------------------------+
1 row in set (0.00 sec)

-- Tx2:
mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-UNCOMMITTED        |
+-------------------------+
1 row in set (0.00 sec)

  • 建立Transaction

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

-- Tx2
mysql> begin;
Query OK, 0 rows affected (0.01 sec)

  • 在Tx1 中對accounts table進行select query

    -- Tx1
mysql> select * from accounts;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at          |
+----+-------+---------+----------+---------------------+
|  1 | one   |     100 | USD      | 2023-08-23 07:05:50 |
|  2 | two   |     100 | USD      | 2023-08-23 07:05:50 |
|  3 | three |     100 | USD      | 2023-08-23 07:05:50 |
+----+-------+---------+----------+---------------------+

  • 在Tx2 中對accounts table進行id=1的 query

    -- Tx2
mysql> select * from accounts where id = 1;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at          |
+----+-------+---------+----------+---------------------+
|  1 | one   |     100 | USD      | 2023-08-23 07:05:50 |
+----+-------+---------+----------+---------------------+
1 row in set (0.00 sec)

  • 這時候在Tx1 中對Account 1的balance減去10

    -- Tx1
mysql> update accounts set balance = balance - 10 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

  • 最後我們在Tx1 與 Tx2 再次對Account 1進行Query

    • 這裡有一點重點是Tx1 尚未committed 但是Tx2 已經能看到Account 1的更新了
    • 這樣的行為就被稱為dirty-read 並且只會發生在read-uncommitted isolation level
    -- Tx1
mysql> select * from accounts where id = 1;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at          |
+----+-------+---------+----------+---------------------+
|  1 | one   |      90 | USD      | 2023-08-23 07:05:50 |
+----+-------+---------+----------+---------------------+
1 row in set (0.00 sec)

-- Tx2
mysql> select * from accounts where id = 1;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at          |
+----+-------+---------+----------+---------------------+
|  1 | one   |      90 | USD      | 2023-08-23 07:05:50 |
+----+-------+---------+----------+---------------------+
1 row in set (0.00 sec)

Read committed isolation level in MySQL

  • 建立兩個Console，並設定兩個Session的Isolation Level為**Read Committed**

    -- Tx1:
mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-COMMITTED          |
+-------------------------+
1 row in set (0.00 sec)

-- Tx2:
mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-COMMITTED          |
+-------------------------+
1 row in set (0.00 sec)

  • 建立Transaction

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

-- Tx2
mysql> begin;
Query OK, 0 rows affected (0.01 sec)

  • 在Tx1 中對accounts table進行select query

    -- Tx1
mysql> select * from accounts;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at          |
+----+-------+---------+----------+---------------------+
|  1 | one   |     90 | USD      | 2023-08-23 07:05:50 |
|  2 | two   |     100 | USD      | 2023-08-23 07:05:50 |
|  3 | three |     100 | USD      | 2023-08-23 07:05:50 |
+----+-------+---------+----------+---------------------+

  • 在Tx2 中對accounts table進行id=1的 query

    -- Tx2
mysql> select * from accounts where id = 1;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at          |
+----+-------+---------+----------+---------------------+
|  1 | one   |     90 | USD      | 2023-08-23 07:05:50 |
+----+-------+---------+----------+---------------------+
1 row in set (0.00 sec)

  • 這時候在Tx1 中對Account 1的balance減去10

    -- Tx1
mysql> update accounts set balance = balance - 10 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from accounts where id = 1;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at          |
+----+-------+---------+----------+---------------------+
|  1 | one   |      80 | USD      | 2023-08-23 07:05:50 |
+----+-------+---------+----------+---------------------+
1 row in set (0.00 sec)

  • 我們在Tx2 再次對Account 1進行Query

    • 這時Tx1 尚未committed ，所以Tx2 的Account 1 Query 依舊是90
    • 因此read-committed 可以避免dirty read 
    -- Tx2
mysql> select * from accounts where id = 1;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at          |
+----+-------+---------+----------+---------------------+
|  1 | one   |      90 | USD      | 2023-08-23 07:05:50 |
+----+-------+---------+----------+---------------------+
1 row in set (0.00 sec)

  • How about non-repeatable and phantom read?

    • non-repeatable :

      -- Tx2:
mysql> select * from accounts where balance >= 90;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at          |
+----+-------+---------+----------+---------------------+
|  1 | one   |      90 | USD      | 2023-08-23 07:05:50 |
|  2 | two   |     100 | USD      | 2023-08-23 07:05:50 |
|  3 | three |     100 | USD      | 2023-08-23 07:05:50 |
+----+-------+---------+----------+---------------------+
3 rows in set (0.00 sec)

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

      再回到Tx2 對Account 1 進行查詢，可以發現和之前所獲得Value(90)不相同，這就被稱為non-repeatable (請對照 “我們在Tx2 再次對Account 1進行Query”)

      -- Tx2
mysql> select * from accounts where id = 1;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at          |
+----+-------+---------+----------+---------------------+
|  1 | one   |      80 | USD      | 2023-08-23 07:05:50 |
+----+-------+---------+----------+---------------------+
1 row in set (0.00 sec)

    • phantom read
      同樣我們在Tx2 進行同一個Query取得balance 90以上的Accounts，可以發現相同的Query但是Return 的Record 數量不相同，這就被稱為phantom read

      mysql> select * from accounts where balance >= 90;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at          |
+----+-------+---------+----------+---------------------+
|  2 | two   |     100 | USD      | 2023-08-23 07:05:50 |
|  3 | three |     100 | USD      | 2023-08-23 07:05:50 |
+----+-------+---------+----------+---------------------+
2 rows in set (0.00 sec)

  • 所以read-committed 只能避免dirty read 無法避開non-repeatable 、phantom read

Repeatable read isolation level in MySQL

  • 建立兩個Console，並設定兩個Session的Isolation Level為**Repeatable**

    -- Tx1:
mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

-- Tx2:
mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

  • 建立Transaction

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

-- Tx2
mysql> begin;
Query OK, 0 rows affected (0.01 sec)

  • 在Tx1 中對accounts table進行select query

    -- Tx1
mysql> select * from accounts;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at          |
+----+-------+---------+----------+---------------------+
|  1 | one   |      80 | USD      | 2023-08-23 07:05:50 |
|  2 | two   |     100 | USD      | 2023-08-23 07:05:50 |
|  3 | three |     100 | USD      | 2023-08-23 07:05:50 |
+----+-------+---------+----------+---------------------+
3 rows in set (0.00 sec)

  • 在Tx2 中對accounts table進行id=1的查詢與balance ≥80 的Query

    -- Tx2
mysql> select * from accounts where id = 1;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at          |
+----+-------+---------+----------+---------------------+
|  1 | one   |      80 | USD      | 2023-08-23 07:05:50 |
+----+-------+---------+----------+---------------------+
1 row in set (0.00 sec)

mysql> select * from accounts where balance >= 80;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at          |
+----+-------+---------+----------+---------------------+
|  1 | one   |      80 | USD      | 2023-08-23 07:05:50 |
|  2 | two   |     100 | USD      | 2023-08-23 07:05:50 |
|  3 | three |     100 | USD      | 2023-08-23 07:05:50 |
+----+-------+---------+----------+---------------------+
3 rows in set (0.00 sec)

  • 這時候在Tx1 中對Account 1的balance減去10

    • 我們知道在隔離級別「read committed」中已經阻止了dirty read , 所以就不需要再驗證
    -- Tx1
mysql> update accounts set balance = balance - 10 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from accounts;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at          |
+----+-------+---------+----------+---------------------+
|  1 | one   |      70 | USD      | 2023-08-23 07:05:50 |
|  2 | two   |     100 | USD      | 2023-08-23 07:05:50 |
|  3 | three |     100 | USD      | 2023-08-23 07:05:50 |
+----+-------+---------+----------+---------------------+
3 rows in set (0.00 sec)

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

  • 我們在Tx2 再次對Account 1進行查詢，來確認是否可以獲得Tx1 所更新的balance

    • 這裡可以發現select query return Account 1的舊版本，balance為80美元，儘管Tx1已經將其更改為70並且成功commit。
    • 這是因為repeatable-read isolation level 確保所有的讀取查詢都是repeatable，這意味著，它總是返回相同的結果，即使其他已提交的交易進行了更改 (避免non-repeatable)。
    • 在進行balance ≥80 的Query 返回的Record數量依舊相同 (避免phantom read)。
    -- Tx2
mysql> select * from accounts where id = 1;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at          |
+----+-------+---------+----------+---------------------+
|  1 | one   |      80 | USD      | 2023-08-23 07:05:50 |
+----+-------+---------+----------+---------------------+
1 row in set (0.00 sec)

mysql> select * from accounts where balance >= 80;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at          |
+----+-------+---------+----------+---------------------+
|  1 | one   |      80 | USD      | 2023-08-23 07:05:50 |
|  2 | two   |     100 | USD      | 2023-08-23 07:05:50 |
|  3 | three |     100 | USD      | 2023-08-23 07:05:50 |
+----+-------+---------+----------+---------------------+
3 rows in set (0.00 sec)

  • 如果我們在Tx2 對Account 1 的balance那結果會是如何?

    • 帳戶的餘額會變為60美元，因為交易1在此之前已經提交了將餘額修改為70美元的更改。
    • 但從交易2的角度看，這不合邏輯。在上一個選擇查詢中，它看到了80美元的餘額，但在從帳戶中扣除10美元後，現在得到60美元。這裡的數學運算不起作用，因為這個交易仍然受到其他交易的同時更新的干擾。
    • 我不知道為什麼MySQL會選擇以這種方式實現可重複讀隔離級別，但在這種情況下，通過引發錯誤來拒絕更改可能更有意義，以確保交易數據的一致性。
    • 讓我們rollback這個交易，並嘗試進入最高隔離級別，看看這個問題是否可以被防止。
    mysql> update accounts set balance = balance - 10 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from accounts where id = 1;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at          |
+----+-------+---------+----------+---------------------+
|  1 | one   |      60 | USD      | 2023-08-23 07:05:50 |
+----+-------+---------+----------+---------------------+
1 row in set (0.00 sec)

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

Serialization anomaly in MySQL

  • MySQL使用locking mechanism來避免**Serialization anomaly**

  • 建立兩個Console，並設定兩個Session的Isolation Level為**Repeatable**

    -- Tx1:
mysql> set session transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)

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

-- Tx2:
mysql> set session transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)

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

  • 建立Transaction

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

-- Tx2
mysql> begin;
Query OK, 0 rows affected (0.01 sec)

  • 在Tx1 中對accounts table進行select query，並進行Sum 和Insert

    -- Tx1
mysql> select * from accounts;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at          |
+----+-------+---------+----------+---------------------+
|  1 | one   |      60 | USD      | 2023-08-23 07:05:50 |
|  2 | two   |     100 | USD      | 2023-08-23 07:05:50 |
|  3 | three |     100 | USD      | 2023-08-23 07:05:50 |
+----+-------+---------+----------+---------------------+
3 rows in set (0.00 sec)

mysql> select sum(balance) from accounts;
+--------------+
| sum(balance) |
+--------------+
|          260 |
+--------------+
1 row in set (0.00 sec)

mysql> insert into accounts (owner, balance, currency) values ('sum', 260, 'USD');
Query OK, 1 row affected (0.00 sec)

mysql> select * from accounts;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at          |
+----+-------+---------+----------+---------------------+
|  1 | one   |      60 | USD      | 2023-08-23 07:05:50 |
|  2 | two   |     100 | USD      | 2023-08-23 07:05:50 |
|  3 | three |     100 | USD      | 2023-08-23 07:05:50 |
|  4 | sum   |     260 | USD      | 2023-08-23 12:36:59 |
+----+-------+---------+----------+---------------------+
4 rows in set (0.00 sec)

  • 在Tx2 中對accounts table進行Select Query

    -- Tx2
mysql> select * from accounts;

  • 會發現這個查詢被blocked了，它需要等待Tx1釋放lock後才能繼續。

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

--Tx2
mysql> select * from accounts;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at          |
+----+-------+---------+----------+---------------------+
|  1 | one   |      60 | USD      | 2023-08-23 07:05:50 |
|  2 | two   |     100 | USD      | 2023-08-23 07:05:50 |
|  3 | three |     100 | USD      | 2023-08-23 07:05:50 |
|  4 | sum   |     260 | USD      | 2023-08-23 12:36:59 |
+----+-------+---------+----------+---------------------+
4 rows in set (1.79 sec)

  • 這時候可以再對Tx2 中對accounts table進行select query，並進行Sum 和Insert

    -- Tx1
mysql> select * from accounts;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at          |
+----+-------+---------+----------+---------------------+
|  1 | one   |      60 | USD      | 2023-08-23 07:05:50 |
|  2 | two   |     100 | USD      | 2023-08-23 07:05:50 |
|  3 | three |     100 | USD      | 2023-08-23 07:05:50 |
|  4 | sum   |     260 | USD      | 2023-08-23 12:36:59 |
+----+-------+---------+----------+---------------------+
4 rows in set (0.00 sec)

mysql> select sum(balance) from accounts;
+--------------+
| sum(balance) |
+--------------+
|          520 |
+--------------+
1 row in set (0.00 sec)

mysql> insert into accounts (owner, balance, currency) values ('sum', 520, 'USD');
Query OK, 1 row affected (0.00 sec)

mysql> select * from accounts;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at          |
+----+-------+---------+----------+---------------------+
|  1 | one   |      60 | USD      | 2023-08-23 07:05:50 |
|  2 | two   |     100 | USD      | 2023-08-23 07:05:50 |
|  3 | three |     100 | USD      | 2023-08-23 07:05:50 |
|  4 | sum   |     260 | USD      | 2023-08-23 12:36:59 |
|  5 | sum   |     520 | USD      | 2023-08-23 13:36:59 |
+----+-------+---------+----------+---------------------+
5 rows in set (0.01 sec)

  • There’s no duplicate sum records. So MySQL has also successfully prevented the serialization anomaly with its locking mechanism.

  • 接下來我現在我想在這兩個交易中嘗試不同的查詢順序

    -- Tx1:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from accounts;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at          |
+----+-------+---------+----------+---------------------+
|  1 | one   |      60 | USD      | 2020-09-06 15:09:38 |
|  2 | two   |     100 | USD      | 2020-09-06 15:09:38 |
|  3 | three |     100 | USD      | 2020-09-06 15:09:38 |
|  4 | sum   |     260 | USD      | 2020-09-15 14:36:20 |
|  5 | sum   |     520 | USD      | 2020-09-15 14:39:21 |
+----+-------+---------+----------+---------------------+
5 rows in set (0.00 sec)

mysql> select sum(balance) from accounts;
+--------------+
| sum(balance) |
+--------------+
|         1040 |
+--------------+
1 row in set (0.00 sec)

--Tx2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from accounts;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at          |
+----+-------+---------+----------+---------------------+
|  1 | one   |      60 | USD      | 2020-09-06 15:09:38 |
|  2 | two   |     100 | USD      | 2020-09-06 15:09:38 |
|  3 | three |     100 | USD      | 2020-09-06 15:09:38 |
|  4 | sum   |     260 | USD      | 2020-09-15 14:36:20 |
|  5 | sum   |     520 | USD      | 2020-09-15 14:39:21 |
+----+-------+---------+----------+---------------------+
5 rows in set (0.00 sec)

mysql> select sum(balance) from accounts;
+--------------+
| sum(balance) |
+--------------+
|         1040 |
+--------------+
1 row in set (0.00 sec)

  • 這次兩個交易都有相同的總和，為1040美元, 讓我們在Tx1中插入一個帶有此總和的新帳戶

    • 會發現出現blocked，因為Tx2 持有一個share lock，阻止其他交易進行更新。
    -- Tx1:
mysql> insert into accounts (owner, balance, currency) values ('sum', 1040, 'USD');
_

  • 現在如果我們嘗試在Tx2中插入一個新的總和帳戶

    • 會發生DeadLock，因為Tx1 和 Tx2 在互相等待
    -- Tx2:
mysql> insert into accounts (owner, balance, currency) values ('sum', 1040, 'USD');
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

  • 所以在Tx1 進行Commit後就可以正常運作

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

mysql> select * from accounts;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at          |
+----+-------+---------+----------+---------------------+
|  1 | one   |      60 | USD      | 2020-09-06 15:09:38 |
|  2 | two   |     100 | USD      | 2020-09-06 15:09:38 |
|  3 | three |     100 | USD      | 2020-09-06 15:09:38 |
|  4 | sum   |     260 | USD      | 2020-09-15 14:36:20 |
|  5 | sum   |     520 | USD      | 2020-09-15 14:39:21 |
|  6 | sum   |    1040 | USD      | 2020-09-15 14:41:26 |
+----+-------+---------+----------+---------------------+
6 rows in set (0.00 sec)

  • The database stays consistent with no serialization anomaly.

Serializable isolation level in MySQL

  • 建立兩個Console，並設定兩個Session的Isolation Level為**Repeatable**

    -- Tx1:
mysql> set session transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)

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

-- Tx2:
mysql> set session transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)

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

  • 建立Transaction

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

-- Tx2
mysql> begin;
Query OK, 0 rows affected (0.01 sec)

  • 在Tx1 中對accounts table進行select query

    -- Tx1
mysql> select * from accounts;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at          |
+----+-------+---------+----------+---------------------+
|  1 | one   |      70 | USD      | 2023-08-23 07:05:50 |
|  2 | two   |     100 | USD      | 2023-08-23 07:05:50 |
|  3 | three |     100 | USD      | 2023-08-23 07:05:50 |
+----+-------+---------+----------+---------------------+
3 rows in set (0.00 sec)

  • 在Tx2 中對accounts table進行id=1的查詢

    -- Tx2
mysql> select * from accounts where id = 1;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at          |
+----+-------+---------+----------+---------------------+
|  1 | one   |      70 | USD      | 2020-09-06 15:09:38 |
+----+-------+---------+----------+---------------------+
1 row in set (0.00 sec)

  • 這時候在Tx1 中對Account 1的balance減去10

    • 可以發現update query 被blocked
    • 這是因為Tx2中的Select查詢正在阻擋Tx1中的這個更新查詢。
    -- Tx1
mysql> update accounts set balance = balance - 10 where id = 1;
    • 如果Tx2 沒有rollback 或是 commit來進行release，就會出現lock wait timeout error
    • 所以在使用serializable isolation level時要確保實現transaction retry strategy 避免timeout 
    -- Tx1
mysql> update accounts set balance = balance - 10 where id = 1;

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

  • 再來Restart Tx1 並進行Select Query 和Account 1的Update

    --Tx1
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

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

mysql> select * from accounts;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at          |
+----+-------+---------+----------+---------------------+
|  1 | one   |      70 | USD      | 2023-08-23 07:05:50 |
|  2 | two   |     100 | USD      | 2023-08-23 07:05:50 |
|  3 | three |     100 | USD      | 2023-08-23 07:05:50 |
+----+-------+---------+----------+---------------------+
3 rows in set (0.01 sec)

mysql> update accounts set balance = balance - 10 where id = 1;

  • 我們再回到Tx2 對Account 1 進行Update

    • 發現出現了DeadLock ，因為Tx2 也需要等待Tx1的Select Query Lock
    • 所以請注意，除了lock wait timeout外，您還需要注意可能的DeadLock情況
    --Tx2

mysql> update accounts set balance = balance - 10 where id = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

--Tx1

mysql> update accounts set balance = balance - 10 where id = 1;
Query OK, 1 row affected (48.33 sec)
Rows matched: 1  Changed: 1  Warnings: 0

  • 讓我們對Tx1 和 Tx2 進行重啟，並對Account 1 進行Select

    -- Tx1:
mysql> rollback;
Query OK, 0 rows affected (0.03 sec)

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

mysql> select * from accounts where id = 1;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at          |
+----+-------+---------+----------+---------------------+
|  1 | one   |      70 | USD      | 2023-08-23 07:05:50 |
+----+-------+---------+----------+---------------------+
1 row in set (0.00 sec)

-- Tx2:
mysql> rollback;
Query OK, 0 rows affected (0.03 sec)

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

mysql> select * from accounts where id = 1;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at          |
+----+-------+---------+----------+---------------------+
|  1 | one   |      70 | USD      | 2023-08-23 07:05:50 |
+----+-------+---------+----------+---------------------+
1 row in set (0.00 sec)

  • 讓我們在Tx1 更新Account 1 的Balance ，並且在 Tx2 進行commit

    -- Tx1:
mysql> update accounts set balance = balance - 10 where id = 1;

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

### After Commit

-- Tx1:
mysql> update accounts set balance = balance - 10 where id = 1;
Query OK, 1 row affected (5.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from accounts where id = 1;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at          |
+----+-------+---------+----------+---------------------+
|  1 | one   |      60 | USD      | 2023-08-23 07:05:50 |
+----+-------+---------+----------+---------------------+
1 row in set (0.00 sec)

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

上一篇
[Day 12] Understand isolation levels & read phenomena
下一篇
[Day 14] Understand isolation levels & read phenomena in PostgreSQL
系列文
Techschool Goalng Backend Master Class 的學習記錄 共 31 篇
  1. 27
    [Day 27] Add users table with unique & foreign key constraints in PostgreSQL
  2. 28
    [Day 28] How to handle DB errors in Golang correctly
  3. 29
    [Day 29] How to securely store passwords?
  4. 30
    [Day 30] How to write stronger unit tests with a custom go-mock matcher
  5. 31
    [Day 31] Why PASETO is better than JWT for token-based authentication?
完整目錄
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言
iThome鐵人賽
參賽組數
126 組
團體組數
1 組
累計文章數
172 篇
最後報名日
9/15
iT+看影片追技術 看影片追技術 看更多
熱門tag 看更多
15th鐵人賽 13th鐵人賽 14th鐵人賽 12th鐵人賽 11th鐵人賽 鐵人賽 2019鐵人賽 2018鐵人賽 javascript 2017鐵人賽 python windows php c# windows server linux css react 程式設計 vue.js
熱門問題
熱門回答
熱門文章
IT邦幫忙