|
|
看的什么书啊？
--This is the MySQL Reference Manual. It documents MySQL 8.0 through 8.0.34, as well as NDB Cluster releases based on version 8.0 of NDB through 8.0.34-ndb-8.0.34, respectively. It may include documentation of features of MySQL versions that have not yet been released. （看的这个版本的官方文档）
看书为解惑，解的什么惑？----locking read 与lock
什么是locking read？
提出背景（或者说为了解决啥问题）--- 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.
实现的方式---A SELECT statement that also performs a locking operation on an InnoDB table. InnoDB supports two types of locking reads that offer extra safety. Either SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE.
潜在的风险--It has the potential to produce a deadlock, depending on the isolation level of the transaction.
要点补充--（1 运行的环境要求） Locking reads are only possible when autocommit is disabled (either by beginning transaction with START TRANSACTION or by setting autocommit to 0. （2 解除锁，必须有相应的操作行为）All locks set by FOR SHARE and FOR UPDATE queries are released when the transaction is committed or rolled back.（3 版本有升级，SQL有变化）SELECT ... FOR SHARE replaces SELECT ... LOCK IN SHARE MODE in MySQL 8.0.1, but LOCK IN SHARE MODE remains available for backward compatibility.
locking read 与 锁的关系（什么情况下加什么锁，实现怎样的locking read）
A locking read, an UPDATE, or a DELETE generally set record locks on every index record that is scanned in the processing of an SQL statement. It does not matter whether there are WHERE conditions in the statement that would exclude the row. InnoDB does not remember the exact WHERE condition, but only knows which index ranges were scanned. The locks are normally【通常】 next-key locks that also block inserts into the “gap” immediately before the record. However, gap locking can be disabled explicitly, which causes next-key locking not to be used. The transaction isolation level can also affect which locks are set.
If a secondary index is used in a search and the index record locks to be set are exclusive【排斥的】, InnoDB also retrieves【取回、检索数据】 the corresponding【相应的、相关的】 clustered index records and sets locks on them.
If you have no indexes suitable for your statement and MySQL must scan the entire【整个的、全部的】 table to process the statement, every row of the table becomes locked, which in turn blocks all inserts by other users to the table. It is important to create good indexes so that your queries do not scan more rows than necessary.
InnoDB sets specific【特有的，明确的，独特的】 types of locks as follows. -----根据不同的情况设置不同的锁
情形1
SELECT ... FROM is a consistent read, reading a snapshot of the database and setting no locks unless the transaction isolation level is set to SERIALIZABLE. For SERIALIZABLE level, the search sets shared next-key locks on the index records it encounters. However, only an index record lock is required for statements that lock rows using a unique index to search for a unique row. ---一般情况下，是不加锁的，例外，就是 在 SERIALIZABLE 隔离级别下会加next-key locks，还有就是在依据 唯一键查询时，也会添加行锁。
情形2
SELECT ... FOR UPDATE and SELECT ... FOR SHARE statements that use a unique index acquire locks for scanned rows, and release the locks for rows that do not qualify【合格、达标】 for inclusion【包括、包含】 in the result set (for example, if they do not meet the criteria【标准;(评判或作决定的)准则;原则;】 given in the WHERE clause). However, in some cases, rows might not be unlocked immediately because the relationship between a result row and its original source is lost during query execution. For example, in a UNION, scanned (and locked) rows from a table might be inserted into a temporary table before evaluating whether they qualify for the result set. In this circumstance, the relationship of the rows in the temporary table to the rows in the original table is lost and the latter rows are not unlocked until the end of query execution.【不太好理解。关联查询？过渡临时表出现异常了？】
情形3
For locking reads (SELECT with FOR UPDATE or FOR SHARE), UPDATE, and DELETE statements, the locks that are taken depend 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, and for non-unique indexes, 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.
---锁还和唯一index有关
情形4
For index records the search encounters, SELECT ... FOR UPDATE blocks other sessions from doing SELECT ... FOR SHARE or from reading in certain transaction isolation levels. Consistent reads ignore any locks set on the records that exist in the read view.
情形5
UPDATE ... WHERE ... sets an exclusive next-key lock on every record the search encounters. However, only an index record lock is required for statements that lock rows using a unique index to search for a unique row.
情形6
When UPDATE modifies a clustered index record, implicit【含蓄的、内含的、不直接言名】 locks are taken on affected secondary index records. 【自然，辅助索引上也会有相应的锁。】The UPDATE operation also takes shared locks on affected secondary index records when performing duplicate check scans prior to inserting new secondary index records, and when inserting new secondary index records.
情形7
DELETE FROM ... WHERE ... sets an exclusive next-key lock on every record the search encounters. However, only an index record lock is required for statements that lock rows using a unique index to search for a unique row.
情形8
INSERT sets an exclusive lock【排他锁】 on the inserted row. This lock is an index-record lock, not a next-key lock (that is, there is no gap lock) and does not prevent other sessions from inserting into the gap before the inserted row.
Prior to inserting the row【在插入记录前】, a type of gap lock called an insert intention gap lock is set. This lock signals【表示、表明】 the intent to insert in such a way that multiple【数量多的】 transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6 each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.【就是为了声明, 不真正冲突的，还是可以插入的？】
If a duplicate-key error occurs, a shared lock on the duplicate index record is set. This use of a shared lock can result in deadlock should there be multiple sessions trying to insert the same row if another session already has an exclusive lock. This can occur if another session deletes the row.
情形9
INSERT ... ON DUPLICATE KEY UPDATE differs from a simple INSERT in that an exclusive lock rather than a shared lock is placed on the row to be updated when a duplicate-key error occurs. An exclusive index-record lock is taken for a duplicate primary key value. An exclusive next-key lock is taken for a duplicate unique key value.
情形10
EPLACE is done like an INSERT if there is no collision on a unique key. Otherwise, an exclusive nextkey lock is placed on the row to be replaced.
情形11
INSERT INTO T SELECT ... FROM S WHERE ... sets an exclusive index record lock (without a gap lock) on each row inserted into T. If the transaction isolation level is READ COMMITTED, InnoDB does the search on S as a consistent read (no locks). Otherwise, InnoDB sets shared next-key locks on rows from S. InnoDB has to set locks in the latter case: During roll-forward recovery using a statement-based binary log, every SQL statement must be executed in exactly the same way it was done originally.
CREATE TABLE ... SELECT ... performs the SELECT with shared next-key locks or as a consistent read, as for INSERT ... SELECT.
When a SELECT is used in the constructs REPLACE INTO t SELECT ... FROM s WHERE ... or UPDATE t ... WHERE col IN (SELECT ... FROM s ...), InnoDB sets shared next-key locks on rows from table s.
情形12
InnoDB sets an exclusive lock on the end of the index associated with the AUTO_INCREMENT column while initializing a previously specified AUTO_INCREMENT column on a table.
With innodb_autoinc_lock_mode=0, InnoDB uses a special AUTO-INC table lock mode where the lock is obtained and held to the end of the current SQL statement (not to the end of the entire transaction) while accessing the auto-increment counter. Other clients cannot insert into the table while the AUTO-INC table lock is held. The same behavior occurs for “bulk inserts” with innodb_autoinc_lock_mode=1. Table-level AUTO-INC locks are not used with innodb_autoinc_lock_mode=2.
InnoDB fetches the value of a previously initialized AUTO_INCREMENT column without setting any locks.
情形13
If a FOREIGN KEY constraint is defined on a table, any insert, update, or delete that requires the constraint condition to be checked sets shared record-level locks on the records that it looks at to check the constraint. InnoDB also sets these locks in the case where the constraint fails.
情形14
LOCK TABLES sets table locks, but it is the higher MySQL layer above the InnoDB layer that sets these locks. InnoDB is aware of table locks if innodb_table_locks = 1 (the default) and autocommit = 0, and the MySQL layer above InnoDB knows about row-level locks.
Otherwise, InnoDB's automatic deadlock detection cannot detect deadlocks where such table locks are involved. Also, because in this case the higher MySQL layer does not know about row-level locks, it is possible to get a table lock on a table where another session currently has row-level locks. However, this does not endanger transaction integrity
情形15
LOCK TABLES acquires two locks on each table if innodb_table_locks=1 (the default). In addition to【除了；另外；除...之外（还）】 a table lock on the MySQL layer, it also acquires an InnoDB table lock. To avoid acquiring InnoDB table locks, set innodb_table_locks=0. If no InnoDB table lock is acquired, LOCK TABLES completes even if some records of the tables are being locked by other transactions.
In MySQL 8.0, innodb_table_locks=0 has no effect for tables locked explicitly with LOCK TABLES ... WRITE. It does have an effect for tables locked for read or write by LOCK TABLES ... WRITE implicitly (for example, through triggers) or by LOCK TABLES ... READ.
情形16
All InnoDB locks held by a transaction are released when the transaction is committed or aborted. Thus, it does not make much sense to invoke LOCK TABLES on InnoDB tables in autocommit=1 mode because the acquired InnoDB table locks would be released immediately.【事物自动提交的模式下，无需过分关注 LOCK TABLES的调用。】
情形17
You cannot lock additional【附加的；额外的】 tables in the middle of a transaction because LOCK TABLES performs an implicit COMMIT and UNLOCK TABLES.
------《15.7.2 InnoDB Transaction Model》《15.7.3 Locks Set by Different SQL Statements in InnoDB》
来源:https://www.cnblogs.com/xuliuzai/p/18090694
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！ |
|
