I Failed the Turing Test

All posts tagged transaction

Locking and MVCC in MySQL InnoDB 鎖機制與多版本並發控制

Locking and MVCC in MySQL InnoDB 鎖機制與多版本並發控制

Posted on Author VintaPosted in Database

最近在研究 MySQL 5.6 / 5.7 InnoDB storage engine 的 Transaction、Locking 和 Multi-Version Concurrency Control (MVCC) 機制，基本上只討論 Isolation level 是 REPEATABLE READ 或 READ COMMITTED 的情況，這一篇是過程中記錄下來的筆記。

ref:
https://dev.mysql.com/doc/refman/5.7/en/innodb-storage-engine.html

Transaction

Transaction Isolation Levels
https://vinta.ws/code/transaction-isolation-levels-in-databases.html

在 autocommit 啟用的情況下（默認），每一個 SQL 語句就是一個 transaction，但是你可以用 START TRANSACTION 或 BEGIN 來手動開啟一個 transaction，然後用 COMMIT 或 ROLLBACK 來結束。

ref:
https://dev.mysql.com/doc/refman/5.7/en/innodb-autocommit-commit-rollback.html

Pessimistic Locking 悲觀鎖 / Optimistic Locking 樂觀鎖

更準確一點的說法是 pessimistic concurrency control 和 optimistic concurrency control。

基本上悲觀鎖、樂觀鎖是一種方法論（或者說是策略），在不同的系統可能有不同的作法，不過這裡就只討論 MySQL。

悲觀鎖假設你在存取某個資料時，其他人同時修改同一份資料的機率很高，也就是對「資料會被修改」這件事持悲觀的態度，所以要在存取（無論讀寫）時就把該資料鎖住，不讓其他人有機會修改。悲觀鎖通常指的是資料庫本身提供的 locking 機制（在 application 層的鎖沒辦法保證不會被修改）。SELECT ... FOR UPDATE; 就是一種悲觀鎖。

樂觀鎖則是對「資料會被修改」這件事持樂觀的態度，通常會使用 version 版本號的方式來實作，在 InnoDB 裡就是指 Multi-Version Concurrency Control (MVCC)。

You can remember which is which by picturing it this way:

  • The pessimistic approach is a user thinking "I’m sure someone will try to update this row at the same time as I will, so I better ask the database server to not let anyone touch it until I’m done with it."
  • The optimistic approach is a user thinking "I’m sure it’s all good and there will be no conflicts, so I’m just going to remember this row to double-check later, but I’ll let the database server do whatever it wants with the row."

ref:
http://stackoverflow.com/questions/129329/optimistic-vs-pessimistic-locking
https://blogs.msdn.microsoft.com/marcelolr/2010/07/16/optimistic-and-pessimistic-concurrency-a-simple-explanation/

Multi-Version Concurrency Control (MVCC)

在 InnoDB 裡是基本上是讀不加鎖，寫才加鎖，而且讀寫不互斥。

InnoDB 會把每個 row 的歷史版本儲存在 rollback segment 裡，rollback segment 的資料除了用在 transaction rollback 的 undo 操作外，會用被用在 consistent read，也就是 snapshot data。

ref:
https://dev.mysql.com/doc/refman/5.7/en/innodb-multi-versioning.html

在所有 InnoDB 的 tables 裡，每個 row 都會有三個隱藏的欄位 DB_ROW_ID、DB_TRX_ID 和 DB_ROLL_PTR：DB_TRX_ID 是建立這個版本的數據的那個 transaction id、DB_ROLL_PTR 就是 rollback data pointer 指向在 undo logs 裡的前一個版本的數據。

TODO:
多說一點細節
例如 SELECT、INSERT、UPDATE 和 DELETE 時的行為

Consistent (Nonlocking) Reads

所謂的 consistent read 是指 read 操作實際上是去讀 snapshot data（該 row 在不同時間點的數據、版本），所以 read 操作不會因為該 row 正在被其他 transaction 修改而被 block 住，snapshot data 就是當前數據的歷史版本。

如果 isolation level 是 REPEATABLE READ 或 READ COMMITTED 的話，SELECT 操作默認就是 consistent read，所以不會加上任何 lock。除非你顯式地加上 SELECT ... LOCK IN SHARE MODE 或 SELECT ... FOR UPDATE（稱為 locking reads）。

ref:
https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_consistent_read
https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_snapshot
https://blogs.oracle.com/mysqlinnodb/entry/repeatable_read_isolation_level_in

至於 INSERT、UPDATE 和 DELETE 操作，MySQL 則會自動對牽涉到的數據加上 lock。

ref:
https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html

不同的 isolation level 在讀 snapshot data 的行為上是不同的。

REPEATABLE READ 在同一個 transaction 內，對同一個 row 的所有 consistent read 讀到的內容都會是第一個 consistent read 時建立的那個 snapshot data，即使其他 transaction 已經 commit 了新的資料，所以才叫 repeatable read。不過如果是在同一個 transaction 內所做的修改則還是會去讀那個修改過的內容，因為是去讀 snapshot。而且 MySQL 的 REPEATABLE READ 還額外地避免了 phantom read。

READ COMMITTED 在同一個 transaction 內，每次去讀同一個 row 都會建立並且讀最新的一份 snapshot data（當然只會讀已經 commit 的資料），先後的 read 可能會讀到不同的內容，所以才會有 non-repeatable read 的問題。

SERIALIZABLE 是讀加讀鎖，寫加寫鎖，而且讀寫互斥。

ref:
https://dev.mysql.com/doc/refman/5.7/en/innodb-consistent-read.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html#isolevel_repeatable-read
https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html#isolevel_read-committed
https://blog.gslin.org/archives/2015/09/27/6007/%E7%B9%BC%E7%BA%8C%E5%AD%B8-isolation/

雖然 MySQL 默認的 isolation level 是 REPEATABLE READ，但是如果你的專案其實不太需要 repeatable reads 的話，用 READ COMMITTED 可能更合適，至少 lock 會比較少。不過 REPEATABLE READ 和 READ COMMITTED 在效能上的差異似乎也沒有想像中那麼大就是了。

ref:
https://www.percona.com/blog/2015/01/14/mysql-performance-implications-of-innodb-isolation-modes/
https://smalldatum.blogspot.tw/2016/11/sysbench-innodb-transaction-isolation.html

Snapshot Read / Current Read

也有一種說法是把「讀」分成 snapshot read 和 current read，不過這種說法似乎只有在中國程序員寫的文章裡流傳，至少我沒有在官方的文件裡找到這兩個名詞。

snapshot read，就是去讀 snapshot data：

  • SELECT

current read，就是去讀最新版本的數據：

  • SELECT ... LOCK IN SHARE MODE
  • SELECT ... FOR UPDATE
  • INSERT
  • UPDATE
  • DELETE

ref:
http://hedengcheng.com/?p=771
http://tech.meituan.com/innodb-lock.html

Lock Modes

Shared Locks 共享鎖 / Exclusive Locks 排他鎖

shared lock (S lock)，也稱為「共享鎖」、「讀鎖」。被加上讀鎖的數據，其他 transaction 可以讀，也可以再加上讀鎖，但是不能寫（因為不能加上寫鎖）。

可以用 SELECT ... LOCK IN SHARE MODE; 顯式地加上 shared lock。MySQL 會對被 SELECT 到的每個 row 加上 shared lock，當然前提是這些 row 沒有被加上 exclusive lock。

ref:
https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_shared_lock

exclusive lock (X lock)，也稱為「排他鎖」、「寫鎖」。被加上寫鎖的數據，其他 transaction 可以讀（因為讀是讀 snapshot data），但是不能再加上任何鎖，所以不能寫（因為不能加上寫鎖），只有加上寫鎖的那個 transaction 才能寫。

可以用 SELECT ... FOR UPDATE; 顯式地加上 exclusive lock，MySQL 會對被 SELECT 到的每個 row 加上 exclusive lock。

ref:
https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_exclusive_lock

Intention Locks 意向鎖

分成 intention shared locks (IS) 和 intention exclusive locks (IX)，用來表示某個 transaction 打算對某個 table 裡的某個 row 加上 shared lock 或 exclusive lock。更明確地說，某個 transaction 必須先取得 table 的 intention lock，它才能對該 table 裡的 row 加上 shared lock 或 exclusive lock。

intention locks 是 MySQL 自動加的，你不需要也沒辦法自己加上這種鎖。

ref:
https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html

另外還有一種 table-level locks，可以透過 LOCK TABLE 顯式地加上：

LOCK TABLES t1 READ;
LOCK TABLES t1 WRITE;

ref:
https://dev.mysql.com/doc/refman/5.7/en/lock-tables.html

Online DDL (Data Definition Language)

ALTER TABLE 操作可能也會有 table-level locks，在 ALTER TABLE 的過程中只能讀，寫則會被 block 直到 ALTER TABLE 完成。不過這點在 MySQL 5.6 / 5.7 以後改善了不少，例如可以用 ALTER TABLE ... ALGORITHM=INPLACE LOCK=NONE; 來指定不同的行為。

ref:
https://dev.mysql.com/doc/refman/5.7/en/alter-table.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl.html

Summary of Online Status for DDL Operations
https://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-overview.html

Lock Types

Locks Set by Different SQL Statements in InnoDB
https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html

Record Locks

record lock 就是加在 index 上的 lock，InnoDB 裡的 row-level lock 實際上是 index 的 record lock。使用 READ COMMITTED 的時候，locking read（即 LOCK IN SHARE MODE 和 FOR UPDATE）、UPDATE 和 DELETE 的 lock 都是 record lock。

TODO:
如果該 table 沒有任何 index

READ COMMITTED 只有 record lock，沒有 gap lock 和 next-key lock，不過 foreign-key constraint 和 duplicate-key 檢查還是會用到 gap lock。

REPEATABLE READ 有 record lock、gap lock 和 next-key lock。

ref:
https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-record-locks
https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html#isolevel_read-committed

Gap Locks

gap lock 是加在 index records 之間的 gap 上的 lock。

ref:
https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-gap-locks

Next-Key Locks

next-key lock 是 record lock + gap lock，next-key lock 可以用來避免 phantom read。

ref:
https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-next-key-locks
https://dev.mysql.com/doc/refman/5.7/en/innodb-next-key-locking.html

Insert Intention Locks

insert intention lock 是 gap lock 的一種，INSERT 之前會先加一個 insert intention lock，但是只要不是要 INSERT 到同一個位置上就不會 block 其他 transaction。

ref:
https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-insert-intention-locks

AUTO-INC Locks

auto-inc lock 是一種 table-level lock，不同種類的 insert-like 語句會有不同的行為。可以透過 innodb_autoinc_lock_mode 設定來改變這些行為。

在 innodb_autoinc_lock_mode = 1（預設值）的情況下，simple inserts（就是那種在執行之前就知道最後會產生幾筆新資料的 inserts）不會有 table-level 的 AUTO-INC lock，只有在取得 auto-increment 值的那瞬間會有 lock。

ref:
https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-auto-inc-locks
https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_autoinc_lock_mode

Django's get_or_create() may raise IntegrityError but subsequent get() raises DoesNotExist

Django's get_or_create() may raise IntegrityError but subsequent get() raises DoesNotExist

Posted on Author VintaPosted in Database, Python, Web Development

Django 的 SomeModel.objects.get_or_create() 實際上的步驟是：

  1. get
  2. if problem: save + some_trickery
  3. if still problem: get again
  4. if still problem: surrender and raise

get_or_create() 拋出 IntegrityError 但是接著 get() 一次卻會是 DoesNotExist，這個錯誤常常會發生在 MySQL 的 AUTOCOMMIT=OFF 且 isolation level 是 REPEATABLE READ 的情況下。MySQL 默認採用 REPEATABLE READ（而 PostgreSQL 預設是 READ COMMITTED），而 REPEATABLE READ 的定義是在同一個 transaction 之內 執行相同的 SELECT 一定會拿到相同的結果。

with transaction.commit_on_success():
    try:
        user, created = User.objects.get_or_create(id=self.id, username=self.username)
    except IntegrityError:
        # 這個 get() 可能會發生 DoesNotExist
        user = User.objects.get(id=self.id)

會發生那個 DoesNotExist 通常是因為：

  1. thread a 執行 get()，但是 DoesNotExist
  2. 所以 thread a 接著 create()，但是在資料建立之前
  3. thread b 幾乎在同一個時間點也執行 get()，也是 DoesNotExist
  4. thread a 成功地 create() 了
  5. 因為在 3 拿不到資料，所以 thread b 也執行 create()，但是因為 UNIQUE CONSTRAINT，導致失敗了
  6. 所以 thread b 又會 get() 一次，但是因為 REPEATABLE READ，thread b 不會拿到 thread a 建立的資料，所以 DoesNotExist
  7. 所以那個 User.DoesNotExist 就是 thread b 拋出來的錯誤
  8. thread a 是成功的，你事後去資料庫裡查詢，會看到那個 User 的資料好端端地在那裡

ref:
http://stackoverflow.com/questions/2235318/how-do-i-deal-with-this-race-condition-in-django
https://blog.ionelmc.ro/2014/12/28/terrible-choices-mysql/

解決的方法，除了就乾脆不要在同一個 transaction 裡之外，基本上就只能把 isolation level 改成 READ COMMITTED 了。似乎沒有其他辦法了，因為這就是 isolation level 的定義。

As of MariaDB/MySQL 5.1, if you use READ COMMITTED or enable innodb_locks_unsafe_for_binlog, you must use row-based binary logging.

SHOW VARIABLES LIKE 'binlog_format';
SET SESSION binlog_format = 'ROW';
SET GLOBAL binlog_format = 'ROW';

ref:
http://dba.stackexchange.com/questions/2678/how-do-i-show-the-binlog-format-on-a-mysql-server

Django 和 Celery 都建議使用 READ COMMITTED
https://docs.djangoproject.com/en/dev/ref/models/querysets/#get-or-create
https://code.djangoproject.com/ticket/13906
https://code.djangoproject.com/ticket/6641
http://docs.celeryproject.org/en/latest/faq.html#mysql-is-throwing-deadlock-errors-what-can-i-do

方法一

SELECT @@GLOBAL.tx_isolation, @@tx_isolation;

# MySQL
SET SESSION tx_isolation='READ-COMMITTED';
SET GLOBAL tx_isolation='READ-COMMITTED';

# MariaDB
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

ref:
http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html

方法二

/etc/mysql/my.cnf 

[mysqld]
transaction-isolation = READ-COMMITTED

方法三

in settings.py

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        ...
        'OPTIONS': {
            'init_command': 'SET SESSION tx_isolation="READ-COMMITTED"',
        },
    }
}

方法四

自己寫一個 get_or_create()

from django.db import transaction

@transaction.atomic
# or
@transaction.commit_on_success
def my_get_or_create(...):
    try:
        obj = MyObj.objects.create(...)
    except IntegrityError:
        transaction.commit()
        obj = MyObj.objects.get(...)
    return obj

ref:
http://stackoverflow.com/questions/2235318/how-do-i-deal-with-this-race-condition-in-django

MySQL 默認是 AUTOCOMMIT
http://dev.mysql.com/doc/refman/5.5/en/commit.html

Django 默認也是 AUTOCOMMIT，除非你使用了 TransactionMiddleware（Django 1.5 以前）或是設置 ATOMIC_REQUESTS = False（Django 1.6 以後）。TransactionMiddleware 的效果類似 @transaction.commit_on_success 或 @transaction.atomic，都是把 AUTOCOMMIT 關掉，差別在於範圍不同。

ref:
http://django.readthedocs.io/en/1.5.x/topics/db/transactions.html#django-s-default-transaction-behavior
http://django.readthedocs.io/en/1.6.x/topics/db/transactions.html#managing-autocommit
http://django.readthedocs.io/en/1.6.x/ref/settings.html#std:setting-DATABASE-ATOMIC_REQUESTS

Transaction isolation levels in MySQL

Transaction isolation levels in MySQL

Posted on Author VintaPosted in Database

Dirty reads 髒讀

Transaction 1 讀到了 Transaction 2 的未提交的值
READ COMMITED 可以避免這個問題
READ UNCOMMITTED 會有這個問題

A dirty read occurs when a transaction is allowed to read data from a row that has been modified by another running transaction and not yet committed.

Non-repeatable reads 不可重複讀

在同一個 Transaction 裡 不同的時間點 讀取了同一個 row 卻得到了不同的值（但是可能使用了不同的 SELECT 語句去讀到同一個 row）
REPEATABLE READ 可以避免這個問題
READ COMMITED 會有這個問題

A non-repeatable read occurs, when during the course of a transaction, a row is retrieved twice and the values within the row differ between reads.

When Transaction 2 commits successfully, which means that its changes to the row with id 1 should become visible. However, Transaction 1 has already seen a different value for age in that row. At the SERIALIZABLE and REPEATABLE READ isolation levels, the DBMS must return the old value for the second SELECT. At READ COMMITTED and READ UNCOMMITTED, the DBMS may return the updated value; this is a non-repeatable read.

Phantom reads 幻讀

有點類似 Non-repeatable reads
在同一個 Transaction 裡 不同的時間點 兩個相同但是先後執行的 SQL 卻得到了不同的結果
差別在於 Phantom reads 專門指沒有用 range locks 時
先後兩個同樣的 SELECT 卻返回了不同數量的結果
SERIALIZABLE 可以避免以上的三種問題

也可以說 non-repeatable reads 的原因是 UPDATE
而 phantom reads 的原因則是 INSERT 和 DELETE

A phantom read occurs when, in the course of a transaction, two identical queries are executed, and the collection of rows returned by the second query is different from the first.

READ UNCOMMITTED

允許 dirty reads
可能會讀到其他 transaction 尚未提交的數據

READ COMMITED

不會有 dirty reads
只會讀到其他 transaction 已經提交的數據
但是允許 non-repeatable reads

大部份資料庫默認的 isolation level 都是 READ COMMITED

REPEATABLE READ

不會有 dirty reads 和 non-repeatable reads
但是允許 phantom reads

MySQL 默認的 isolation level 是 REPEATABLE READ
但是 InnoDB 透過 next-key lock 也防止了 phantom reads

SERIALIZABLE

不會有 dirty reads、non-repeatable reads 和 phantom reads
讀寫互斥
所以讀和寫都只能一個一個依序執行

ref:
https://www.percona.com/blog/2012/08/28/differences-between-read-committed-and-repeatable-read-transaction-isolation-levels/