MySQLInnoDB存储引擎之锁_MySQL
程序员文章站
2022-04-23 14:42:45
...
概念:
锁是用来管理对共享文件的并发访问。innodb会在行级别上对数据库上锁。不过innodb存储引擎会在数据库内部其他多个地方使用锁，从而允许对不同资源提供并发访问。例如操作缓冲池中的LRU列表，删除，添加，移动LRU列表中的元素，为了保证一致性，必须有锁的介入。MyISAM引擎是表锁，而InnoDB提供一致性的非锁定读、行级锁，且行级锁没有相关额外的开销。
锁
table-level locking（表级锁）
整个表被客户锁定。根据锁定的类型，其他客户不能向表中插入记录，甚至从中读数据也受到限制MyISAM、MEMORY默认锁级别，个别时候，InnoDB也会升级为表级锁
row-level locking（行级锁）
只有线程当前使用的行被锁定，其他行对于其他线程都是可用的InnoDB默认行级锁。是基于索引数据结构来实现的，而不是像ORACLE的锁，是基于block的。InnoDB也会升级为表级锁，全表/全索引更新，请求autoinc锁等
page-level locking（页级锁）
锁定表中某些行集合（称做页），被锁定的行只对锁定最初的线程是可行。如果另外一个线程想要向这些行写数据，它必须等到锁被释放。不过其他页的行仍然可以使用BDB默认页级锁
lock与latch
latch称为闩锁(轻量级的锁)，因为其要求锁定的时间必须非常短。若持续的时间长，则应用的性能会非常差。在InnoDB存储引擎中，又可以分为mutex(互斥量)和rwlock(读写锁)。其目的是用来保证并发线程操作临界资源的正确性，并且通常没有死锁检测的机制。latch可以通过命令show engine innodb mutex来进行查看。如图：
由上图可以看出列Type显示的总是InnoDB，列Name显示latch的信息以及所在源码的行数，列Status中显示的os_waits表示操作系统等待的次数。
lock的对象是事务，用来锁定的是数据库中的对象，如表、页、行。并且一般lock的对象仅在事务commit或者rollback后释放(不同事务隔离级别释放的时间可能不一样)。有死锁机制。二则的区别如下:
特点：
InnoDB是通过对索引上的索引项加锁来实现行锁。这种特点也就意味着，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。
锁的类型:
有两种标准的行级锁:
共享锁(S lock):允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁.SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
排它锁(X lock):允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他锁.SELECT * FROM table_name WHERE ... FOR UPDATE
InnoDB存储引擎支持意向锁且设计比较简练，分为两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。(意向锁是InnoDB自动加的)
意向共享锁(IS):事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁.
意向独占锁(IX):事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁.
表级意向锁与行级锁的兼容情况如下图：
锁的查看
在InnoDB1.0版本之前只能通过show engine innodb status(transactions行中查看) 或者 show full processlist来查看当前库中锁的请求。但是在这之后在information_schema架构下新增innodb_trx、innodb_locks和innodb_lock_waits三张表记录当前库中锁的情况。
三个表的字段说明如下图
vcq9wLS2wcihtbHHsNa00NDKsbzkyv2+3b/i1tDQ0LXEyv2+3aGjyOe5+7bByKG1xNDQ1f3U2ta00NBkZWxldGW78tXfdXBkYXRlstnX96Os1eLKsbbByKGy2df3sru74dLytMvIpbXItP3Q0MnPy/i1xMrNt8Who8/gt7S12KOsSW5ub0RCtOa0otL9x+a74ciltsHIodDQtcTSu7j2v+zV1cr9vt0otbHHsNDQyv2+3bXEwPrKt7Dmsb4poaO/7NXVyv2+3crH1ri4w9DQtcTWrsewsOaxvrXEyv2+3aOsuMPKtc/WysfNqLn9dW5kb7bOwLTN6rPJoaO2+HVuZG/Tw8C01NrKws7xu9i59sr9vt2jrNLytMu/7NXVyv2+3bG+ye3Kx8O709C27s3iv6rP+qGjtvjH0qOstsHIob/s1dXK/b7dyseyu9Do0qrJz8v4tcSho9K71sLQ1LfHy/i2qLbBysdJbm5vREK05rSi0v3H5rXExKzIz7bByKG3vcq9KNTatsHIobK7u+HVvNPDus21yLT9se3Jz7XEy/gpoaO1q8rH1Nqyu82sysLO8bj0wOu8trHwz8KjrLbByKG1xLe9yr2yu82so6yyorK7ysfU2sO/uPbKws7xuPTA67y2sfDPwra8yseyydPDt8fL+LaotcTSu9bC0NS2waGjvLTKuba8ysfKudPDt8fL+LaotcTSu9bC0NS2waOstavKx7bU09q/7NXVyv2+3bXEtqjS5SYjMjY2ODQ7yr3Ssrj3srvP4M2soaPU2srCzvG49MDrvLax8FJFQUQKIENPTU1JVFRFRChSQym6zVJFUEVBVEFCTEUgUkVBRChSUixJbm5vREK05rSi0v3H5rXExKzIz8rCzvG49MDrvLax8CnPwqOsSW5ub0RCtOa0otL9x+bKudPDt8fL+LaotcTSu9bC0NS2waGjyLu2+KOsttTT2r/s1dXK/b7dtcS2qNLlyKWyu8/gzayho9TaUkPKws7xuPTA67y2sfDPwqOsttTT2r/s1dXK/b7do6y3x9K71sLQ1LbB19zKx7bByKGxu8v4tqjQ0LXE1+7QwtK7t92/7NXVyv2+3aGjtvjU2lJSysLO8bj0wOu94rDzz8KjrLbU09q/7NXVyv2+3aOst8fSu9bC0NS2wdfcyse2wcihysLO8b+qyrzKsbXE0NDK/b7dsOaxvqGjPGJyPgogICAg0rvWwtDUy/i2qLbBPGJyPgogICAgICAgINPQyc/OxNaqtcCjrMSsyM+1xMrCzvG49MDrvLax8ChSUinEo8q9z8KjrElubm9EQrTmtKLS/cfmtcRzZWxlY3Sy2df3yrnTw9K71sLQ1LfHy/i2qLbBoaO1q8rH1NrEs9Cpx+m/9s/Co6zTw7un0OjSqs/Uyr212LbUyv2+3b/itsGy2df3vNPL+NLUsaPWpMr9vt3C37yttcTSu9bC0NSho0lubm9EQrTmtKLS/cfmttTT2nNlbGVjdNPvvuTWp7PWwb3W1tK71sLQ1LXEy/i2qLbBstnX9zo8YnI+CiAgICAgICAgICAgIHNlbGVjdCAuLi4gZm9yIHVwZGF0ZaO6ttS2wcihtcTQ0LzHwry801jL+KOsxuTL+8rCzu+yu8TcttS4w9DQvNPIzrrOy/ihozxicj4KICAgICAgICAgICAgc2VsZWN0IC4uLiBsb2NrIGluIHNoYXJlIG1vZGWjurbUtsHIobXE0NC8x8K8vNNTy/ijrMbky/vKws7vv8nS1LbUuMPQ0LzTU8v4o6y1q8rHyOe5+7zTWMv4o6zU8rvhsbvX6Mj7oaM8YnI+CiAgICDX1NT2s6TT68v4PGJyPgogICAgICAgINTaSW5ub0RCtOa0otL9x+a1xMTatOa94bm51tCjrLbUw7+49rqs09DX1NT2s6QmIzIwNTQwO7XEse22vNPQ0ru49tfU1PazpLzGyv3G9yhhdXRvLWluY3JlbWVudCBjb3VudGVyKaGjtbG21Lqs09DX1NT2s6S1xLzGyv3G97XEse29+NDQsuXI67LZ1/fKx6Os1eK49rzGyv3G97vhsbuz9cq8u6+jrNa00NDI58/CtcTT777kwLS1w7W9vMbK/cb3tcQmIzIwNTQwOzpzZWxlY3QgbWF4KGF1dG9faW5jX2NvbCkgZnJvbSB0IGZvciB1cGRhdGWho7LlyOuy2df3u+HSwL7d1eK49tfU1PazpLXEvMbK/cb3JiMyMDU0MDu80zG4s9Po19TU9rOkwdCho9XiuPbKtc/Wt73KvbPGzqpBVVRPLUlOQyBMb2NraW5no6zV4srH0rvW1szYyuK1xLHty/i7+tbGo6zOqsHLzOG437LlyOu1xNDUxNyjrMv4srvKx9Ta0ru49srCzvHN6rPJ1q6687LFys23xaOstvjKx9TazeqzybbU19TU9rOkJiMyMDU0MDuy5cjrtcRTUUzT777kuvO74cGivLTKzbfFoaNBVVRPLUlOQwogTG9ja2luZ9Ta0ru2qLPMtsjJz8zhuN/By7Kit6Ky5cjrtcTQp8LKo6y1q8rHu7m05tTa0rvQqdDUxNzJz7XEzsrM4qGjytfPyKOsttTT2tPQ19TU9rOkJiMyMDU0MDu1xMHQtcSyoreisuXI69DUxNy9z7Luo6zKws7xsdjQ67XItP3HsNK7uPay5cjrtcTN6rPJKLK708O1yLT9ysLO8bXEzeqzySmho8bktM6jrLbU09ppbnNlcnQgLi4uIHNlbGVjdLXEtPPK/b7dwb+1xLLlyOu74dOwz+yy5cjrtcTQ1MTco6zS8s6qwe3Su7j2ysLO8dbQtcSy5cjru+Gxu9foyPuho7TTTXlTUUw1LjEuMjKw5rG+v6rKvKOsSW5ub0RCtOa0otL9x+bM4bmpwcvSu9bWx+HBv7y2u6Wz4sG/tcTX1NT2s6TKtc/Wu/rWxqOs1eLW1rv61sa087TzzOG438HL19TU9rOkJiMyMDU0MDuy5cjrtcTQ1MTcoaPNqLn9ss7K/Wlubm9kYl9hdXRvaW5jX2xvY2tfbW9kZcC0v9jWxtfU1PazpLXExKPKvSjErMjPzqoxKaGj19TU9rOktcSy5cjrvfjQ0LfWwODI5828OjxpbWcgc3JjPQ=="http://www.bitsCN.com/uploadfile/Collfiles/20141220/20141220092504215.jpg" alt="\">
innodb_autoinc_lock_mode的参数值及其对自增长的影响如下图:
MyISAM存储引擎是表锁，自增长不用考虑并发插入的问题。需要注意的是：在InnoDB存储引擎中，自增长值的列必须是索引，同时必须是索引的第一个列，如果不是第一个列，MySQL是会抛出异常的。异常如图
外键与锁
外键主要用于完整性的约束检查。在InnoDB存储引擎中，对于一个外键列，如果没有显式地对这个列加索引，InnoDB存储引擎会自动对其加一个索引，避免表锁。对于外键值的插入或者更新，首先需要查询父表中的记录，对于父表的select操作，不是使用的一致性非锁定读的方式，因为这样会发生数据不一致的问题，所以这时使用的是select ... lock in share mode方式，即主动给父表加一个S锁。
锁的问题
dirty read 脏读
脏读就是读取到脏数据(未提交的数据)。一个事务(A)读取到另一个事务(B)中修改后但尚未提交的数据，并在这个数据的基础上操作。这时，如果B事务回滚，那么A事务读到的数据是无效的。不符合一致性。如图
首先事务的隔离级别有默认的RR改为RU，由上述例子可以看出会话B中两次select操作取得了不同的结果，并且这2条记录是会话A中并未提交的数据，这就产生了脏读。由此可以得出结论:脏读发生的条件是事务的隔离级别为RU。
unrepeatable read 不可重复读
事务(A)读取到了另一个事务(B)已经提交的更改数据，不符合隔离性。不可重复读和脏读的区别是:脏读是读到未提交的数据，而不可重复读则读到的是已经提交的数据。首先将事务隔离级别调整为RC，然后操作下边的例子:
phantom read 幻读
事务(A)读取到了另一个事务(B)提交的新增数据，不符合隔离性。
锁的范围(锁的算法)：
1.Record Lock :单个记录上的锁，总是会去锁住索引记录，如果InnoDB存储引擎表在建立的时候没有设置任何一个索引，那么这时InnoDB存储引擎会使用隐式的主键来进行锁定。
2.Gap Lock：间隙锁，锁定一个范围，但不包含记录本身。
3.Next-key Lock： 锁定一个范围和本身 Record Lock + Gap Lock，防止幻读。
主键索引和唯一辅助索引 = record lock
非唯一辅助索引 = next-key lock
阻塞
不同锁之间的兼容性关系，在有些时刻一个事务中的锁需要等待另外一个事务中的锁释放它所占用的资源，这就是阻塞。阻塞并不是一件坏事，其是为了确保事务可以并发且正常地运行。在InnoDB存储引擎中，参数innodb_lock_wait_timeout用来动态的控制等待的时间(默认50秒)，innodb_rollback_on_timeout用来静态的设定释放在等待超时时对进行的事务进行回滚操作(默认OFF，代表不回滚)。
死锁
注意:
1.S X IS IX，表示的是，本锁和其他锁共存的方式，是互斥还是兼容
2.RECORD LOCK、GAP LOCK、NEXT-KEY LOCK，表示的是，这些锁要加载的范围，是行记录本身，还是行记录+间隙，甚至更大的范围
重要的结论：
1、任何辅助索引上的锁，或者非索引列上的锁，最终都要回溯到主键上，在主键上也要加一把锁
2、任何叶子节点上的S或X锁之前，都会在根节点加一个IS或IX锁，也就是表级别的IS、IX锁
3、主键索引上的锁，都是record lock
4、唯一索引辅助索引上的锁，也都是record lock
5、非唯一索引辅助索引上的锁，则是next-key lock
6、不会有单独的gap lock出现，只会伴随着record lock出现，依附于它
锁是用来管理对共享文件的并发访问。innodb会在行级别上对数据库上锁。不过innodb存储引擎会在数据库内部其他多个地方使用锁，从而允许对不同资源提供并发访问。例如操作缓冲池中的LRU列表，删除，添加，移动LRU列表中的元素，为了保证一致性，必须有锁的介入。MyISAM引擎是表锁，而InnoDB提供一致性的非锁定读、行级锁，且行级锁没有相关额外的开销。
锁
table-level locking（表级锁）
整个表被客户锁定。根据锁定的类型，其他客户不能向表中插入记录，甚至从中读数据也受到限制MyISAM、MEMORY默认锁级别，个别时候，InnoDB也会升级为表级锁
row-level locking（行级锁）
只有线程当前使用的行被锁定，其他行对于其他线程都是可用的InnoDB默认行级锁。是基于索引数据结构来实现的，而不是像ORACLE的锁，是基于block的。InnoDB也会升级为表级锁，全表/全索引更新，请求autoinc锁等
page-level locking（页级锁）
锁定表中某些行集合（称做页），被锁定的行只对锁定最初的线程是可行。如果另外一个线程想要向这些行写数据，它必须等到锁被释放。不过其他页的行仍然可以使用BDB默认页级锁
lock与latch
latch称为闩锁(轻量级的锁)，因为其要求锁定的时间必须非常短。若持续的时间长，则应用的性能会非常差。在InnoDB存储引擎中，又可以分为mutex(互斥量)和rwlock(读写锁)。其目的是用来保证并发线程操作临界资源的正确性，并且通常没有死锁检测的机制。latch可以通过命令show engine innodb mutex来进行查看。如图：
由上图可以看出列Type显示的总是InnoDB，列Name显示latch的信息以及所在源码的行数，列Status中显示的os_waits表示操作系统等待的次数。
lock的对象是事务，用来锁定的是数据库中的对象，如表、页、行。并且一般lock的对象仅在事务commit或者rollback后释放(不同事务隔离级别释放的时间可能不一样)。有死锁机制。二则的区别如下:
特点：
InnoDB是通过对索引上的索引项加锁来实现行锁。这种特点也就意味着，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。
锁的类型:
有两种标准的行级锁:
共享锁(S lock):允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁.SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
排它锁(X lock):允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他锁.SELECT * FROM table_name WHERE ... FOR UPDATE
InnoDB存储引擎支持意向锁且设计比较简练，分为两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。(意向锁是InnoDB自动加的)
意向共享锁(IS):事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁.
意向独占锁(IX):事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁.
表级意向锁与行级锁的兼容情况如下图：
锁的查看
在InnoDB1.0版本之前只能通过show engine innodb status(transactions行中查看) 或者 show full processlist来查看当前库中锁的请求。但是在这之后在information_schema架构下新增innodb_trx、innodb_locks和innodb_lock_waits三张表记录当前库中锁的情况。
三个表的字段说明如下图
innodb_autoinc_lock_mode的参数值及其对自增长的影响如下图:
MyISAM存储引擎是表锁，自增长不用考虑并发插入的问题。需要注意的是：在InnoDB存储引擎中，自增长值的列必须是索引，同时必须是索引的第一个列，如果不是第一个列，MySQL是会抛出异常的。异常如图
外键与锁
外键主要用于完整性的约束检查。在InnoDB存储引擎中，对于一个外键列，如果没有显式地对这个列加索引，InnoDB存储引擎会自动对其加一个索引，避免表锁。对于外键值的插入或者更新，首先需要查询父表中的记录，对于父表的select操作，不是使用的一致性非锁定读的方式，因为这样会发生数据不一致的问题，所以这时使用的是select ... lock in share mode方式，即主动给父表加一个S锁。
锁的问题
dirty read 脏读
脏读就是读取到脏数据(未提交的数据)。一个事务(A)读取到另一个事务(B)中修改后但尚未提交的数据，并在这个数据的基础上操作。这时，如果B事务回滚，那么A事务读到的数据是无效的。不符合一致性。如图
首先事务的隔离级别有默认的RR改为RU，由上述例子可以看出会话B中两次select操作取得了不同的结果，并且这2条记录是会话A中并未提交的数据，这就产生了脏读。由此可以得出结论:脏读发生的条件是事务的隔离级别为RU。
unrepeatable read 不可重复读
事务(A)读取到了另一个事务(B)已经提交的更改数据，不符合隔离性。不可重复读和脏读的区别是:脏读是读到未提交的数据，而不可重复读则读到的是已经提交的数据。首先将事务隔离级别调整为RC，然后操作下边的例子:
phantom read 幻读
事务(A)读取到了另一个事务(B)提交的新增数据，不符合隔离性。
锁的范围(锁的算法)：
1.Record Lock :单个记录上的锁，总是会去锁住索引记录，如果InnoDB存储引擎表在建立的时候没有设置任何一个索引，那么这时InnoDB存储引擎会使用隐式的主键来进行锁定。
2.Gap Lock：间隙锁，锁定一个范围，但不包含记录本身。
3.Next-key Lock： 锁定一个范围和本身 Record Lock + Gap Lock，防止幻读。
主键索引和唯一辅助索引 = record lock
非唯一辅助索引 = next-key lock
阻塞
不同锁之间的兼容性关系，在有些时刻一个事务中的锁需要等待另外一个事务中的锁释放它所占用的资源，这就是阻塞。阻塞并不是一件坏事，其是为了确保事务可以并发且正常地运行。在InnoDB存储引擎中，参数innodb_lock_wait_timeout用来动态的控制等待的时间(默认50秒)，innodb_rollback_on_timeout用来静态的设定释放在等待超时时对进行的事务进行回滚操作(默认OFF，代表不回滚)。
死锁
死锁是指两个或者两个以上的事务在执行过程中，因争夺资源而造成的一种相互等待的现象。解决死锁最简单的一种方式是超时，即当两个事务相互等待是，当一个等待时间超过设置的某一阀值时，其中一个事务进行回滚，另外一个等待的事务就能继续进行。在InnoDB存储引擎中，参数innodb_lock_wait_timeout用来设置超时时间。但若超时的事务所占权重比较大，如果事务操作更新了很多行，占用了较多的undo log,这时采用FIFO的方式就不合适啦，因为回滚这个事务的时间相对于另一个事务所占用的时间可能会很多。因此，除了超时机制，当前数据库都普遍采用wait-for graph(等待图)的方式来进行死锁检测。要求数据库报错以下两种信息:a.锁的信息链表;b.事务等待链表。通过上述链表可以构造一张图，而在这个图中若存在回路，就代表存在死锁。在wait-for graph中，事务为图中的节点。如图：
注意:
1.S X IS IX，表示的是，本锁和其他锁共存的方式，是互斥还是兼容
2.RECORD LOCK、GAP LOCK、NEXT-KEY LOCK，表示的是，这些锁要加载的范围，是行记录本身，还是行记录+间隙，甚至更大的范围
重要的结论：
1、任何辅助索引上的锁，或者非索引列上的锁，最终都要回溯到主键上，在主键上也要加一把锁
2、任何叶子节点上的S或X锁之前，都会在根节点加一个IS或IX锁，也就是表级别的IS、IX锁
3、主键索引上的锁，都是record lock
4、唯一索引辅助索引上的锁，也都是record lock
5、非唯一索引辅助索引上的锁，则是next-key lock
6、不会有单独的gap lock出现，只会伴随着record lock出现，依附于它
上一篇： php实现向数据库表中插入数据