MySQL 8.0 Reference Manual（读书笔记64节--InnoDBTransaction Model）

拾柒號

The InnoDB transaction model aims to combine the best properties【ˈprɑpərtiz 财产;特性;房地产;不动产;财物;庄园;所有物;房屋及院落;】 of a multi-versioning database with traditional two-phase locking. InnoDB performs locking at the row level and runs queries as nonlocking consistent reads by default, in the style of Oracle. The lock information in InnoDB is stored space-efficiently so that lock escalation【ˌeskəˈleɪʃn 升级;增加;扩大;逐步上升;价格调整;】 is not needed. Typically, several【ˈsevrəl 不同的，各种各样的;大量的，许多的;各自的，分别的;单个的;】 users are permitted to lock every row in InnoDB tables, or any random subset of the rows, without causing InnoDB memory exhaustion【ɪɡˈzɔːstʃən 疲惫;筋疲力尽;耗尽;枯竭;】.
1 Transaction Isolation Levels

Transaction isolation is one of the foundations【faʊnˈdeɪʃənz 基础;基金会;地基;根据;基本原理;房基;】 of database processing. Isolation is the I in the acronym【ˈækrənɪm 缩略语;】 ACID; the isolation level is the setting that fine-tunes the balance between performance and reliability【rɪˌlaɪə'bɪləti 可靠性;】, consistency【kənˈsɪstənsi 一致性;连贯性;黏稠度;密实度;坚实度;平滑度;】, and reproducibility【ˌriprəˌdusɪˈbɪlɪti 再现性;重复性;复现性;还原性;】 of results when multiple transactions are making changes and performing queries at the same time.
InnoDB offers all four transaction isolation levels described by the SQL:1992 standard: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. The default isolation level for InnoDB is REPEATABLE READ.--四种隔离级别
A user can change the isolation level for a single session or for all subsequent connections with the SET TRANSACTION statement. To set the server's default isolation level for all connections, use the -- transaction-isolation option on the command line or in an option file.--隔离级别的设置
 InnoDB supports each of the transaction isolation levels described here using different locking strategies. You can enforce a high degree of consistency with the default REPEATABLE READ level, for operations on crucial【ˈkruːʃl 关键的;至关重要的;关键性的;】 data where ACID compliance【kəmˈplaɪəns 遵从;服从;顺从;】 is important. Or you can relax the consistency rules with READ COMMITTED or even READ UNCOMMITTED, in situations such as bulk reporting where precise【prɪˈsaɪs 准确的;精确的;确切的;明确的;精细的;(强调时间或方式等)就，恰好;细致的;认真的;一丝不苟的;】 consistency and repeatable results are less important than minimizing the amount of overhead for locking. SERIALIZABLE enforces even stricter rules than REPEATABLE READ, and is used mainly in specialized situations, such as with XA transactions and for troubleshooting issues with concurrency and deadlocks.
The following list describes how MySQL supports the different transaction levels. The list goes from the most commonly used level to the least used.
• REPEATABLE READ

默认的隔离级别
This is the default isolation level for InnoDB. Consistent【kənˈsɪstənt 一致的;与…一致的;符合的;持续的;相符的;连续的;始终如一的;不矛盾的;相互连贯的;】 reads within the same transaction read the snapshot established by the first read. This means that if you issue several plain (nonlocking) SELECT statements within the same transaction, these SELECT statements are consistent also with respect【rɪˈspekt 尊敬，尊重，敬重;（事物的）方面;关系，关联;注重，重视;敬意，问候;细节;着眼点;】 to each other.
For locking reads (SELECT with FOR UPDATE or FOR SHARE), UPDATE, and DELETE statements, locking depends on whether the statement uses a unique index with a unique search condition, or a range-type search condition.
• For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it.
• For other search conditions, InnoDB locks the index range scanned, using gap locks or next-key locks to block insertions by other sessions into the gaps covered by the range.
• READ COMMITTED

Each consistent read, even within the same transaction, sets and reads its own fresh snapshot.
For locking reads (SELECT with FOR UPDATE or FOR SHARE), UPDATE statements, and DELETE statements, InnoDB locks only index records, not the gaps before them, and thus permits the free insertion of new records next to locked records. Gap locking is only used for foreign-key constraint checking and duplicate-key checking.
Because gap locking is disabled, phantom【ˈfæntəm 幻影;幽灵;幻象;幻觉;鬼魂;鬼;】 row problems may occur, as other sessions can insert new rows into the gaps.
Only row-based binary logging is supported with the READ COMMITTED isolation level. If you use READ COMMITTED with binlog_format=MIXED, the server automatically uses row-based logging.--这一点需要特别注意
Using READ COMMITTED has additional effects:
 • For UPDATE or DELETE statements, InnoDB holds locks only for rows that it updates or deletes. Record locks for nonmatching rows are released after MySQL has evaluated【ɪˈvæljueɪtɪd 评价;评估;估计;】 the WHERE condition. This greatly reduces the probability of deadlocks, but they can still happen.
• For UPDATE statements, if a row is already locked, InnoDB performs a “semi-consistent” read, returning the latest committed version to MySQL so that MySQL can determine whether the row matches the WHERE condition of the UPDATE. If the row matches (must be updated), MySQL reads the row again and this time InnoDB either locks it or waits for a lock on it.
Consider the following example, beginning with this table:

1. CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB;
2. INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2);
3. COMMIT;

复制代码

In this case, the table has no indexes, so searches and index scans use the hidden clustered index for record locking rather than indexed columns.
Suppose that one session performs an UPDATE using these statements:

1. # Session A
2. START TRANSACTION;
3. UPDATE t SET b = 5 WHERE b = 3;

复制代码

Suppose also that a second session performs an UPDATE by executing these statements following those of the first session:

1. # Session B
2. UPDATE t SET b = 4 WHERE b = 2;

复制代码

As InnoDB executes each UPDATE, it first acquires an exclusive lock for each row, and then determines whether to modify it. If InnoDB does not modify the row, it releases the lock. Otherwise, InnoDB retains【rɪˈteɪnz 保持;保留;持有;继续拥有;继续容纳;】 the lock until the end of the transaction. This affects transaction processing as follows.
When using the default REPEATABLE READ isolation level, the first UPDATE acquires an x-lock on each row that it reads and does not release any of them:

1. x-lock(1,2); retain x-lock
2. x-lock(2,3); update(2,3) to (2,5); retain x-lock
3. x-lock(3,2); retain x-lock
4. x-lock(4,3); update(4,3) to (4,5); retain x-lock
5. x-lock(5,2); retain x-lock

复制代码

The second UPDATE blocks as soon as it tries to acquire any locks (because first update has retained locks on all rows), and does not proceed until the first UPDATE commits or rolls back:

1. x-lock(1,2); block and wait for first UPDATE to commit or roll back

复制代码

If READ COMMITTED is used instead, the first UPDATE acquires an x-lock on each row that it reads and releases those for rows that it does not modify:

1. x-lock(1,2); unlock(1,2)
2. x-lock(2,3); update(2,3) to (2,5); retain x-lock
3. x-lock(3,2); unlock(3,2)
4. x-lock(4,3); update(4,3) to (4,5); retain x-lock
5. x-lock(5,2); unlock(5,2)

复制代码

For the second UPDATE, InnoDB does a “semi-consistent” read, returning the latest committed version of each row that it reads to MySQL so that MySQL can determine whether the row matches the WHERE condition of the UPDATE:

1. x-lock(1,2); update(1,2) to (1,4); retain x-lock
2. x-lock(2,3); unlock(2,3)
3. x-lock(3,2); update(3,2) to (3,4); retain x-lock
4. x-lock(4,3); unlock(4,3)
5. x-lock(5,2); update(5,2) to (5,4); retain x-lock

复制代码

However, if the WHERE condition includes an indexed column, and InnoDB uses the index, only the indexed column is considered when taking and retaining【rɪˈteɪnɪŋ 固定的;矫正的;】 record locks. In the following example, the first UPDATE takes and retains an x-lock on each row where b = 2. The second UPDATE blocks when it tries to acquire x-locks on the same records, as it also uses the index defined on column b.

1. CREATE TABLE t (a INT NOT NULL, b INT, c INT, INDEX (b)) ENGINE = InnoDB;
2. INSERT INTO t VALUES (1,2,3),(2,2,4);
3. COMMIT;
4. # Session A
5. START TRANSACTION;
6. UPDATE t SET b = 3 WHERE b = 2 AND c = 3;
7. # Session B
8. UPDATE t SET b = 4 WHERE b = 2 AND c = 4;

复制代码

The READ COMMITTED isolation level can be set at startup or changed at runtime. At runtime, it can be set globally for all sessions, or individually per session.
• READ UNCOMMITTED

SELECT statements are performed in a nonlocking fashion【ˈfæʃn 时尚;时装业;(行为、活动等的)时兴;(衣服、发式等的)流行款式;】, but a possible earlier version of a row might be used. Thus, using this isolation level, such reads are not consistent. This is also called a dirty read. Otherwise, this isolation level works like READ COMMITTED.
• SERIALIZABLE

This level is like REPEATABLE READ, but InnoDB implicitly【ɪmˈplɪsətli 含蓄地;无保留地;暗中地;不明显地;无疑问地;】 converts all plain SELECT statements to SELECT ... FOR SHARE if autocommit is disabled. If autocommit is enabled, the SELECT is its own transaction. It therefore is known to be read only and can be serialized if performed as a consistent (nonlocking) read and need not block for other transactions. (To force a plain SELECT to block if other transactions have modified the selected rows, disable autocommit.)
补充：
As of MySQL 8.0.22, DML operations that read data from MySQL grant tables (through a join list or subquery) but do not modify them do not acquire read locks on the MySQL grant tables, regardless of the isolation level.
2 autocommit, Commit, and Rollback

In InnoDB, all user activity occurs inside a transaction. If autocommit mode is enabled, each SQL statement forms a single transaction on its own. By default, MySQL starts the session for each new connection with autocommit enabled, so MySQL does a commit after each SQL statement if that statement did not return an error. If a statement returns an error, the commit or rollback behavior depends on the error.
A session that has autocommit enabled can perform a multiple-statement transaction by starting it with an explicit START TRANSACTION or BEGIN statement and ending it with a COMMIT or ROLLBACK statement.
If autocommit mode is disabled within a session with SET autocommit = 0, the session always has a transaction open. A COMMIT or ROLLBACK statement ends the current transaction and a new one starts.
If a session that has autocommit disabled ends without explicitly committing the final transaction, MySQL rolls back that transaction.
Some statements implicitly【ɪmˈplɪsətli 含蓄地;无保留地;暗中地;不明显地;无疑问地;】 end a transaction, as if you had done a COMMIT before executing the statement.
A COMMIT means that the changes made in the current transaction are made permanent【ˈpɜːrmənənt 永久的;永恒的;长久的;】 and become visible to other sessions. A ROLLBACK statement, on the other hand, cancels all modifications made by the current transaction. Both COMMIT and ROLLBACK release all InnoDB locks that were set during the current transaction.
Grouping DML Operations with Transactions

By default, connection to the MySQL server begins with autocommit mode enabled, which automatically commits every SQL statement as you execute it. This mode of operation might be unfamiliar if you have experience with other database systems, where it is standard practice to issue a sequence【ˈsiːkwəns 序列;顺序;次序;一系列;一连串;(电影中表现同一主题或场面的)一组镜头;】 of DML statements and commit them or roll them back all together.
 To use multiple-statement transactions, switch autocommit off with the SQL statement SET autocommit = 0 and end each transaction with COMMIT or ROLLBACK as appropriate【əˈproʊpriət , əˈproʊprieɪt 适当的;合适的;恰当的;】. To leave autocommit on, begin each transaction with START TRANSACTION and end it with COMMIT or ROLLBACK. The following example shows two transactions. The first is committed; the second is rolled back.

1. mysql> CREATE TABLE customer (a INT, b CHAR (20), INDEX (a));
2. Query OK, 0 rows affected (0.00 sec)
3. mysql> -- Do a transaction with autocommit turned on.
4. mysql> START TRANSACTION;
5. Query OK, 0 rows affected (0.00 sec)
6. mysql> INSERT INTO customer VALUES (10, 'Heikki');
7. Query OK, 1 row affected (0.00 sec)
8. mysql> COMMIT;
9. Query OK, 0 rows affected (0.00 sec)
10. mysql> -- Do another transaction with autocommit turned off.
11. mysql> SET autocommit=0;
12. Query OK, 0 rows affected (0.00 sec)
13. mysql> INSERT INTO customer VALUES (15, 'John');
14. Query OK, 1 row affected (0.00 sec)
15. mysql> INSERT INTO customer VALUES (20, 'Paul');
16. Query OK, 1 row affected (0.00 sec)
17. mysql> DELETE FROM customer WHERE b = 'Heikki';
18. Query OK, 1 row affected (0.00 sec)
19. mysql> -- Now we undo those last 2 inserts and the delete.
20. mysql> ROLLBACK;
21. Query OK, 0 rows affected (0.00 sec)
22. mysql> SELECT * FROM customer;
23. +------+--------+
24. | a    | b      |
25. +------+--------+
26. | 10   | Heikki |
27. +------+--------+
28. 1 row in set (0.00 sec)
29. mysql>

复制代码

 Transactions in Client-Side Languages

In APIs such as PHP, Perl DBI, JDBC, ODBC, or the standard C call interface of MySQL, you can send transaction control statements such as COMMIT to the MySQL server as strings just like any other SQL statements such as SELECT or INSERT. Some APIs also offer separate special transaction commit and rollback functions or methods.
3 Consistent Nonlocking Reads

A consistent read means that InnoDB uses multi-versioning to present【ˈpreznt , prɪˈzent 提出;(以某种方式)展现，显示，表现;表达，表示;提交;使发生;颁发;交付;授予;把…交给;上演;正式介绍;突然出现;主持播放;正式出席;】 to a query a snapshot of the database at a point in time. The query sees the changes made by transactions that committed before that point in time, and no changes made by later or uncommitted transactions. The exception to this rule is that the query sees the changes made by earlier statements within the same transaction. This exception causes the following anomaly【əˈnɑːməli 异常;反常现象;异常事物;】: If you update some rows in a table, a SELECT sees the latest version of the updated rows, but it might also see older versions of any rows. If other sessions simultaneously【ˌsaɪməlˈteɪniəsli 同时;联立;急切地;】 update the same table, the anomaly【əˈnɑːməli 异常;反常现象;异常事物;】 means that you might see the table in a state that never existed in the database.
If the transaction isolation level is REPEATABLE READ (the default level), all consistent reads within the same transaction read the snapshot established by the first such read in that transaction. You can get a fresher snapshot for your queries by committing the current transaction and after that issuing new queries.With READ COMMITTED isolation level, each consistent read within a transaction sets and reads its own fresh snapshot.
Consistent read is the default mode in which InnoDB processes SELECT statements in READ COMMITTED and REPEATABLE READ isolation levels. A consistent read does not set any locks on the tables it accesses, and therefore other sessions are free to modify those tables at the same time a consistent read is being performed on the table.
Suppose that you are running in the default REPEATABLE READ isolation level. When you issue a consistent read (that is, an ordinary SELECT statement), InnoDB gives your transaction a timepoint【taɪm pɔɪnt 时间点;时刻;时点;的时点;】 according to which your query sees the database. If another transaction deletes a row and commits after your timepoint was assigned, you do not see the row as having been deleted. Inserts and updates are treated similarly.
You can advance your timepoint by committing your transaction and then doing another SELECT or START TRANSACTION WITH CONSISTENT SNAPSHOT.
This is called multi-versioned concurrency control.
In the following example, session A sees the row inserted by B only when B has committed the insert and A has committed as well, so that the timepoint is advanced past the commit of B.

1.             Session A              Session B
2.           SET autocommit=0;        SET autocommit=0;
3. time
4. |         SELECT * FROM t;
5. |         empty set
6. |                                  INSERT INTO t VALUES (1, 2);
7. |
8. v         SELECT * FROM t;
9.           empty set
10.                                    COMMIT;
11.           SELECT * FROM t;
12.           empty set
13. <br>          COMMIT;
14.           SELECT * FROM t;
15.           ---------------------
16.           | 1 | 2 |
17.           ---------------------

复制代码

If you want to see the “freshest” state of the database, use either the READ COMMITTED isolation level or a locking read:

1. SELECT * FROM t FOR SHARE;

复制代码

With READ COMMITTED isolation level, each consistent read within a transaction sets and reads its own fresh snapshot. With FOR SHARE, a locking read occurs instead: A SELECT blocks until the transaction containing the freshest rows ends.
Consistent read does not work over certain DDL statements:
• Consistent read does not work over DROP TABLE, because MySQL cannot use a table that has been dropped and InnoDB destroys the table.
• Consistent read does not work over ALTER TABLE operations that make a temporary copy of the original table and delete the original table when the temporary copy is built. When you reissue a consistent read within a transaction, rows in the new table are not visible because those rows did not exist when the transaction's snapshot was taken. In this case, the transaction returns an error: ER_TABLE_DEF_CHANGED, “Table definition has changed, please retry transaction”.
The type of read varies for selects in clauses like INSERT INTO ... SELECT, UPDATE ... (SELECT), and CREATE TABLE ... SELECT that do not specify FOR UPDATE or FOR SHARE:
• By default, InnoDB uses stronger locks for those statements and the SELECT part acts like READ COMMITTED, where each consistent read, even within the same transaction, sets and reads its own fresh snapshot.
• To perform a nonlocking read in such cases, set the isolation level of the transaction to READ UNCOMMITTED or READ COMMITTED to avoid setting locks on rows read from the selected table.
内容补充
The snapshot of the database state applies to SELECT statements within a transaction, not necessarily to DML statements. If you insert or modify some rows and then commit that transaction, a DELETE or UPDATE statement issued from another concurrent REPEATABLE READ transaction could affect those just-committed rows, even though the session could not query them. If a transaction does update or delete rows committed by a different transaction, those changes do become visible to the current transaction. For example, you might encounter a situation like the following:

1. SELECT COUNT(c1) FROM t1 WHERE c1 = 'xyz';
2. -- Returns 0: no rows match.
3. DELETE FROM t1 WHERE c1 = 'xyz';
4. -- Deletes several rows recently committed by other transaction.
5. SELECT COUNT(c2) FROM t1 WHERE c2 = 'abc';
6. -- Returns 0: no rows match.
7. UPDATE t1 SET c2 = 'cba' WHERE c2 = 'abc';
8. -- Affects 10 rows: another txn just committed 10 rows with 'abc' values.
9. SELECT COUNT(c2) FROM t1 WHERE c2 = 'cba';
10. -- Returns 10: this txn can now see the rows it just updated.

复制代码

4 Locking Reads

If you query data and then insert or update related data within the same transaction, the regular SELECT statement does not give enough protection. Other transactions can update or delete the same rows you just queried. InnoDB supports two types of locking reads that offer extra safety:
• SELECT ... FOR SHARE
Sets a shared mode lock on any rows that are read. Other sessions can read the rows, but cannot modify them until your transaction commits. If any of these rows were changed by another transaction that has not yet committed, your query waits until that transaction ends and then uses the latest values.
Prior to MySQL 8.0.22, SELECT ... FOR SHARE requires the SELECT privilege and at least one of the DELETE, LOCK TABLES, or UPDATE privileges. From MySQL 8.0.22, only the SELECT privilege is required.
From MySQL 8.0.22, SELECT ... FOR SHARE statements do not acquire read locks on MySQL grant tables.
补充; SELECT ... FOR SHARE is a replacement for SELECT ... LOCK IN SHARE MODE, but LOCK IN SHARE MODE remains available for backward compatibility. The statements are equivalent. However, FOR SHARE supports OF table_name, NOWAIT, and SKIP LOCKED options.
• SELECT ... FOR UPDATE
For index records the search encounters, locks the rows and any associated index entries, the same as if you issued an UPDATE statement for those rows. Other transactions are blocked from updating those rows, from doing SELECT ... FOR SHARE, or from reading the data in certain transaction isolation levels. Consistent【kənˈsɪstənt】 reads ignore any locks set on the records that exist in the read view. (Old versions of a record cannot be locked; they are reconstructed by applying undo logs on an in-memory copy of the record.)
SELECT ... FOR UPDATE requires the SELECT privilege and at least one of the DELETE, LOCK TABLES, or UPDATE privileges.
These clauses are primarily useful when dealing with tree-structured or graph-structured data, either in a single table or split across multiple tables. You traverse【[trəˈvɜːrs , ˈtrævɜːrs 穿过;横越;横过;横渡;】 edges【ˈedʒɪz (尤指灾难的)边缘;边;边沿;刀刃;刀口;边线;利刃;】 or tree branches from one place to another, while reserving【rɪˈzɜːrvɪŋ 保留;拥有，保持，保留(某种权利);预订，预约(座位、席位、房间等);贮备;】 the right to come back and change any of these “pointer” values.
All locks set by FOR SHARE and FOR UPDATE queries are released when the transaction is committed or rolled back.
A locking read clause in an outer statement does not lock the rows of a table in a nested subquery unless a locking read clause is also specified in the subquery. For example, the following statement does not lock rows in table t2.

1. SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2) FOR UPDATE;

复制代码

To lock rows in table t2, add a locking read clause to the subquery:

1. SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2 FOR UPDATE) FOR UPDATE;

复制代码

补充;Locking reads are only possible when autocommit is disabled (either by beginning transaction with START TRANSACTION or by setting autocommit to 0.
Locking Read Examples

Suppose that you want to insert a new row into a table child, and make sure that the child row has a parent row in table parent. Your application code can ensure referential integrity throughout this sequence of operations.
First, use a consistent read to query the table PARENT and verify that the parent row exists. Can you safely insert the child row to table CHILD? No, because some other session could delete the parent row in the moment between your SELECT and your INSERT, without you being aware of it.
To avoid this potential issue, perform the SELECT using FOR SHARE:

1. SELECT * FROM parent WHERE NAME = 'Jones' FOR SHARE;

复制代码

After the FOR SHARE query returns the parent 'Jones', you can safely add the child record to the CHILD table and commit the transaction. Any transaction that tries to acquire an exclusive lock in the applicable row in the PARENT table waits until you are finished, that is, until the data in all tables is in a consistent state.
For another example, consider an integer counter field in a table CHILD_CODES, used to assign a unique identifier to each child added to table CHILD. Do not use either consistent read or a shared mode read to read the present value of the counter, because two users of the database could see the same value for the counter, and a duplicate-key error occurs if two transactions attempt to add rows with the same identifier to the CHILD table.
Here, FOR SHARE is not a good solution because if two users read the counter at the same time, at least one of them ends up in deadlock when it attempts to update the counter.
To implement reading and incrementing the counter, first perform a locking read of the counter using FOR UPDATE, and then increment the counter. For example:

1. SELECT counter_field FROM child_codes FOR UPDATE;
2. UPDATE child_codes SET counter_field = counter_field + 1;

复制代码

A SELECT ... FOR UPDATE reads the latest available data, setting exclusive locks on each row it reads. Thus, it sets the same locks a searched SQL UPDATE would set on the rows.
The preceding description is merely an example of how SELECT ... FOR UPDATE works. In MySQL, the specific task of generating a unique identifier actually can be accomplished using only a single access to the table:

1. UPDATE child_codes SET counter_field = LAST_INSERT_ID(counter_field + 1);
2. SELECT LAST_INSERT_ID();

复制代码

The SELECT statement merely retrieves the identifier information (specific to the current connection). It does not access any table.
Locking Read Concurrency with NOWAIT and SKIP LOCKED

If a row is locked by a transaction, a SELECT ... FOR UPDATE or SELECT ... FOR SHARE transaction that requests the same locked row must wait until the blocking transaction releases the row lock. This behavior prevents transactions from updating or deleting rows that are queried for updates by other transactions. However, waiting for a row lock to be released is not necessary if you want the query to return immediately when a requested row is locked, or if excluding locked rows from the result set is acceptable.
To avoid waiting for other transactions to release row locks, NOWAIT and SKIP LOCKED options may be used with SELECT ... FOR UPDATE or SELECT ... FOR SHARE locking read statements.
• NOWAIT
A locking read that uses NOWAIT never waits to acquire a row lock. The query executes immediately, failing with an error if a requested row is locked.
• SKIP LOCKED
A locking read that uses SKIP LOCKED never waits to acquire a row lock. The query executes immediately, removing locked rows from the result set.
注意：Queries that skip locked rows return an inconsistent view of the data. SKIP LOCKED is therefore not suitable for general transactional work. However, it may be used to avoid lock contention when multiple sessions access the same queuelike table.
NOWAIT and SKIP LOCKED only apply to row-level locks.
Statements that use NOWAIT or SKIP LOCKED are unsafe for statement based replication.
The following example demonstrates NOWAIT and SKIP LOCKED. Session 1 starts a transaction that takes a row lock on a single record. Session 2 attempts a locking read on the same record using the NOWAIT option. Because the requested row is locked by Session 1, the locking read returns immediately with an error. In Session 3, the locking read with SKIP LOCKED returns the requested rows except for the row that is locked by Session 1.

1. # Session 1:
2. mysql> CREATE TABLE t (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;
3. mysql> INSERT INTO t (i) VALUES(1),(2),(3);
4. mysql> START TRANSACTION;
5. mysql> SELECT * FROM t WHERE i = 2 FOR UPDATE;
6. +---+
7. | i |
8. +---+
9. | 2 |
10. +---+
11. # Session 2:
12. mysql> START TRANSACTION;
13. mysql> SELECT * FROM t WHERE i = 2 FOR UPDATE NOWAIT;
14. ERROR 3572 (HY000): Do not wait for lock.
15. # Session 3:
16. mysql> START TRANSACTION;
17. mysql> SELECT * FROM t FOR UPDATE SKIP LOCKED;
18. +---+
19. | i |
20. +---+
21. | 1 |
22. | 3 |
23. +---+

复制代码

 

来源:https://www.cnblogs.com/xuliuzai/p/18147130
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！