mysql 开发进阶篇系列 10 锁问题 (相同索引键值或

会话1

会话2

SELECT @@tx_isolation

@@tx_isolation

REPEATABLE-READ

SELECT @@tx_isolation

@@tx_isolation

REPEATABLE-READ

SET autocommit=0;

SET autocommit=0;

-- 当前会话对不存在的记录加 for update;

SELECT * FROM city WHERE city_id=102 FOR UPDATE;

 

 

如果这里插入的值>=102就会出现阻塞

INSERT INTO city VALUES(200,2,'江门','005')

错误代码: 1205

Lock wait timeout exceeded; try restarting transaction

 

ROLLBACK;

 

 

INSERT INTO city VALUES(200,2,'江门','005')

共 1 行受到影响

概念

  1. 共享锁(S):允许一个事务去读一行,阻止其他事务获得相同的数据集的排他锁。
  2. 排他锁(X):允许获得排他锁的事务更新数据,但是组织其他事务获得相同数据集的共享锁和排他锁。
  3. 对于insert、update、delete,InnoDB会自动给涉及的数据加排他锁(X);对于一般的Select语句,InnoDB不会加任何锁,事务可以通过以下语句给显示加共享锁或排他锁。

3. 创建了索引,但使用的是表锁
  在前面章节说过,创建了索引但不走索引的情况,这种情况下innodb将使用表锁,而不是行锁,因些分析锁冲突时,还需检查sql的执行计划,以确认是否真正使用了索引。

锁的算法

InnoDB存储引擎有三种行锁的算法

  • Record Lock:单行记录上锁
  • Gap Lock:间隙锁,锁定一个范围
  • Next-Key Lock

         当我们用范围条件而不是相等条件检索数据,并请求共享或排它锁时,innodb会给符合条件的已有数据记录的索引项加锁;对于键值在条件范围内但并不存在的记录就叫做"间隙锁"  比如city表数据分布如下:

mysql 锁机制

标签(空格分隔): mysql


美高梅官方网站 1

共享锁

select * from table_name where .....lock in share mode

Note left of 事务1: select * from table_1 where id=1 lock in share mode;
事务1-->事务2: 
Note right of 事务2: select * from table_1 where id=1 lock in share mode;
事务2-->事务1: 
Note left of 事务1: update table_1 set age=10 where id=1;
Note left of 事务1: 事务1更新时发现此行锁被其他事务享用,等待
事务1-->事务2: 
Note right of 事务2: update table_1 set age=12 where id=1;
Note right of 事务2: 事务2更新时发现此行锁被其他事务享用,也等待,导致死锁

 

改进

TODO...

  这就是一个范围条件的检索, innodb不但会对符合条件的101的记录加锁,也会对city_id大于101(虽然记录并不存在)的"间隙"加锁。使用间隙锁的目的是为了防止幻读,以满足相关的隔离级别。关于幻读查看"sql 开发进阶篇系列 6 锁问题(事务与隔离级别介绍)"
很明显,在使用范围条件的检索记录时, 会阻塞符合条件范围内键值的并发插入,往往造成严重的锁等待。在实现业务中尽量使用相等条件来检索数据。还需注意如查使用相等条件检索的数据不存在时,也会加间隙锁。
  为了防止幻读,mysql隔离级别必须是REPEATABLE-READ和Serializable。REPEATABLE-READ也是默认的隔离级别。

一致性锁定读

默认配置下事务的隔离级别为REPEATABLE READ,select操作为非一致性锁定读,但某些情况下需要对数据库读取操作进行加锁保证数据的一致性。select 有两种一致的锁定读:

  • select ... for update
  • select ... lock in share mode

   如果查询使用如下sql
  select * from city where city_id>100 for update;

例子

事务A
select * from table where id='1';
.
select * from table where id='1';
.
select * from table where id='1';

上述例子中事务B update以后事务A第一次select的时候RC级别和RR级别获取的结果都是id=1的那一条数据;第二次select的时候,由于事务B已经提交,RC级别select的结果就是id=3,而RR级别读取的是事务开始时的数据,id=1。

会话1

会话2

SET autocommit=0;

SET autocommit=0;

SELECT * FROM city WHERE city_id=14  FOR UPDATE;

city_id      country_id        cityname CityCode

14     2       深圳         001

 

 

该记录没有被索引,所以可以获得锁

SELECT * FROM city WHERE  CityCode='002' FOR UPDATE;

city_id      country_id        cityname CityCode

15     2       长沙         002

 

由于该记录被会话1锁定,所以需要等待

SELECT * FROM city WHERE  CityCode='001' FOR UPDATE;

等待...

事务隔离级别

隔离级别 脏读 不可重复读 幻读
未提交读(Read uncommitted)
已提交度(Read committed) x
可重复读(Repeatable read) x x
可序列化(Serializable) x x x

 2.使用不同索引键值但是同一行的冲突 

缺点

虽然是insert后就释放锁,不是事务提交后才释放,但是必须等前一个insert的完成才能进行下一次insert,性能较差。

1.使用相同索引键值的冲突

行锁的三种形式

  1. Record lock:锁定一条记录。
  2. Gap lock
  3. Next-key lock

  当表有多个索引时候,不同的事务可以使用不同的索引锁定不同的行,无论什么索引,innodb都会使用行锁来对数据加锁。
  例如city表city_id字段有主键索引,CityCode字段有普通索引:

排他锁

美高梅官方网站,select * from table_name where .....for update

Note left of 事务1: select * from table_1 where id=1 for update;
事务1-->事务2: 
Note right of 事务2: select * from table_1 where id=1 for update;
Note right of 事务2: 等待...
事务2-->事务1: 
Note left of 事务1: update table_1 set age=10 where id=1;
Note left of 事务1: 更新完后释放锁
事务1-->事务2: 
Note right of 事务2: 获得锁后,得到其他事务提交的记录

本文由美高梅官方网站发布于数据统计,转载请注明出处:mysql 开发进阶篇系列 10 锁问题 (相同索引键值或

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。