MySQLInnoDB锁与事务理解20201210

阅读量：4172 次

发布时间：2019-05-26

本文共 29385 字，大约阅读时间需要 97 分钟。

MySQLInnoDB锁与事务理解

概述

事务（Transaction）是并发控制的基本单位。所谓的事务，它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。

事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行，因此事务是一个不可分割的工作逻辑单元。

在数据库系统上执行并发操作时，事务是作为最小的控制单元来使用的，特别适用于多用户同时操作的数据库系统。例如，航空公司的订票系统、银行、保险公司以及证券交易系统等。

事务具有 4 个特性，即原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability），这 4 个特性通常简称为 ACID。

一关于事务的定义有几点需要解释下：

1.数据库事务可以包含一个或多个数据库操作,但这些操作构成一个逻辑上的整体。

2.构成逻辑整体的这些数据库操作,要么全部执行成功,要么全部不执行。

3.构成事务的所有操作,要么全都对数据库产生影响,要么全都不产生影响,即不管事务是否执行成功,数据库总能保持一致性状态。

4.以上即使在数据库出现故障以及并发事务存在的情况下依然成立。

二事务的四大特性

2.1原子性(Atomicity)

原子性(Atomicity):事务中的所有操作作为一个整体像原子一样不可分割，要么全部成功,要么全部失败。

2.2一致性(Consistency)

事务的执行结果必须使数据库从一个一致性状态到另一个一致性状态。一致性是基础，也是最终目的，其他三个特性（原子性、隔离性和持久性）都是为了保证一致性的。

以转账为例子，A向B转账，假设转账之前这两个用户的钱加起来总共是2000，那么A向B转账之后，不管这两个账户怎么转，A用户的钱和B用户的钱加起来的总额还是2000，这个就是事务的一致性。

事务开始前和结束后，数据库的完整性约束没有被破坏。比如A向8转账，不可能A扣了钱，B却没收到。

2.3隔离性（Isolation）

事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

比如多个用户同时往一个账户转账,最后账户的结果应该和他们按先后次序转账的结果一样。

2.4持久性(Durability)

持久性(Durability):事务一旦提交,其对数据库的更新就是持久的。任何事务或系统故障都不会导致数据丢失。

2.5 小结

原子性是事务隔离的基础，隔离性和持久性是手段，最终目的是为了保持数据的一致性。

三如何确保事务的ACID特性

在事务的ACID特性中,C即一致性是事务的根本追求,而对数据一致性的破坏主要来自两个方面

1.事务的并发执行

2.事务故障或系统故障

数据库系统是通过并发控制技术和日志恢复技术来避免这种情况发生的。

并发控制技术保证了事务的隔离性,使数据库的一致性状态不会因为并发执行的操作被破坏。

日志恢复技术保证了事务的原子性,使一致性状态不会因事务或系统故障被破坏。同时使已提交的对数据库的修改不会因系统崩溃而丢失,保证了事务的持久性。

四 MySQL中使用事务

Mysql数据库中使用事务有四种方式，自动提交，显性提交，手动提交，隐式提交。MySQL默认采用自动提交（AUTOCOMMIT)模式。也就是说，如果不是显式地开始一个事务，则每个查询都被当作一个事务执行提交操作。

4.1 自动与手动提交

MySQL默认采用自动提交（AUTOCOMMIT)模式。也就是说，如果不是显式地开始一个事务，则每个查询都被当作一个事务执行提交操作。在当前连接中，可以通过设置AUTOCOMMIT变量来启用或者禁用自动提交模式:

show variables like '%autocom%';

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

| Variable_name | Value |

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

| autocommit | ON |

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

1 row in set (0.00 sec)

1或者ON表示启用，0或者OFF表示禁用。当 AUTOCOMMIT=0时，所有的查询都是在一个事务中，直到显式地执行COMMIT提交或者ROLLBACK回滚，该事务结束，同时又开始了另一个新事务。

set session AUTOCOMMIT=0;

delete from syjm5;

没有提交

select count(*) from syjm5;

50000

set session AUTOCOMMIT=0;

delete from syjm5;

commit;

select count(*) from syjm5;

4.2 显式提交

事务的语句

　开始事物：BEGIN

　提交事物：COMMIT

　回滚事务：ROLLBACK

结束事务：END

事务的保存点

SAVE TRANSACTION 保存点名称 --自定义保存点的名称和位置

ROLLBACK TRANSACTION 保存点名称 --回滚到自定义的保存点

begin tran1 --开始执行事务

update bb setmoneys=moneys-@momeys where ID=@fromID -执行的第一个操作，转账出钱，减去转出的金额

update bb setmoneys=moneys+@momeys where ID=@toID --执行第二个操作，接受转账的金额，增加转来的金额

if@@error<>0 --判断如果两条语句有任何一条出现错误

begin

rollback –开始执行事务的回滚，恢复的转账开始之前状态

return 0

end

else --如何两条都执行成功

begin

commit --执行这个事务的操作

return 1

end

4.3 隐式提交

又名自动提交，即无需显示执行commit语句，session中的操作被自动提交到数据库的过程。

隐式提交的方式：

1、正常执行完DDL语句。包括create，alter，drop，truncate，rename。

2、正常执行完DCL语句。包括grant，revoke。

3、正常退出数据库管理软件，没有明确发出commit或者rollback。

隐式提交的注意事项：

1、执行DDL语句时，前面的DML操作也会被提交到数据库中，因为是在一个session里，那执行ddl语句的时候前面的dml语句肯定也会“不可幸免”的被提交到库中。

2、即使DDL语句执行失败，前面的DML操作也会被提交到数据库中这就有点儿让人奇怪了，DDL都执行失败了，怎么还会提交呢？这就需要探究一下隐式提交的本质了（下文有叙述）。

3、在前面1和2的基础上，为了避免隐式提交或者回滚，尽量保证一条或者几条DML操作完成后有显示的提交或者回滚，防止后续执行的DCL或者DDL自动提交前期的DML操作。

五并发与隔离级别

数据库是一个共享资源，可以供多个用户使用，众多用户同时使用同一个数据库，称为高并发数据库。在同一时刻并发运行的事务数可达数百上千个。例如飞机订票数据库系统、银行数据库系统等都是高并发数据库系统。

5.1 高并发带来问题

高并发会出现丢失更新，脏读，不可重复读，幻读等问题，是通过隔离级别解决。但是隔离级别越高，数据库的并发性能越差，性能越低。

5.2 丢失更新Lost Update

更新丢失(Lost Update )A和B同时写

1.事务A将数值改为1并提交﹔⒉.事务B将数值改为2并提交。

这时数据的值为2，事务A所做的更新将会丢失。

解决办法︰对行加锁，只允许并发一个更新事务。

5.3 脏读

事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据。

例如︰

1.风哥的学员张同学的原工资10000，财务人员将张同学的工资改为20000。(但未提交事务)

2.张同学这时去查自己的工资，发现自己的工资变为了20000，这小子够开心了，准备提前拿1万出去玩玩。

3.而财务发现操作有误，回滚了事务，张同学的工资又变为了10000 ,像这样，张同学之前查到的工资数20000是一个脏数据。

5.4 不可重复读

事务A多次读取同一数据，事务B在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。

A先读，B再改，A再读

例如:

1.在事务A中，张同学读取了自己的工资为10000，操作并没有完成。

⒉在事务B中，这时财务人员修改了张同学的工资为20000，并提交了事务。

3.在事务A中，张同学再次读取自己的工资时，工资变为了20000.

解决办法∶如果只有在修改事务完全提交之后才可以读取数据，则可以避免该问题。

5.5 幻读

B事务读取了两次数据，在这两次的读取过程中A事务添加了数据，B事务的这两次读取出来的集合不一样。

系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

A改后还未提交，B改其他的，A再查

例如︰

1.A把所有的“10000”改为“20000"

2.B把所有的“5000”改为“10000"3.A再查询10000，却发现还有一批。

小结︰不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。

5.4 事务隔离级别

Read Uncommitted(读未提交)

最低的隔离级别,什么都不需要做,一个事务可以读到另一个事务未提交的结果;所有的并发事务问题都会发生,解决了更新丢失。

Read Committed(读已提交)

只有在事务提交后,其更新结果才会被其他事务看见。解决了更新丢失、脏读。大多数数据库的默认隔离级别为: Read Commited(读已提交) ,如Oracle , DB2 , Sql Server。

Repeated Read(重复读)

在一个事务中,对于同一份数据的读取结果总是相同的,无论是否有其他事务对这份数据进行操作,以及这个事务是否提交。解决了更新丢失、脏读、不可重复读。

少数数据库默认的隔离级别为Repeatable Read(可重复读)，

如MySQL InnoDB存储弓|擎,因为考虑到数据安全,请使用Read Commited(读已提交)。

Serialization(串行化)

事务串行化执行，隔离级别最高,牺牲了系统的并发性。可以解决并发事务的所有问题。

show variables like tx_isolation;

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

| Variable_name | Value |

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

| tx_isolation | READ-COMMITTED |

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

六 MySQL MVCC(多版本并发控制)

6.1 功能介绍

MVCC(Multi Version Concurrency Control的简称)，代表多版本并发控制。与MVCC相对的，是基于锁的并发控制，Lock-Based Concurrency Control)。

MVCC最大的优势：读不加锁，读写不冲突。在读多写少的OLTP应用中，读写不冲突是非常重要的，极大的增加了系统的并发性能。

指的是一种提高并发的技术。最早的数据库版本，只有读读之间可以并发;但读写/写读/写写都要阻塞。

引入多版本之后,只有写写之间相互阻塞,其他三种操作(读读/读写/写读)都可以并行,这样大幅度提高了InnoDB的并发度。

在内部实现中, InnoDB是通过在undo log中实现的,通过undo log可以找回数据的历史版本(前镜像) , 找回的数据历史版本可以提供给用户读,也可以在回滚的时候覆盖数据页上的数据。

6.2 多版本并发控制实现原理

每个事务启动时，InnoDB会为每个启动的事务提供一个当前时刻的镜像;

为了实现此功能, InnoDB会为每个表提供2个隐藏字段:

一个用于保存行的创建时间，

一个用于保存行的失效时间(里面存储的是系统版本号: system version number )

在某一事务中,使用比当前事务相等或更旧的版本号数据,从而保证其所读取的数据都是过去的数据。

6.3 MVCC实现

MVCC是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存行的过期时间（或删除时间）。当然存储的并不是实际的时间值，而是系统版本号（system version number)。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。

下面看一下在REPEATABLE READ隔离级别下，MVCC具体是如何操作的。

SELECT

InnoDB会根据以下两个条件检查每行记录：

InnoDB只查找版本早于当前事务版本的数据行（也就是，行的系统版本号小于或等于事务的系统版本号），这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的。

行的删除版本要么未定义，要么大于当前事务版本号。这可以确保事务读取到的行，在事务开始之前未被删除。

只有符合上述两个条件的记录，才能返回作为查询结果

INSERT

InnoDB为新插入的每一行保存当前系统版本号作为行版本号。

DELETE

InnoDB为删除的每一行保存当前系统版本号作为行删除标识。

UPDATE

InnoDB为插入一行新记录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识。保存这两个额外系统版本号，使大多数读操作都可以不用加锁。这样设计使得读数据操作很简单，性能很好，并且也能保证只会读取到符合标准的行，不足之处是每行记录都需要额外的存储空间，需要做更多的行检查工作，以及一些额外的维护工作

6.4 MVCC优缺点

6.4.1 MVCC的优点

在读取数据的时候, innodb几乎不用获得任何锁，每个查询都通过版本检查,只获得自己需要的数据版本，从而大大提高了系统的并发度。

6.4.2 MVCC的缺点

为了实现多版本, innodb必须对每行增加相应的字段来存储版本信息,同时需要维护每一行的版本信息,而且在检索行的时候,需要进行版本的比较，因而降低了查询的效率;

innodb还必须定期清理不再需要的行版本,及时回收空间,这也增加了一些开销。

七通过案例理解隔离级别

7.1 读未提交Read Uncommited

mysql> set session tx_isolation='read-uncommitted';

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show variables like '%tx_isolation%';

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

| Variable_name | Value |

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

| tx_isolation | READ-UNCOMMITTED |

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

1 row in set (0.00 sec)

窗口1

Begin

create table syjjjj(id int,name varchar(10),age int);

窗口2 读出的数据是错误的

7.2 读已提交 Read commited

mysql> set session tx_isolation='READ-COMMITTED';

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show variables like '%tx_isolation%';

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

| Variable_name | Value |

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

| tx_isolation | READ-COMMITTED |

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

1 row in set (0.01 sec)

7.3可重复读Repeatable Read

set session tx_isolation='Repeatable-Read';

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show variables like '%tx_isolation%';

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

| Variable_name | Value |

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

| tx_isolation | REPEATABLE-READ |

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

1 row in set (0.00 sec)

隔离级别

表数据

A插入已经提交了，但是B永远无法查询

如果想查出数据，必须要B重新连接，有一个新事务以后，才能查出数据。

八 MySQL锁管理

8.1 MySQL锁介绍

MySQL锁是数据库管理系统协调多个进程或线程并发访问某一数据的机制。

在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所在有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。锁有效解决一致性和有效性。

锁冲突也是影响数据库并发访问性能的一个重要因素。锁是Mysql在服务器层和存储引擎层的的并发控制。

加锁是消耗资源的，锁的各种操作，包括获得锁、检测锁是否是否已解除、释放锁等。

8.2 根据锁粒度分为3类

不同存储引擎的可以支持的锁类型

Lock锁根据粒度主要分为表锁、页锁和行锁。不同的存储引擎拥有的锁粒度都不同。

8.2.1 表锁:table lock

表锁:table lock，是MySQL各存储引擎中最大颗粒度的锁定机制，该锁定机制最大的特点是实现逻辑非常简单，带来的系统负面影响最小。获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定，所以可以很好的避免困扰我们的死锁问题。锁定了整张表，开销小，加锁快﹔不会出现死锁;锁定粒度大，发生锁冲突的概率最高，并发度最低。

当然，锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高，致使并大度大打折扣。使用表级锁定的主要是MyISAM，MEMORY，CSV等一些非事务性存储引擎。

表级锁更适合于以查询为主，并发用户少，只有少量按索引条件更新数据的应用，如Web 应用。

MyISAM 在执行查询语句（SELECT）前，会自动给涉及的表加读锁，在执行更新操作（UPDATE、DELETE、INSERT 等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显式加锁。

用户也可以使用lock 和UNLOCK 手动添加与解除表锁

LOCK TABLE itpux_m5 read, itpux_m1 read;

select count(*) as 'sum' from itpux_m1;

select count(*) as 'sum' from itpux_m5;

UNLOCK TABLES;

8.2.2 行锁:row lock

行锁:row lock，锁定了需要的行，开销大，加锁慢﹔会出现死锁;锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能。

虽然能够在并发处理能力上面有较大的优势，但是行级锁定也因此带来了不少弊端。由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。此外，行级锁定也最容易发生死锁。

使用行级锁定的主要是InnoDB存储引擎。

行级锁∶更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理(OLTP）系统。

注:锁的粒度越小，开销越大，但并发性越好。

A行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

B最大程度的支持并发，同时也带来了最大的锁开销。

C在 InnoDB 中，除单个 SQL 组成的事务外，锁是逐步获得的，这就决定了在 InnoDB 中发生死锁是可能的。

D行级锁只在存储引擎层实现，而Mysql服务器层没有实现。行级锁更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统

8.2.3页锁:page lock

页锁:page lock，开销和加锁时间界于表锁和行锁之间; 获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。另外，页级锁定和行级锁定一样，会发生死锁。并发度在两者之间。

8.2.4 锁粒度总结

在数据库实现资源锁定的过程中，随着锁定资源颗粒度的减小，锁定相同数据量的数据所需要消耗的内存数量是越来越多的，实现算法也会越来越复杂。不过，随着锁定资源颗粒度的减小，应用程序的访问请求遇到锁等待的可能性也会随之降低，系统整体并发度也随之提升。

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

从锁的角度来说，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。

8.3 InnoDB锁模式分类

8.3.1 锁分类介绍

对数据的操作其实只有两种，也就是读和写，而数据库在实现锁时，也会对这两种操作使用不同的锁；InnoDB 实现了标准的行级锁，也就是共享锁（Shared Lock）和排他锁（Exclusive Lock）。

共享锁（读锁），允许事务读一行数据。

排他锁（写锁），允许事务删除或更新一行数据。

而它们的名字也暗示着各自的另外一个特性，共享锁之间是兼容的，而互斥锁与其他任意锁都不兼容：

lnnodb 实现了两种类型的行锁:

·共享锁(S)︰允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁，允许其他事物再加S锁，不允许其他事物再加X锁。非阻塞。

排他锁(X)∶允许获得排他锁的事务更新数据，阻止其他事务获取相同数据集的共享读锁和排他写锁。也称写锁，不允许其他事务再加S锁或者X锁，阻塞。

另外，为了允许行锁和表锁共存，事项多粒度锁机制,innodb还有两种内部使用的意向锁，这两种意向锁都是表锁:

·意向共享锁(IS)︰事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。

·意向排它锁（IX)︰事务打算给数据行加行排它锁，事务在给一个数据行加排它锁前必须先取得该表的IX锁。

8.3.2 锁兼容

8.4 手动加锁

8.4.1 行锁

事务可以通过以下语句显式给记录集加共享锁或排它锁。

共享锁(S) : select * from table_name where ... lock in share

排它锁(X) : select * from table_name where ... for update.

8.4.2 表锁

mysql 的表锁 lock tables 感觉就像一个封闭的空间

LOCK TABLE `table` [READ|WRITE]

解锁

UNLOCK TABLES;

//如将 table1 设为read锁, table2 设为write锁, table3 设为read锁

lock tables [table1] read,[table2] write,[table3] read;

----------------------------------------------------------------------

//执行到这里时,进入封闭空间。

1. table1 仅允许[所有人]读,[空间外]如需写、更新要等待[空间退出],[空间内]如需写、更新会引发mysql报错。

2. table2 仅允许[空间内]读写更新,[空间外]如需写、更新要等待[空间退出]。

3. table3 仅允许[所有人]读,[空间外]如需写、更新要等待[空间退出],[空间内]如需写、更新会引发mysql报错。

----------------------------------------------------------------------

//执行到这里时,退出封闭空间,释放所有表锁

unlock tables

8.5 InnoDB锁算法

Record Lock、Gap Lock、Next-key Lock锁

8.5.1 行锁Record Lock

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。单条索引上加锁，record lock 永远锁的是索引，而非数据本身。

如果innodb表中没有索引，那么会自动创建一个隐藏的聚集索引，锁住的就是这个聚集索引。所以说当一条sql没有走任何索引时，那么将会在每一条聚集索引后面加X锁，这个类似于表锁，但原理上和表锁应该是完全不同的。

记录锁锁定的是索引记录，而不是行数据，也就是说锁定的是key。

该锁是加在索引上的(从上面的index PRIMARY of table 'itpux `. fg`就能看出来)记录锁可以有两种类型∶

lock_mode X locks rec but not gap

lock_mode s locks rec but not gap

8.5.2 间隙锁Gap Lock

间隙锁，是在索引的间隙之间加上锁，这是为什么Repeatable Read隔离级别下能防止幻读的主要原因。

Gap锁（间隙锁），锁住的不是记录，而是范围，一般是针对非唯一索引而言的，确保索引记录的间隙不变，间隙锁是针对事务隔离级别为可重复读或以上级别而已的。

eg:RECORD LOCKS space id 12 page no 5 n bits 23 index idx_fg of table 'itpux'. fg ' trx id 6686 lock_mode X locks gap before rec

间隙锁的出现主要集中在同一个事务中先delete后insert的情况下，当去删除一条记录的时候，如果这个记录存在，那么这个时产生普通行锁，锁住这个记录，然后删除再释放锁。

如果这条记录不存在，问题就来了，数据库会扫描索引，发现这个记录不存在，这个时候的delete语句获取到的就是一个间隙锁，然后数据库会向左扫描扫到第一个比给定参数小的值，向右扫描扫描到第一个比给定参数大的值，然后以此为界，构建一个区间，锁住整个区间内的数据，一个特别容易出现死锁的间隙锁诞生了。

很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。

8.5.3 Next-Key Lock

前两种的结合，对记录及其前面的间隙加锁。

默认情况下，InnoDB工作在可重复读隔离级别下，并且会以Next-Key Lock的方式对数据行进行加锁，这样可以有效防止幻读的发生。

Next-Key Lock是行锁和间隙锁的组合，当InnoDB扫描索引记录的时候，会首先对索引记录加上行锁(Record Lock )，再对索引记录两边的间隙加上间隙锁(Gap Lock )。加上间隙锁之后，其他事务就不能在这个间隙修改或者插入记录。

eg : RECORD LOCKS space id 12page no 5 n bits 23 index idx_fg of table 'itpux '. 'fg'trx id6686 lock_mode x

8.5.4 Insert Intention Locks插入意向锁

可以理解为特殊的Gap间隔锁的一种，用以提升并发写入的性能。

eg :RECORD LOCKS space id 12 page no 3 n bits 23 index PRIMARY of table 'itpux . `fg trx id6686 lock_mode X insert intention waiting

8.5.5 .AUTO-INC Locks自增锁

属于表级别的锁，

涉及到参数innodb_auto_lockmode三个取值∶（在复制有时候细讲）与复制有关。

insert—般分为两种，

第一种:简单的insert。插入之前知道插入的行数，autoincrement提前分配，提前就确定了，如:insert ,replace。

第二种:块的insert。插入之前不知道多少插入的行数，如:insert select , load data ,replace , replace ,replace ... select

简单insert不会生成auto-inc locking(自增锁）。

当事务A，开始insert时，事务B被锁住，当事务A insert结束时，事务B insert被解锁并插入成功。这样的目的是Mysql通过auto-inc locking来保障块插入是连续的所以在此期间不能有新记录插入。

如果列中没有自增量，auto-inc locking锁自然也不会存在。

工作中经常有使用MySQL自带的autoincrement函数作为计数器，在实际使用中当并发比较小的时候还没有问题，一旦并发增加就会出现很多问题，因为插入的记录行数不能马上确定的。

自增锁在事物提交前就释放，其他的锁是在事物提交时才释放

如果自增锁锁在提交后才释放，那其他事物就没法插入了，无法实现并发。

eg : TABLE LOCK table xx trx id 6686 lock mode AUTO-INC waiting

8.5.6 显示锁(explicit lock)

手工加锁，在show engine innoDB status 中能够看到,会在内存中产生对象,占用内存.eg: select ... for update , select ... lock in share mode

8.5.7 .隐示锁(implicit lock)

在索引中对记录逻辑的加锁，但是实际上不产生锁对象，不占用内存空间.

哪些语句会产生implicit lock呢?

insert into xx values(xx)

update xx set t=t+1 where id = 1;会对辅助索引加implicit lock

implicit lock产生冲突的时候，会自动转换成explicit lock,这样做的好处就是降低锁的开销。

比如∶我插入了一条记录100，本身这个记录加上implicit lock，如果这时候有人再去更新这条100的记录，那么就会自动转换成explicit lock

8.5.8 metadata lock元数据锁,数据字典锁

这是MySQL Server层实现的锁，跟引擎层无关，当你执行select的时候，如果这时候有ddi语句，那么ddl会被阻塞，因为select语句拥有metadata lock，防止元数据被改掉。

8.6 其他名称解释

8.6.1 锁迁移（锁继承）

比如我锁住的记录是一条已经被标记为删除的记录，但是还没有被puger，然后这条被purge掉了，那么上面的锁自然而然就继承给了下一条记录，我们称之为锁迁移。

锁升级︰执行一条全表更新的语句，那么数据库就会对所有记录进行加锁，那么可能造成锁开销非常大，可能升级为页锁，或者表锁。MySQL没有锁升级功能。

8.6.2 锁分裂与锁合并

InnoDB的实现加锁，其实是在页上面做的，没有办法直接对记录加锁。当insert的时候，会产生页的分裂动作(行溢出），如果页分裂了，锁对象也会分裂。有页分裂，也就有页合并，锁也会合作。

8.6.3 latch

latch一般称为门( shuan)锁（轻重级别的钡)，内公是I水oTT出I的论什过。若持续的时间长，则应用的性能会非常差。临界资源用完释放，不支持死锁检测，是应用程序中的锁，不是数据库的锁。

在lnnodb存储引擎中，latch又可以分为mutex(互斥量）和RW-Lock（读写锁）。

8.6.4 .lock

lock对象是事务，用来锁定的是数据库中的对象，如表、行、页。

一般lock的对象仅在事务commit或rollback后进行释放（不同事务隔离级别释放的时间可能不同）。

此外，lock正如在大多数数据库中一样，是有死锁机制的。

当事务结束后，释放，支持死锁检测，是数据库中的锁。

8.6.5死锁

所谓死锁<DeadLock>:是指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去。

此时系统处于死锁状态或系统产生了死锁，这些在互相等待的进程称为死锁进程。表级锁不会产生死锁，所以解决死锁主要还是针对于最常用的InnoDB。

如果没有死锁检测，那么就会互相卡死，一直hang死。

如果有死锁检测机制，那么数据库会自动根据代价来评估出哪些事务可以被回滚掉，用来打破这个僵局。

总结:

死锁并没有啥坏处，反而可以保护数据库和应用。

那么出现死锁，而且非常频繁，我们应该调整业务逻辑，让其避免产生死锁方为上策。

九阅读mysql锁日志

主要针对两种情况，1.表里面有索引。2.表里面没有索引

9.1 有索引查看锁日志

create table syj111(

id int PRIMARY key auto_increment,

name VARCHAR(20),

age int (10),

key idx_name (name desc)

) engine=innodb DEFAULT charset=utf8;

insert into syj111 values (1,'jjj',27);

insert into syj111 values (2,'lhr',22);

insert into syj111 values (3,'fj',25);

insert into syj111 values (4,'db',23);

insert into syj111 values (5,'hn',23);

desc syj111;

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

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

| id | int(11) | NO | PRI | NULL | auto_increment |

| age | int(10) | YES | | NULL | |

set tx_isolation='Repeatable-Read';

##开始事务未提交

begin;

select * from syj111 where id=1 for update;

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

| id | name | age |

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

| 1 | jjj | 27 |

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

1 row in set (0.00 sec)

show engine innodb status\G;

锁详细日志没有被打开

TRANSACTIONS

------------

Trx id counter 12069

Purge done for trx's n:o < 12066 undo n:o < 0 state: running but idle

History list length 0

LIST OF TRANSACTIONS FOR EACH SESSION:

---TRANSACTION 421691023244000, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421691023243088, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

show variables like '%output_locks%';

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

| Variable_name | Value |

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

| innodb_status_output_locks | OFF |

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

1 row in set (0.00 sec)

set globalinnodb_status_output_locks=1;

TRANSACTIONS

------------

Trx id counter 12565

---下一个事务号 12565

Purge done for trx's n:o < 0 undo n:o < 0 state: running but idle

--purge 线程是空的。undo n:o < 0中未被purge的事务数为0。Purge done for trx's n:o < 0解释purge线程已经清理了<0事务。

InnoDB中delete所做删除只是标记为删除的状态，实际上并没有删除掉，因为MVCC机制的存在，要保留之前的版本为并发所使用。最终的删除由purge线程来决定的什么时候来真正删除文件的。

History list length 0

--每10秒会删除无用的undo页，这里的History list length就指上一个10秒之后的未删除的事务数，一般100以内为正常。

LIST OF TRANSACTIONS FOR EACH SESSION:

--列出事务会话

---TRANSACTION 421931892361952, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 12563, ACTIVE 339 sec

--事务ID12563 已经被锁住339秒

lock struct(s), heap size 1136, 1 row lock(s)

--一条记录被锁住，内存1136字节

MySQL thread id 2, OS thread handle 140455760918272, query id 55 localhost root starting

show engine innodb status

TABLE LOCK table `itpuxdb`.`syj111` trx id 12566 lock mode IX

--表锁 lock mode IX 排他意向锁，2个记录锁，查询的不是主键，但是通过主键锁定的。

RECORD LOCKS space id 75 page no 4 n bits 72 index idx_name of table `itpuxdb`.`syj111` trx id 12566 lock_mode X

Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

0: len 3; hex 6a6a6a; asc jjj;; --二级索引被锁住了

1: len 4; hex 80000001; asc ;; --一级索引被锁住了

--记录锁 Next-Key Lock（记录锁和间隔锁）75号表空间，4页。

RECORD LOCKS space id 75 page no 3 n bits 72 index PRIMARY of table `itpuxdb`.`syj111` trx id 12566 lock_mode X locks rec but not gap

Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0

0: len 4; hex 80000001; asc ;; --一级索引被锁住了

1: len 6; hex 000000002f15; asc / ;; --事务id 2f15十六进制变成十进制12053

2: len 7; hex b50000003d0110; asc = ;; —占用7字节回滚指针，MVCC

3: len 3; hex 6a6a6a; asc jjj;; --第二个字段jjj被锁定了

4: len 4; hex 8000001b; asc ;; --第三个段被锁定

--记录锁 行锁Record Lock，排它锁。75号表空间，3页。第二行

RECORD LOCKS space id 75 page no 4 n bits 72 index idx_name of table `itpuxdb`.`syj111` trx id 12566 lock_mode X locks gap before rec

Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

0: len 3; hex 6c6872; asc lhr;;

1: len 4; hex 80000002; asc ;;

--间隙锁Gap Lock。75号表空间，4页。第3行

说明主键索引是一级索引，其他索引是二级索引。最后都是通过主键索引锁定的。

mysql> desc syj111;

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

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

| id | int(11) | NO | PRI | NULL | auto_increment |

| age | int(10) | YES | | NULL | |

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

rows in set (0.00 sec)

9.2 表中没有索引的日志

create table syj2(

id int,

name VARCHAR(20),

age int (10)

) engine=innodb DEFAULT charset=utf8;

insert into syj2 values (1,'jjj',27);

insert into syj2 values (2,'lhr',22);

insert into syj2 values (3,'fj',25);

insert into syj2values (4,'db',23);

insert into syj2 values (5,'hn',23);

set tx_isolation='Repeatable-Read';

show variables like '%tx_isolation%';

begin;

select * from syj2 where name=’jjj’ for update;

---TRANSACTION 12584, ACTIVE 17 sec

事务ID 活跃时间17秒。6个锁，一个表锁，5个行锁。

2 lock struct(s), heap size 1136, 6 row lock(s)

MySQL thread id 4, OS thread handle 140455760918272, query id 111 localhost root starting

show engine innodb status

TABLE LOCK table `itpuxdb`.`syj2` trx id 12584 lock mode IX

--表锁 lock mode IX 排他意向锁，由于没有创建索引

RECORD LOCKS space id 77 page no 3 n bits 72 index GEN_CLUST_INDEX of table `itpuxdb`.`syj2` trx id 12584 lock_mode X

--如果表中没有PRIMARY KEY，而且也没有合适的UNIQUE index，那么InnoDB内部将生产一个名字叫GEN_CLUST_INDEX的隐藏clustered index，其值为行ID。

这种锁信息的出现，以为着出现了表锁。在innodb中行级锁是靠索引实现的，出现这种锁信息，基本可以判定字段缺少索引。

Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0

0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0

0: len 6; hex 000000037400; asc t ;;

1: len 6; hex 00000000311b; asc 1 ;;

2: len 7; hex bb0000002b0110; asc + ;;

3: len 4; hex 80000001; asc ;;

4: len 3; hex 6a6a6a; asc jjj;;

5: len 4; hex 8000001b; asc ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0

0: len 6; hex 000000037401; asc t ;;

1: len 6; hex 00000000311c; asc 1 ;;

2: len 7; hex bc0000002d0110; asc - ;;

3: len 4; hex 80000002; asc ;;

4: len 3; hex 6c6872; asc lhr;;

5: len 4; hex 80000016; asc ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 0

0: len 6; hex 000000037402; asc t ;;

1: len 6; hex 000000003121; asc 1!;;

2: len 7; hex bf000000810110; asc ;;

3: len 4; hex 80000003; asc ;;

4: len 2; hex 666a; asc fj;;

5: len 4; hex 80000019; asc ;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 6; compact format; info bits 0

0: len 6; hex 000000037403; asc t ;;

1: len 6; hex 000000003122; asc 1";;

2: len 7; hex c00000003b0110; asc ; ;;

3: len 4; hex 80000005; asc ;;

4: len 2; hex 686e; asc hn;;

5: len 4; hex 80000017; asc ;;

Record lock, heap no 6 PHYSICAL RECORD: n_fields 6; compact format; info bits 0

0: len 6; hex 000000037404; asc t ;;

1: len 6; hex 000000003127; asc 1';;

2: len 7; hex a3000000220110; asc " ;;

3: len 4; hex 80000004; asc ;;

4: len 2; hex 6462; asc db;;

5: len 4; hex 80000017; asc ;;

十 Mysql死锁相关参数

innodb_print_all_deadlocks ON

###死锁的相关日志回输出

innodb_lock_wait_timeout 5

默认是50秒，MySQL获取行锁的时候，有锁超过5秒就超时了。就报锁等待超时。

innodb_deadlock_detect ON

开启死锁检测，数据库自动回滚，如果没有off就用超时来处理，就用这个参数处理innodb_lock_wait_timeout。

innodb_status_output_locks OFF

#默认关闭，建议开启，有详细的记录

10.1 查看锁状态

show engine innodb status\G

show variables like '%timeout%';

innodb_lock_wait_timeout | 5 ##锁等待超时5秒

show status like '%lock%';

show variables like '%autocommit%';

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

| Variable_name | Value |

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

| autocommit | ON |

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

Autocommit ##自动提交

十一 Mysql常见锁及问题解决

11.1 模拟一个典型的锁及处理过程

set global innodb_lock_wait_timeout=5000;

Query OK, 0 rows affected (0.00 sec)

show variables like 'innodb_lock_wait_timeout%';

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

| Variable_name | Value |

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

| innodb_lock_wait_timeout | 5000 |

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

select @@tx_isolation;

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

| @@tx_isolation |

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

| READ-COMMITTED |

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

1 row in set, 1 warning (0.00 sec)

CREATE table syjuser2 (

a int auto_increment PRIMARY key,

b int,

c int,

UNIQUE key idx_b(b)

) ENGINE innodb;

insert into syjuser2 value (null,12,21);

insert into syjuser2 value (null,13,31);

commit;

B窗口由于A窗口没有提交插入12被卡住，

---TRANSACTION 34848, ACTIVE 140 sec inserting

mysql tables in use 1, locked 1

LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1

MySQL thread id 6, OS thread handle 140555715659520, query id 190 localhost root update

insert into syjuser2 value (null,12,22)

------- TRX HAS BEEN WAITING 140 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 153 page no 4 n bits 72 index idx_b of table `itpuxdb`.`syjuser2` trx id 34848 lock mode S waiting

Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 32

0: len 4; hex 8000000c; asc ;;

1: len 4; hex 80000001; asc ;;

------------------

TABLE LOCK table `itpuxdb`.`syjuser2` trx id 34848 lock mode IX

RECORD LOCKS space id 153 page no 4 n bits 72 index idx_b of table `itpuxdb`.`syjuser2` trx id 34848 lock mode S waiting

Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 32

0: len 4; hex 8000000c; asc ;;

1: len 4; hex 80000001; asc ;;

---TRANSACTION 34847, ACTIVE 165 sec

3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1

MySQL thread id 2, OS thread handle 140555718940416, query id 188 localhost root

TABLE LOCK table `itpuxdb`.`syjuser2` trx id 34847 lock mode IX

RECORD LOCKS space id 153 page no 4 n bits 72 index idx_b of table `itpuxdb`.`syjuser2` trx id 34847 lock_mode X locks rec but not gap

Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 32

0: len 4; hex 8000000c; asc ;;

1: len 4; hex 80000001; asc ;;

RECORD LOCKS space id 153 page no 3 n bits 72 index PRIMARY of table `itpuxdb`.`syjuser2` trx id 34847 lock_mode X locks rec but not gap

Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 32

0: len 4; hex 80000001; asc ;;

1: len 6; hex 00000000881f; asc ;;

2: len 7; hex 3d0000002d2513; asc = -% ;;

3: len 4; hex 8000000c; asc ;;

4: len 4; hex 80000015; asc ;;

解决办法1，应用自己提交，锁释放

11.1.1 解决办法2，应用长时间不能提交，锁死，手工干预。

1 查询是否有锁表

show open tables where in_use>0;

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

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

| itpuxdb | syjuser2 | 2 | 0 |

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

Name_locked >0 就是有锁表。

查询单个数据库的情况

show open tables from itpuxdb;

2 查进程

show processlist;

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

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

| 3 | root | 192.168.198.1:51170 | itpuxdb | Sleep | 1462 | | NULL |

| 4 | root | 192.168.198.1:51195 | itpuxdb | Sleep | 1458 | | NULL |

| 5 | root | 192.168.198.1:51198 | itpuxdb | Sleep | 1458 | | NULL |

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

找到有锁的进程ID，杀掉: kill 8;

kill 8;

Query OK, 0 rows affected (0.00 sec)

mysql> show processlist;

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

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

| 3 | root | 192.168.198.1:51170 | itpuxdb | Sleep | 1627 | | NULL |

| 4 | root | 192.168.198.1:51195 | itpuxdb | Sleep | 1623 | | NULL |

| 5 | root | 192.168.198.1:51198 | itpuxdb | Sleep | 1623 | | NULL |

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

11.1.2 show processlist; 列解释

各列的含义和用途：

id，一个标识，你要kill一个线程的时候很有用。

user，显示当前用户，如果不是root，这个命令就只显示你权限范围内的sql语句。

host，显示这个语句是从哪个ip、哪个端口上发出的。

db，显示这个进程目前连接的是哪个数据库。

command，显示这个连接的状态，一般就是休眠（sleep），查询（query），连接（connect）。

time，显示这个状态持续的时间，单位是秒。

state，显示使用当前连接的sql语句的状态，很重要的列，可用来判断mysql的运行状态。

info，显示这个sql语句，因为长度有限，所以长的sql语句就显示不全，但是一个判断问题语句的重要依据。

这个命令中最关键的就是state列，mysql列出的状态主要有以下几种：

Checking table正在检查数据表（这是自动的）。

　Closing tables 正在将表中修改的数据刷新到磁盘中，同时正在关闭已经用完的表。这是一个很快的操作，如果不是这样的话，就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中。

　Connect Out 复制从服务器正在连接主服务器。

　Copying to tmp table on disk 由于临时结果集大于tmp_table_size，正在将临时表从内存存储转为磁盘存储以此节省内存。

　Creating tmp table 正在创建临时表以存放部分查询结果。

　deleting from main table 服务器正在执行多表删除中的第一部分，刚删除第一个表。

　deleting from reference tables 服务器正在执行多表删除中的第二部分，正在删除其他表的记录。

　Flushing tables 正在执行FLUSH TABLES，等待其他线程关闭数据表。

Killed 发送了一个kill请求给某线程，那么这个线程将会检查kill标志位，同时会放弃下一个kill请求。MySQL会在每次的主循环中检查kill标志位，不过有些情况下该线程可能会过一小段才能死掉。如果该线程程被其他线程锁住了，那么kill请求会在锁释放时马上生效。

　Locked 被其他查询锁住了。

　Sending data 正在处理Select查询的记录，同时正在把结果发送给客户端。

　Sorting for group 正在为GROUP BY做排序。

　Sorting for order 正在为ORDER BY做排序。

　Opening tables 这个过程应该会很快，除非受到其他因素的干扰。例如，在执Alter TABLE或LOCK TABLE语句行完以前，数据表无法被其他线程打开。正尝试打开一个表。

　Removing duplicates 正在执行一个Select DISTINCT方式的查询，但是MySQL无法在前一个阶段优化掉那些重复的记录。因此，MySQL需要再次去掉重复的记录，然后再把结果发送给客户端。

　Reopen table 获得了对一个表的锁，但是必须在表结构修改之后才能获得这个锁。已经释放锁，关闭数据表，正尝试重新打开数据表。

　Repair by sorting 修复指令正在排序以创建索引。

　Repair with keycache 修复指令正在利用索引缓存一个一个地创建新索引。它会比Repair by sorting慢些。

　Searching rows for update 正在讲符合条件的记录找出来以备更新。它必须在Update要修改相关的记录之前就完成了。

　Sleeping 正在等待客户端发送新请求.

　System lock 正在等待取得一个外部的系统锁。如果当前没有运行多个mysqld服务器同时请求同一个表，那么可以通过增加--skip-external-locking参数来禁止外部系统锁。

　Upgrading lock Insert DELAYED正在尝试取得一个锁表以插入新记录。

　Updating 正在搜索匹配的记录，并且修改它们。

　User Lock 正在等待GET_LOCK()。

　Waiting for tables 该线程得到通知，数据表结构已经被修改了，需要重新打开数据表以取得新的结构。然后，为了能的重新打开数据表，必须等到所有其他线程关闭这个表。以下几种情况下会产生这个通知：FLUSH TABLES tbl_name, Alter TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE,或OPTIMIZE TABLE。

　waiting for handler insert Insert DELAYED已经处理完了所有待处理的插入操作，正在等待新的请求。