Mysql 锁、事务说的是什么

什么是锁

用于管理对共享资源的并发访问

在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

共享锁(行)
排他锁(行)
意向锁(表)
记录锁(索引)
间隙锁(范围) 左开右闭锁定范围、 例如 1到3 锁定的是1、2、3

读锁，简称S锁，一个事物获取了一个数据行的读锁，其他事物能获得该行对应的读锁，但不能获得写锁，即一个事物在读取一个数据行时，其他事物也可以读，但不能对该数据进行增删改的操作

写锁
写锁，简称X锁，一个事物获取了一个数据行的写锁，其他事物就不能在获取该行的其他锁写锁优先级最高

写锁的应用的就很简单，一些DML(增删改)语句的操作都会对行记录加写锁

还有可能是加字段等修改表结构的操作（DDL）

比较特殊的就是select for update ,它会对读取的行记录加一个写锁，那么其他任何事务就不能对被锁定的行加上任何锁，要不然会被阻塞

InnoDB存储引擎中的锁，行级锁
共享锁： (S Lock) 允许事务读一行数据 读锁
排他锁： (X Lock) 允许事务删除或更新一行数据 写锁

InnoDB 存储引擎，表级锁
意向共享锁(IS Lock) 事务想要获得一张表中某几行的共享锁
意向排他锁(IX Lock) 事务想要获得一张中某几行的排他锁

	IS	IX	S	X
IS	兼容	兼容	兼容	不兼容
IX	兼容	兼容	不兼容	不兼容
S	兼容	不兼容	兼容	不兼容
X	不兼容	不兼容	不兼容	不兼容

lock的对象是事物，用来锁定的是数据库中的对象，如表、页、行。并且一般lock的对象仅在事物commit或rollback后进行释放(不同事物隔离级别释放的时间可能不同)

	lock	latch
对象	事物	线程
保护	数据库内容	内存数据结构
持续时间	整个事物过程	临界资源
模式	行锁、表锁、意向锁	读写锁、互斥量
存在于	Lock Manager 的哈希表中	每个数据结构的对象中
死锁	通过 waits-for graph、time out 等机制进行死锁检测与处理	无死锁检测与处理机制、仅通过应用程序加锁的顺序(lock leveling) 保证无死锁的情况发生

SHOW ENGINE INNODB MUTEX 可以看到Latch的锁

共享锁(S Lock) 允许事物读一行数据
排他锁(X Lock) 允许事物删除或更新一行数据

意向锁为表级别的锁，主要是为了在一个事物中掲示下一行将被请求的锁类型，其支持两种意向锁

意向共享锁 (IS Lock) 事物想要获得一张表中某几行的共享锁
意向排他锁(IX Lock) 事物想要获得一张表中某几行的排他锁

如果是mysql的话用这个命令 sys.innodb_lock_waits

show engine innodb status 查看当前锁请求的信息
show full processlist

• INNODB_TRX
• INNODB_LOCKS
• INNODB_LOCK_WAITS

通过这三张表，用户可以更简单地监控当前事物并分析可能存在的锁的问题

• 查看事务
  select * from information_schema.INNODB_TRX;
• 查看锁
  select * from information_schema.INNODB_LOCKS;
• 查看当前事物的等待
  select * from information_schema.INNODB_LOCK_WAITS;

INNODB_TRX结构说明

详见

字段名	说明
trx_id	InnoDB存储引起内部唯一的事物ID
trx_state	当前事务的状态
trx_started	事物的开始时间
trx_requested_lock_id	等待事务的锁ID，如trx_state的状态为LOCK WAIT，那么该值代表当前的事务等待之前事务占用资源的ID。若trx_state不是LOCK WAIT，则该值为NULL
trx_wait_started	事务等待开始的时间
trx_weight	事务的权重，反映了一个事物修改和锁住的行数。在InnoDB存储引擎中，当发生死锁需要回滚时，InnoDB存储引擎会选择该值最小进行回滚
trx_mysql_thread_id	MySQL中的线程ID，SHOW PROCESSLIT 显示的结果
trx_query	事务运行的SQL语句
TRX_OPERATION_STATE	交易的当前操作，如果有的话; 否则 NULL。
TRX_TABLES_IN_USE	InnoDB处理此事务的当前SQL语句时使用 的表数。
TRX_TABLES_LOCKED	InnoDB当前SQL语句具有行锁定 的表的数量。（因为这些是行锁，而不是表锁，所以通常仍可以通过多个事务读取和写入表，尽管某些行被锁定。）
TRX_LOCK_MEMORY_BYTES	内存中此事务的锁结构占用的总大小
TTRX_LOCK_STRUCTS	事务保留的锁数。
TRX_ROWS_LOCKED	此交易锁定的大致数字或行数。该值可能包括实际存在但对事务不可见的删除标记行
TRX_ROWS_MODIFIED	此事务中已修改和插入的行数。
TRX_CONCURRENCY_TICKETS	一个值，指示当前事务在被换出之前可以执行多少工作
TRX_ISOLATION_LEVEL	当前事务的隔离级别。
TRX_UNIQUE_CHECKS	是否为当前事务打开或关闭唯一检查。例如，在批量数据加载期间可能会关闭它们
TRX_FOREIGN_KEY_CHECKS	是否为当前事务打开或关闭外键检查。例如，在批量数据加载期间可能会关闭它们
TRX_LAST_FOREIGN_KEY_ERROR	最后一个外键错误的详细错误消息（如果有）; 否则NULL
TRX_ADAPTIVE_HASH_LATCHED	自适应哈希索引是否被当前事务锁定。当自适应哈希索引搜索系统被分区时，单个事务不会锁定整个自适应哈希索引。自适应哈希索引分区由innodb_adaptive_hash_index_parts，默认设置为8。
TRX_ADAPTIVE_HASH_TIMEOUT	是否立即为自适应哈希索引放弃搜索锁存器，或者在MySQL的调用之间保留它。当没有自适应哈希索引争用时，该值保持为零，语句保留锁存器直到它们完成。在争用期间，它倒计时到零，并且语句在每次行查找后立即释放锁存器。当自适应散列索引搜索系统被分区（受控制 innodb_adaptive_hash_index_parts）时，该值保持为0
TRX_IS_READ_ONLY	值为1表示事务是只读的。
TRX_AUTOCOMMIT_NON_LOCKING	值为1表示事务是 SELECT不使用FOR UPDATEor或 LOCK IN SHARED MODE子句的语句，并且正在执行， autocommit因此事务将仅包含此一个语句。当此列和TRX_IS_READ_ONLY都为1时，InnoDB优化事务以减少与更改表数据的事务关联的开销

***********************1.row**********************************
trx_id:7311F4
trx_state:LOCK_WAIT
trx_started: 2010-01-04 10:49:33
trx_requested_lock_id: 7311F4:96:3:2
trx_wait_started: 2010-01-04 10:49:33
trx_weight:2 
trx_mysql_thread_id:471719
trx_query: select * from parent lock in share mode 

**********************2.row****************************
trx_id:730FEE
trx_state:RUNNING
trx_started:2010-01-04 10;18:37
trx_requested_lock_id:NULL
trx_wait_started:NULL
trx_weight:2
trx_mysql_thread_id:471718
trx_query:NULL

还需要访问表INNODB_LOCKS

详见https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-table.html

lock_id	锁的ID
lock_trx_id	事务ID
lock_mode	锁的模式
lock_type	锁的类型，表锁还是行锁
lock_table	要加锁的表
lock_index	锁住的索引
lock_space	锁对象的space id
lock_page	事物锁定页的数量。若是表锁，则该值为NULL
lock_rec	事务锁定行的数量，若是表锁，则该值为NULL
lock_data	事务锁定记录的主键值，若是表锁，则该值为NULL

*******************1.row*******************
lock_id:7311F4:96:3:2
lock_trx_id:7311F4
lock_mode:S
lock_type:RECORD
lock_table:'mytest'.'parent'
lock_index:'PRIMARY'
lock_space:96
lock_page:3
lock_rec:2
lock_data:1
************************2.row***************************
lock_id:730FEE:96:3:2
lock_trx_id:730FEE
lock_mode:X
lock_type:RECORD
lock_table:'mytest'.'parent'
lock_space:96
lock_page:3
lock_rec:2
lock_data:1

用户可以清晰地看到当前锁的信息.trx_id为730FEE的事务向表parent加了一个X的行锁，ID为7311F4的事务向表parent申请了一个S的行锁。lock_data都是1，申请相同的资源，因此会有等待。这也可以解释INNODB_TRX中为什么一个事务的trx_state是“RUNNING”,另一个是"LOCK WAIT了
"

当事务较小时，用户就可以认为地、直观地进行判断了。但是当事务量非常大，其中锁和等待也时常发生，这个时候就不这么容易判断了。通过表INNODB_LOCK_WAITS,可以很直观地反映当前事物的等待

INNODB_LOCK_WAITS

https://dev.mysql.com/doc/refman/5.7/en/innodb-lock-waits-table.html

requesting_trx_id	申请锁资源的事务ID
requesting_lock_id	申请的锁的ID
blocking_trx_id	阻塞的事物ID
blocking_lock_id	阻塞的锁的ID

requesinng_trx:7311F4
requested_lock_id: 7311F4:96:3:2
blocking_trx_id:730FEE
blocking_lock_id:730FEE:96:3:2

事务 A 在等待事务 B 释放 id=2 的行锁，而事务 B 在等待事务 A 释放 id=1 的行锁。 事务 A 和事务 B 在互相等待对方的资源释放，就是进入了死锁状态。当出现死锁以后，

有两种策略：

1. 一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。
2. 另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数innodb_deadlock_detect 设置为 on，表示开启这个逻辑。

正常情况下我们还是要采用第二种策略，即：主动死锁检测，而且 innodb_deadlock_detect 的默认值本身就是 on。主动死锁检测在发生死锁的时候，是能够快速发现并进行处理的，但是它也是有额外负担的。

还不行，就逻辑设计优化

事务特性

原则性：要不全部提交，要不全部失败
一致性: 在事物开始之前和开始结束，对数据中完整性没有被破坏掉
隔离性: 一个事物修改数据，在未提交完成前，对于其它事物是不可见的
持久性: 一旦事物提交，所有修改的数据都会永久保存到数据库中，系统崩溃，数据都不会丢失

事务的隔离级别

1. Read uncommitted 读未提交: 一个事务还没提交时，它做的变更就能被别的事务看到
2. Read committed 读已提交: 一个事务提交之后，它做的变更才会被其他事务看到。(不可重复读与幻读这个级别出现的)
3. Repeatable read 可重复读: 个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
4. Serializable 序列化(串行): 顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行

脏读 读取未提交

脏数据是指未提交的数据,如果读到了脏数据，即一个事务可以读到另外一个事务中未提交的数据，则显然违反了数据库的隔离性

其中“读提交”和“可重复读”比较难理解，所以我用一个例子说明这几种隔离级别。假设数据表 T 中只有一列，其中一行的值为 1，下面是按照时间顺序执行两个事务的行为

mysql> create table T(c int) engine=InnoDB;insert into T(c) values(1);

我们来看看在不同的隔离级别下，事务 A 会有哪些不同的返回结果，也就是图里面 V1、V2、V3 的返回值分别是什么。

• 若隔离级别是“读未提交”， 则 V1 的值就是 2。这时候事务 B 虽然还没有提交，但是结果已经被 A 看到了。因此，V2、V3 也都是2。
• 若隔离级别是“读提交”，则 V1 是 1，V2 的值是 2。事务 B 的更新在提交后才能被 A 看到。所以， V3 的值也是 2。
• 若隔离级别是“可重复读”，则 V1、V2 是 1，V3 是 2。之所以 V2 还是1，遵循的就是这个要求：事务在执行期间看到的数据前后必须是一致的。可重复读只要事务A内没有执行对事务B的更新sql语句，那读V1、V2的数据就会一直不变。
• 若隔离级别是“串行化”，则在事务 B 执行“将 1 改成 2”的时候，会被锁住。直到事务 A 提交后，事务 B 才可以继续执行。所以从 A
  的角度看， V1、V2 值是 1，V3 的值是 2

你在管理一个个人银行账户表。一个表存了账户余额，一个表存了账单明细。到了月底你要做数据校对，也就是判断上个月的余额和当前余额的差额，是否与本月的账单明细一致。即使有用户发生了一笔新的交易，也不影响你的校对结果。这时候使用“可重复读”隔离级别就很方便

用于查找持续时间超过 60s 的事务

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

例如：
张三的工资为5000,事务A中把他的工资改为8000,但事务A尚未提交。
与此同时，
事务B正在读取张三的工资，读取到张三的工资为8000。
随后，
事务A发生异常，而回滚了事务。张三的工资又回滚为5000。
最后，
事务B读取到的张三工资为8000的数据即为脏数据，事务B做了一次脏读。

不可重复度

• 不可重复读是指在一个事务内多次读取同一个数据集合，数据内容不一致。在这个事务还没有结束时，另外一个事务也访问该同一个数据集合，并做了一些DML操作。因此在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的情况，这种情况称为不可重复读
• 在简单说明：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致
• 出现子更新和删除

例如：
在事务A中，读取到张三的工资为5000，操作没有完成，事务还没提交。
与此同时，
事务B把张三的工资改为8000，并提交了事务。
随后，
在事务A中，再次读取张三的工资，此时工资变为8000。在一个事务中前后两次读取的结果并不致，导致了不可重复读。

幻读

前后多次读取，数据总量不一致

• 事务A在执行读取操作，需要两次统计数据的总量，前一次查询数据总量后，此时事务B执行了新增数据的操作并提交后，这个时候事务A读取的数据总量和之前统计的不一样，就像产生了幻觉一样，平白无故的多了几条数据，成为幻读。
• 出现在新增

例如：
目前工资为5000的员工有10人，事务A读取所有工资为5000的人数为10人。
此时，
事务B插入一条工资也为5000的记录。
这是，事务A再次读取工资为5000的员工，记录为11人。此时产生了幻读。

行锁的3种算法

MySQL 默认的级别是:Repeatable read 可重复读

当隔离级别是可重复读，且禁用innodb_locks_unsafe_for_binlog的情况下，在搜索和扫描index的时候使用的next-key locks可以避免幻读。

innodb_locks_unsafe_for_binlog：
静态参数，默认为0，表示启动gap lock，如果设置为1，表示禁用gap lock

next-key lock 锁定一个范围，并且锁定记录本身

不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

• Record Lock：行锁，锁定单个行记录。
• Gap Lock：间隙锁，锁定一个范围，但不包括记录本身，GAP锁的目的，是为了防止幻读。
• Next-Key Lock：即行锁+间隙锁。锁定一个范围,并且锁定记录本身，当Innodb扫描索引记录时，会先对选中的索引记录加上记录锁，在RR(可重复读隔离级别下默认就有)

阻塞

阻塞： 一个事务的锁需要等待另一个事务的锁释放

死锁

死锁： 两个或两个以上的事务执行过程中，相互占用对方等待的资源，而产生的异常

• 如果程序是串行的，那么不可能发生死锁
• 死锁只存在并发的情况，A等待B，B在等待A，这种死锁间隙被称为AB-BA锁

主动死锁检测，而且 innodb_deadlock_detect 的默认值本身就是 on。主动死锁检测在发生死锁的时候，是能够快速发现并进行处理的，但是它也是有额外负担的

悲观锁

一锁二查三更新

特点是先获取锁，再进行业务操作

为了保证事务的隔离性，就需要一致性锁定读。读取数据时给加锁，其它事务无法修改这些数据

必须是通过索引条件来检索数据，否则会切换为表锁

原理：在查询出信息就把当前的数据锁定，直到我们修改完毕后再解锁，将防止其它进程读取或修改表中的数据

乐观锁

先进行业务操作，不到万不得已不去拿锁

大多是基于数据版本号（ Version ）记录机制实现

商品表添加version版本字段或者timestamp时间戳字段

原理：数据更新的时候需要判断该数据是否被别人修改过，如果数据被其他线程修改，则不进行数据更新，如果没有则进行数据更新

通常的实现方式是：对表的数据进行操作时，同时将数据表的版本字段取出，等到操作完毕进行提交时，将数据版本号与表内的数据版本号进行比较，如果相等，说明这段时间内没有别的事务对数据表进行操作，则将版本号加一，并予以更新。否则认为是过期数据，进行回滚。