MySQL Review the past and learn the new--Locks in Innodb storage engine

I encountered a lot of lock problems recently, so after solving them, I carefully read the books about locks, which are summarized as follows:

1 , type of lock

InnodbStorage engine implementation The following 2 standard row-level locks are provided:

? Shared lock(S lock) , allows the transaction to read one row of data.

? Exclusive lock(X lock), allows a transaction to delete or update a row of data.

When a transaction acquires the shared lock on row r, then another The transaction can also immediately acquire the shared lock on row r, because the read does not change the data on row r, which is the case Lock compatible. But if a transaction wants to obtain an exclusive lock on row r, it must wait for the transaction to release the shared lock on row r ##—This situation is that the locks are incompatible. The compatibility between the two is shown in the following table:

# #conflictconflict##S ​​

Compatibility of exclusive locks and shared locks

X Exclusive lock

##S ​​Shared lock

##X

Exclusive lock

Shared Lock

##Conflict

##Compatibility

2##, lock extension

InnodbThe storage engine supports multi-granularity locking, which allows row-level locks and table-level locks to exist at the same time. In order to support locking operations at different granularities, the InnoDB storage engine supports an additional locking method, which is intention locking. Intention locks are table-level locks designed primarily to reveal the type of lock that will be requested for the next row within a transaction. It is also divided into two types:

? Intention shared lock (IS Lock), the transaction wants to obtain certain rows in a table shared lock.

? Intention exclusive lock (IX Lock), the transaction wants to obtain exclusive locks on certain rows in a table.

Since InnoDB supports row-level locks, intention locks do not The assembly blocks any request except for the full table scan. Shared locks, exclusive locks, intention shared locks, and intention exclusive locks all have mutually compatible /mutually exclusive relationships, which can be represented by a compatibility matrix(y means compatible, n means incompatible ), as shown below:

	X Exclusive lock	##S ​​Shared lock	IX Intention exclusive lock	IS Intention shared lock
X Exclusive lock	conflict	conflict	conflict	Conflict
##S ​​ Shared Lock	conflict	Compatible	Conflict	Compatible
IX Intention exclusive lock	conflict	conflict	##compatibility	Compatible
##IS Intention shared lock	Conflict	Compatibility	compatible	compatible

          Analysis: The mutual compatibility relationship between X and S As described in step1, the relationships between IX and IS are all compatible, which is also easy to understand. , because they are only "intentional" and are still in the YY stage, and have not really done anything, so they are compatible;

The rest The ones are X and IX, X and IS, S and IX, S and IS Okay, we can deduce these four sets of relationships from the relationship between X and S.

Simply put: X and IX’s =X Relationship with X. why? Because after the transaction acquires the IX lock, it has the right to acquire the X lock. If X is compatible with IX, both transactions will obtain XLock situation, this is contradictory to what we know X and X are mutually exclusive, so X and IX can only have a mutually exclusive relationship. The remaining three sets of relationships are similar and can be derived in the same way.

3, simulated lock scene

Before InnoDB Plugin, we could only pass SHOW FULL PROCESSLIS and SHOW ENGINE INNODB STATUS to view the current database request, and then determine the lock situation in the transaction. In the new version of InnoDB Plugin, 3 is added to the information_schema library. Table, INNODB_LOCKS, INNODB_TRX, INNODB_LOCK_WAITS. Through this 3 table, you can more easily monitor current transactions and analyze possible lock problems. If the database is running normally, these 3 tables will be empty without any records.

3.1, open transaction t1, t2, Simulate lock

Open 2session windows and open 2 transactions t1 and t2.

Open the transaction in the first windowt1Perform a lock operation, is as follows t1Transaction window interface:

mysql> set autocommit =0;
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
  开始执行锁定操作
mysql> select * from test.t1 where a<5 for update;
+---+----+----+
| a | b  | c  |
+---+----+----+
| 1 | c2 | c2 |
| 2 | a  |    |
| 3 | r5 | r3 |
| 4 | r4 | r5 |
+---+----+----+
4 rows in set (0.00 sec)

# #

mysql>

这个时候，事务t1已经锁定了表t1的所有a60eb77c3130c79e74878e2ea79b95e43 update t1 set a=111 where a=1;

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

##mysql>

At this time, sessionB## The transaction in #2 is terminatedupdate t1 set a=111 where a=1; dmlRequest operation.

6, self-growth and lock

自增长在数据库中是非常常见的一种属性，在Innodb的存储引擎的内存结构中，对每个含有自增长值的表都有一个自增长计数器（auto-increment counter）。当对有自增长字段的表进行insert时候，这个计数器会被初始化，执行如下的sql语句来得到计数器的值。

SELECT MAX(auto_inc_col) FROM tablename FOR UPDATE;

插入操作会依据这个自增长的计数器值+1赋予自增长列，这个实现方式称为AUTO-INC Locking，这种锁其实是一种特殊的表锁机制，为了提高插入的性能，锁不是在一个事务完成后才释放，而是在完成对自增长值插入的sql语句后立即释放。

 

从mysql 5.1.22版本开始，提供了一些轻量级互斥的自增长实现机制，这种机制大大提高自增长值插入的性能。还提供了一个参数innodb_autoinc_lock_mode，默认值为1.

 

自增长的分类：

在mysql的innodb表中，自增长列必须是索引，而且必须为索引的第一列，如果是第二个列会报错如下所示：

mysql> CREATE TABLE t(a INT AUTO_INCREMENT ,b VARCHAR(10),KEY (b,a));
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
mysql>
mysql> CREATE TABLE t(a INT AUTO_INCREMENT ,b VARCHAR(10),PRIMARY KEY (a),KEY (b,a));
Query OK, 0 rows affected (0.01 sec)
 
mysql>

而在myisam表中，则没有这样的限制，如下所示：

mysql>  CREATE TABLE t_myisam(a INT AUTO_INCREMENT ,b VARCHAR(10),KEY (b,a))engine=myisam;
Query OK, 0 rows affected (0.01 sec)

 

mysql>

 

7，MySQL外键和锁

在innodb存储引擎中，对于一个外键列，如果没有显式的针对这个列添加索引Innodb存储引擎会自动的对其添加一个索引，这样可以避免表锁，这点比oracle做的较好一些，oracle需要自己手动添加外键锁。

 以上就是MySQL 温故而知新--Innodb存储引擎中的锁的内容，更多相关内容请关注PHP中文网（www.php.cn）！