MySQL--参数innodb_locks_unsafe_for_binlog和间隙锁机制

innodb_locks_unsafe_for_binlog

 

Property	Value
Command-Line Format	--innodb-locks-unsafe-for-binlog
Deprecated	5.6.3
System Variable	innodb_locks_unsafe_for_binlog
Scope	Global
Dynamic	No
Type	boolean
Default Value	OFF

 

This variable affects how InnoDB uses  gap locking for searches and index scans. innodb_locks_unsafe_for_binlog is deprecated and will be removed in a future MySQL release.

这个参数影响innodb如何使用间隙锁进行索引扫描和搜索，这个参数在未来的版本将被移除

 

Normally, InnoDB uses an algorithm called next-key locking that combines index-row locking with  gap locking. InnoDB performs row-level locking in such a way that when it searches or scans a table index, it sets shared or exclusive locks on the index records it encounters. Thus,  row-level locks are actually index-record locks. In addition, a next-key lock on an index record also affects the gap before the index record. That is, a next-key lock is an index-record lock plus a gap lock on the gap preceding the index record. If one session has a shared or exclusive lock on record R in an index, another session cannot insert a new index record in the gap immediately before R in the index order. See  Section 14.5.1, “InnoDB Locking”.

一般来说，innodb使用一种名为下一个键锁定的算法，这个算法将索引行级锁和间隙锁相结合。innodb在执行行级锁定时，会通过在遇到的索引记录上加共享锁或者独占锁这种方式进行搜索和扫描索引，因此，行级锁实际上就是索引记录锁。此外，索引记录上的下一键锁也会影响索引记录之间的间隙。也就是说，下一键锁是一个索引记录锁加上在索引记录之前的间隙上的间隙锁。

如果一个会话在索引记录R上已经有一个共享锁或者独占锁，另一个会话就不能立即在R的索引顺序之前的间隙中插入新的索引记录。

实验

 

session 1：

(root@localhost) [partition_test]> show variables like 'tx_isolation';

+---------------+-----------------+

| Variable_name | Value           |

+---------------+-----------------+

| tx_isolation  |  REPEATABLE-READ |

+---------------+-----------------+

1 row in set (0.00 sec)

 

(root@localhost) [partition_test]> select * from a;

+----+------+------+

| id | a    | b    |

+----+------+------+

|  1 |    2 |    3 |

|  2 |    4 |    5 |

|  3 |    2 |    3 |

|  4 |    2 |    3 |

|  5 |    5 |    5 |

|  6 |    0 |    3 |

+----+------+------+

6 rows in set (0.00 sec)

更新操作：加独占锁

(root@localhost) [partition_test]> begin ;

Query OK, 0 rows affected (0.00 sec)

 

(root@localhost) [partition_test]> update a set a = 2 and b = 4  where id > 4;

Query OK, 1 row affected (0.00 sec)

Rows matched: 2  Changed: 1  Warnings: 0

session 2：

(root@localhost) [partition_test]> insert into a select 7,8,9;

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

 

By default, the value of innodb_locks_unsafe_for_binlog is 0 (disabled), which means that gap locking is enabled: InnoDB uses next-key locks for searches and index scans. To enable the variable, set it to 1. This causes gap locking to be disabled: InnoDB uses only index-record locks for searches and index scans.

默认情况下，innodb_locks_unsafe_for_binlog 是0（不启用），意味着默认启用间隙锁：innodb当innodb进行搜索或者索引扫描的时候，使用下一键锁。启用的话将参数设置为1，gap lock将被禁用： innodb只使用index-record lock 来进行搜索和索引扫描

Enabling innodb_locks_unsafe_for_binlog does not disable the use of gap locking for foreign-key constraint checking or duplicate-key checking.

启用该参数并不会禁止使用间隙锁进行外键约束检查或重复键检查

The effects of enabling innodb_locks_unsafe_for_binlog are the same as setting the transaction isolation level to  READ COMMITTED, with these exceptions:

启用该参数的效果和设置事务隔离级别为  READ COMMITTED相同，但有两点意外：

• Enabling  innodb_locks_unsafe_for_binlog is a global setting and affects all sessions, whereas the isolation level can be set globally for all sessions, or individually per session.
• 启用该参数会影响所有的会话，而隔离级别可以为每个会话单独设置，也可以进行全局设置​

• innodb_locks_unsafe_for_binlog can be set only at server startup, whereas the isolation level can be set at startup or changed at runtime.
• 该参数只能在数据库启动时进行设置，隔离级别可以在启动后进行动态设置​

READ COMMITTED therefore offers finer and more flexible control than  innodb_locks_unsafe_for_binlog. For more information about the effect of isolation level on gap locking, see  Section 14.5.2.1, “Transaction Isolation Levels”.

同该参数相比， READ COMMITTED提供更精细和更灵活的控制

Enabling innodb_locks_unsafe_for_binlog may cause phantom problems because other sessions can insert new rows into the gaps when gap locking is disabled. Suppose that there is an index on the id column of the child table and that you want to read and lock all rows from the table having an identifier value larger than 100, with the intention of updating some column in the selected rows later:

启用该参数可能会导致幻读，因为间隙锁被禁用，其他会话依然可以向间隙中插入数据。假设表child的id列有一个索引，读取并锁定id大于的100 的所有行：

SELECT * FROM child WHERE id > 100 FOR UPDATE;

The query scans the index starting from the first record where the id is greater than 100. If the locks set on the index records in that range do not lock out inserts made in the gaps, another session can insert a new row into the table. Consequently, if you were to execute the same  SELECT again within the same transaction, you would see a new row in the result set returned by the query. This also means that if new items are added to the database, InnoDB does not guarantee serializability. Therefore, if innodb_locks_unsafe_for_binlog is enabled, InnoDB guarantees at most an isolation level of  READ COMMITTED. (Conflict serializability is still guaranteed.) For more information about phantoms, see  Section 14.5.4, “Phantom Rows”.

该参数如果启用，innodb最多保证隔离级别为read-committed

Enabling innodb_locks_unsafe_for_binlog 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 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 InnoDBeither locks it or waits for a lock on it.

Consider the following example, beginning with this table:

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

In this case, table has no indexes, so searches and index scans use the hidden clustered index for record locking (see  Section 14.8.2.1, “Clustered and Secondary Indexes”).

Suppose that one client performs an  UPDATE using these statements:

SET autocommit = 0;UPDATE t SET b = 5 WHERE b = 3;

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

SET autocommit = 0;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 and innodb_locks_unsafe_for_binlog is enabled, it releases the lock. Otherwise, InnoDB retains the lock until the end of the transaction. This affects transaction processing as follows.

If innodb_locks_unsafe_for_binlog is disabled, the first  UPDATE acquires x-locks and does not release any of them:

x-lock(1,2); retain x-lock

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

x-lock(3,2); retain x-lock

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

x-lock(5,2); retain x-lock

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

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

If innodb_locks_unsafe_for_binlog is enabled, the first  UPDATE acquires x-locks and releases those for rows that it does not modify:

x-lock(1,2); unlock(1,2)

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

x-lock(3,2); unlock(3,2)

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

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 to MySQL so that MySQL can determine whether the row matches the WHERE condition of the  UPDATE:

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

x-lock(2,3); unlock(2,3)

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

x-lock(4,3); unlock(4,3)

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

 间隙锁

Gap Locks

A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record. For example, SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; prevents other transactions from inserting a value of 15 into column t.c1, whether or not there was already any such value in the column, because the gaps between all existing values in the range are locked.

A gap might span a single index value, multiple index values, or even be empty.

Gap locks are part of the tradeoff between performance and concurrency, and are used in some transaction isolation levels and not others.

Gap locking is not needed for statements that lock rows using a unique index to search for a unique row. (This does not include the case that the search condition includes only some columns of a multiple-column unique index; in that case, gap locking does occur.) For example, if the id column has a unique index, the following statement uses only an index-record lock for the row having id value 100 and it does not matter whether other sessions insert rows in the preceding gap:

SELECT * FROM child WHERE id = 100;

If id is not indexed or has a nonunique index, the statement does lock the preceding gap.

It is also worth noting here that conflicting locks can be held on a gap by different transactions. For example, transaction A can hold a shared gap lock (gap S-lock) on a gap while transaction B holds an exclusive gap lock (gap X-lock) on the same gap. The reason conflicting gap locks are allowed is that if a record is purged from an index, the gap locks held on the record by different transactions must be merged.

Gap locks in InnoDB are “purely inhibitive”, which means that their only purpose is to prevent other transactions from inserting to the gap. Gap locks can co-exist. A gap lock taken by one transaction does not prevent another transaction from taking a gap lock on the same gap. There is no difference between shared and exclusive gap locks. They do not conflict with each other, and they perform the same function.

Gap locking can be disabled explicitly. This occurs if you change the transaction isolation level to READ COMMITTED or enable the innodb_locks_unsafe_for_binlogsystem variable (which is now deprecated). Under these circumstances, gap locking is disabled for searches and index scans and is used only for foreign-key constraint checking and duplicate-key checking.

There are also other effects of using the READ COMMITTED isolation level or enabling innodb_locks_unsafe_for_binlog. Record locks for nonmatching rows are released after MySQL has evaluated the WHERE condition. For UPDATE statements, InnoDB does a “semi-consistent” read, such that it returns the latest committed version to MySQL so that MySQL can determine whether the row matches the WHERE condition of the UPDATE.

 

转载于:https://www.cnblogs.com/ykyk1229/p/9317546.html