mysql-innodb-锁问题.sql

--myisam innodb 锁机制
-- innodb 支持事务,支持行级锁


--事务(transaction)及其 ACID 属性:

--名词解释
--事务是有一组 sql 组成的逻辑处理单元,事务具有以下4个属性, 通常称为数据的 ACID 属性
原子性(Atomicity) : 事务是一个原子操作单元,其对数据的修改,要么全都执行,那么全都不执行
一致性(Consistent) : 在事务开始和完成时候,数据都必须保持一致状态,这意味着所有相关的数据规则都必须应用于事务的修改,以保证数据的完整性;事务结束时,所有的内部数据结构(如B树索引或者双向链表)也都必须是正确的
隔离性(Isolation) : 数据库系统提过了一定的隔离机制,保证事务在不受外部并发操作影响的 ”独立“ 环境中执行,这意味着事务处理过程中的中间状态对外部是不可见的,反之亦然
持久性(Durable) : 事务完成之后,他对数据的修改是永久性的, 即使出现系统故障也能够保持

银行转账就是事物典型一个例子


--并发事务处理带来的问题,
相对于串行处理来说,并发事务处理能力能大大增加数据库资源的利用率,提高数据库系统的事务吞吐率,从而可以支持更多的用户,但是并发事务处理也会带来一些问题,主要包括以下几个方面
--更新丢失 (lost update): 2个人同时编辑同一个文件,后面执行更新的会覆盖前面的更新,导致前面的更新丢失
--脏读 (Dirty Reads): 一个事务正在对一条记录做修改, 这个事务完成并提交前, 这条记录的数据就处于不一致的状态了; 这时候,另一个事务也来读取同一条记录,如果不加控制,第二个事务读取了这些 ”脏“ 数据,并据此做进一步处理,就会产生未提交的数据依赖关系, 这种现象被形象的称为 ”脏读“
--不可重复读 (Non-Repeatable Reads) : 一个事务在读取某些数据后的某个时间,再次读取以前读过的数据,却发现其读出的数据已经发生了改变或者某些记录已经删除了,
--幻读(Phantom Reads): 一个事务按照相同的查询条件重新读取以前检索过的数据,却发现其他事务插入了满足查询条件的新数据,这种现象就称为 ”幻读“

--事务的隔离级别
’并发事务‘带来的问题,”更新丢失“ 通常是应该完全避免的
”脏读“ ”不可重复读“ ”幻读“ ,其实都是数据库读一致性的问题,必须有数据库提供一定的事务隔离机制来解决,数据库实现事务隔离的方式,基本有2总方式,
--1 一种是在读取数据前,对其加锁,阻止其他事务对数据进行修改
--2 另一种是不加任何锁, 通过一定的机制生成一个数据请求时间点的一致性数据快照(snapshot),并用这个快照来提供一定级别(语句级别 或者 事务级别)的一致性读取,
-- 从用户角度来看,好像是数据库可以提供统一数据,多个版本,因此,这中技术叫做 ”数据多版本并发控制“(MultiVersion Concurrency Control 简称 mvcc 或者 mcc) 也经常称为多版本数据库

--数据库的事务隔离越严格,并发副作用越小, 但付出的代价也就越大, ”因为事务隔离实质是就是使事务在一定程度上串行化“,这显然与 ”并发“ 是矛盾的,
--同时不同的应用对读一致性和事务隔离的程度也不同
--为了解决 ”隔离“ 和 ”并发“ 的矛盾, ISO/ANSI SQL92定义了4个事务隔离级别,

读数据一致性及允许的并发副作用 读数据的一致性 脏读 不可重复读 幻读
隔离级别
未提交读(read uncommited) 最低级别 是 是 是
已提交读(read commited) 语义级别 否 是 是
可重复读(Repeatable read) 事务级别 否 否 是
可序列化(Serializable) 最高级别 事务级别 否 否 否


--获取innodb 行锁争用情况
-- show status like 'innodb_row_lock%'


--innodb的行锁模式和加锁方法
共享锁(S): 允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁
--通俗来讲,一个事务给一行加了共享锁, 允许另一个事务可以在这一行添加共享锁(也就是可以读到数据),但是不允许另一个事务给这一行添加排它锁(S)(也就是做出更新)

排它锁(X):允许获得排它锁的事务更新数据, 阻止其他事务获取相同数据集的共享读锁(S) 和 排他写锁(X)
--通俗来讲,一个事务给一行添加了排它锁,那么只有这个事务可以更新这行数据,其他事务想要在这行上追加共享锁(也就是读取这行),或者排它锁(也就是修改这行)是不允许的

另外,为了允许行锁和表锁共存,实现多粒度机制,INNODB 还有2种内部使用的意向锁, (Intention Locks),这2个意向锁都是表锁 🔐
意向共享锁(IS):事务打算给数据行添加行共享锁,事务在给一个数据行添加共享锁之前必须先获取这个表的意向共享锁(IS)
意向排他锁(IX): 事务打算给数据行添加行排它锁,事务在给一个数据行添加排它锁之前必须先获得这个表的意向排它锁(IX)


各中锁之间的兼容模式
请求锁模式 X IX S IS
是否兼容
当前锁模式

X 冲突 冲突 冲突 冲突
IX 冲突 兼容 冲突 兼容
S 冲突 冲突 兼容 兼容
IS 冲突 兼容 兼容 兼容

--如果一个事务请求的所模式与当前的模式兼容,Innodb 就讲请求的锁,授予该事务;反之,如果二者不兼容,那就该事务就等待锁释放

--意向锁是Innodb 自动添加的,不需要用户干预,对于 update,delete,insert语句, innodb会自动给涉及到的数据添加 意向排它锁;
--对于普通的 select 语句,innodb是不会加任何锁的
--事务可以通过以下语句,显示的给记录添加共享锁 和 排他锁
共享锁(S): select * from table where 1=1 .. lock in share mode;
排它锁 (X) :select * from table where 1=1 .. for update;

--例子
--一个session对一个数据行添加了 (lock in share mode) 👌
--另一个session 可以查询该行记录,并且可以添加共享锁(lock in share mode) ✅
--另一个session 要会更新该行数据,添加排它锁( for update ) ❎

--一个session对一个数据行添加了 (for update) 👌
--另一个session 不可以查询该行记录,不可以添加共享锁(lock in share mode) ❎
--另一个session 要会更新该行数据,不可以添加排它锁( for update ) ❎


--innodb 行锁实现方式
innodb 行锁是通过索引上的索引项加锁实现的, 如果没有索引,innodb将通过隐蔽的聚簇索引来对记录加锁, innodb 行锁分为3种形式

record lock:对索引加锁
gap lock: 对索引之间的“间隙”,第一条记录前的 “间隙” 或者最后一条的记录后的 “间隙”加锁
next-key lock: 前2种的组合,对记录及其前面的间隙加锁

innodb 这种行锁实现特点意味着:如果不通过索引条件检索数据,那么innosb将对表中所有的记录加锁, 实际效果跟表锁一样
--实际开发过程中,要特别注意 innodb 行锁的这一个特性,否则可能会导致大量的锁冲突,从而影响并发性能
--select * from table where id = 1 for update; id记录没加锁,会全表锁定

--由于 mysql 的行锁,是针对索引加的锁, 不是针对记录加的锁,所以 虽然不是访问的同一行记录,但是,如果使用的相同的索引,那么会出现锁冲突的,设计的时候,应该注意下
-- ind idnex(id)
--select * from tabel where id = 1 and name='1' for update; --使用的 id 索引,
--select * from tabel where id = 1 and name='4' for update; --使用的相同过的 id 索引,会出现锁等待


表中有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行,不论是使用主键索引,唯一索引,或者是普通索引, innodb 都会使用行锁来对数据加锁,
--select * from table where id = 1; id 是索引
--数据 id = 1 ,name=1, id = 1,name=4
--select * from tabel where name = 4; name 是索引, 会出现锁等待


--如果索引项 出现了隐形类型转换, 那么 索引不会使用,会全表扫描,会全表索引


--

转: https://github.com/chenshuai1993/php-interview/blob/master/mysql/mysql-innodb-%E9%94%81%E9%97%AE%E9%A2%98.sql

*文章为作者原创或网络转载
转载此文请于文首标明作者姓名,保持文章完整性,并请附上出处
未按照规范转载者,陈帅同学保留追究相应责任的权利
编程改变世界

干了这碗鸡汤