MySQL的锁机制(表锁,行锁,读锁(共享锁),写锁(排它锁),乐观锁,悲观锁,死锁)

[TOC]

表锁

MyISAM支持表锁,不支持行锁

表锁的实现方式:

1.读锁(共享锁),对MyISAM表的读操作,不会阻塞其他用户对同一表的读请求,但会阻塞对同一表的写请求;

2.写锁(排它锁),对 MyISAM表的写操作,则会阻塞其他用户对同一表的读和写操作;

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

读锁的使用

-- 线程一给student表加读锁
lock table student read;
-- 当前线程一可以对student表进行查询,修改,新增,删除
-- 释放锁
 unlock tables;
 
 -- 假设线程二在线程一没释放锁之前进来了,只能进行查询,不能进行更新操作,会一直等待直到线程一释放锁

写锁的使用

-- 线程一给student表加写锁
lock table student write;
-- 当前线程一可以对student表进行查询,修改,新增,删除
-- 释放锁
 unlock tables;
 
 -- 假设线程二在线程一没释放锁之前进来了,查询和更新都会暂停,会一直等待直到线程一释放锁

行锁

InnoDB的行锁

InnoDB行锁的实现是基于索引,如果查询的条件不是索引上的列,则还是加的表锁!

实现方式:

1.读锁(共享锁),SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE;

2.写锁(排它锁),SELECT * FROM table_name WHERE ... FOR UPDATE;

读锁的使用

-- 线程一加读锁
set autocommit = 0;
SELECT * from student WHERE xing = '陈' LOCK IN SHARE MODE;
-- 线程一可以进行增删改查操作
commit;

-- 在线程一提交事务之前线程二进来了,可以进行查询
SELECT * FROM student WHERE xing = '陈' LOCK IN SHARE MODE;
-- 线程二进行写操作会等待,直到线程一释放锁(student.xing是索引列)
UPDATE student SET student.username = 'cxqq' WHERE student.xing = '陈';
-- 线程二假如更新的数据(student.username)不是索引列,也会等待直到获取锁
-- 线程二把student.username修改为索引后,可以正常修改数据不会等待
-- 理解:InnoDB基于索引,如果查询的数据不是索引则加表锁,加表锁就要获取锁,但是锁已经被线程一拿去了,所以会等待直到获得锁,但是如果把字段加上索引后就可以更新数据了,因为加的是行锁,线程二修改的数据和线程一修改的数据没影响,所以就可以修改了
UPDATE student SET student.username = '121212' WHERE student.username = '张三';

写锁的使用

-- 线程一加读锁
set autocommit = 0;
SELECT * from student WHERE xing = '陈' for update;
-- 线程一可以进行增删改查操作
commit;

-- 在线程一提交事务之前线程二进来了,查询会等待,直到获取锁
SELECT * FROM student WHERE xing = '陈' FOR UPDATE;
-- 线程二进行写操作会等待,直到线程一释放锁(student.xing是索引列)
UPDATE student SET student.username = 'cxqq' WHERE student.xing = '陈';
-- 线程二把姓修改为‘张’时,会等待直到线程一释放锁(student.xing是索引列)
UPDATE student SET student.username = 'cxqq' WHERE student.xing = '张';
-- 线程二把姓修改为‘李’时,没有等待直接修改(student.xing是索引列)
UPDATE student SET student.username = 'cxqq' WHERE student.xing = '李';
/* 
为啥出现上面的很奇怪的现象,线程二修改数据时会等待,也会没有等待直接修改成功了?
即使查询默认是行锁,当锁定记录超过一定比例时,行锁会升级成表锁。
*/

-- 线程二假如更新的数据(student.username)不是索引列,也会等待直到获取锁
-- 线程二把student.username修改为索引后,可以正常修改数据不会等待
-- 理解:InnoDB基于索引,如果查询的数据不是索引则加表锁,加表锁就要获取锁,但是锁已经被线程一拿去了,所以会等待直到获得锁,但是如果把字段加上索引后就可以更新数据了,因为加的是行锁,线程二修改的数据和线程一修改的数据没影响,所以就可以修改了
UPDATE student SET student.username = '121212' WHERE student.username = '张三';

悲观锁

悲观的认为并发重,查询和写入都会加锁,实现方式就是上面的读写锁。

乐观锁

mysql本身没有实现乐观锁,我们可以自己借助版本号实现,需要自己添加一个版本号字段,每次修改时版本号自动加1

// 伪代码
// s1 : id = 1, username = lisi, version = 1
Student s1 = studentDao.selectByUserId(1);
// s2 : id = 1, username = lisi, version = 1
Student s2 = studentDao.selectByUserId(1);

// sql:
// update student  
// set username = chenqi , version = version + 1  
// where id = #{id} and version = #{version};  
// 修改之后,id为1的对象变成了 id = 1 , username = chenqi ,version = 2
int result = studentDao.updateByUser(s1);
// 下面的sql会修改失败,因为id=1的对象- version已经变成2了
int result2 = studentDao.updateByUser(s2);

if(1 == result) {
  log.info("修改成功")
}
if(1 == result2) {
  log.info("修改成功")
}
// .......

死锁

set autocommit= 0;
-- 事务一
begin;
UPDATE student set student.username = 'cxxxxq' WHERE student.id = 1;
UPDATE student set student.username = 'cxxxxq' WHERE student.id = 2;
commit;

-- 事务二
begin;
UPDATE student set student.username = 'cxxxxq' WHERE student.id = 2;
UPDATE student set student.username = 'cxxxxq' WHERE student.id = 1;
commit;

/*
事务一和事务二分别执行第一条sql没问题,但是执行第二条sql会出现死锁
事务一执行第二个sql更新时想加锁,结果锁被事务二获取了
事务二执行第二个sql更新时想加锁,结果锁被事务一获取了
之后两个事务互相等待对方释放锁
  数据库实现了各种死锁检测和死锁的超时机制
  InnoDB会自动检测死锁,并使一个事务释放锁并回退,另一个事务获得锁,继续完成事务
  如果出现死锁会报错 Deadlock found when trying to get lock; try restarting transaction
  在涉及表锁的情况下,InnoDB并不能完全自动检测到死锁,这需要通过设置锁等待超时参数 innodb_lock_wait_timeout来解决。需要说明的是,这个参数并不是只用来解决死锁问题,在并发访问比较高的情况下,如果大量事务因无法立即获得所需的锁而挂起,会占用大量计算机资源,造成严重性能问题,甚至拖跨数据库。我们通过设置合适的锁等待超时阈值,可以避免这种情况发生。
通常来说,死锁都是应用设计的问题,通过调整业务流程、数据库对象设计、事务大小,以及访问数据库的SQL语句,绝大部分死锁都可以避免。
*/

参考:

https://www.iteye.com/blog/chenzhou123520-1860954

https://www.iteye.com/blog/chenzhou123520-1863407

https://www.cnblogs.com/jpfss/p/9213189.html

https://www.cnblogs.com/chenqionghe/p/4845693.html

Last modification:June 29th, 2020 at 03:23 pm