mysql锁机制分为表级锁和行级锁还有页级锁,其中还有乐观锁和悲观锁、共享锁和排他锁。
myisam 和 memory 存储引擎采用的是 表级锁; innodb 存储引擎既支持行级锁,也支持表级锁,但默认情况下采用行级锁。
共享锁又称为读锁,简称S锁,顾名思义,共享锁就是多个事务对于同一数据可以共享一把锁,都能访问到数据,但是只能读不能修改。
排他锁又称为写锁,简称X锁,顾名思义,排他锁就是不能与其他所并存,如一个事务获取了一个数据行的排他锁,其他事务就不能再获取该行的其他锁,包括共享锁和排他锁,但是获取排他锁的事务是可以对数据就行读取和修改。
注意:排他锁指的是一个事务在一行数据加上排他锁后,其他事务不能再在其上加其他的锁。
mysql InnoDB引擎默认的update,delete,insert都会自动给涉及到的数据加上排他锁,而select语句默认不会加任何锁类型。如果要加排他锁可以使用 select ... for update 语句,加共享锁可以使用 select ... lock in share mode 语句。
这里要注意一点:普通查询没有任何锁机制(即 select...from...where...),不与排他锁互斥,不管是加了共享锁还是排他锁,普通查询都可以查到数据,只不过查到的数据有可能是旧数据。
死锁
数据库和操作系统一样,是一个多用户使用的共享资源,当多个用户并发地存取数据时,在数据库中就会产生多个事务同时存取同一数据的情况。当两个事务需要一组有冲突的锁,而不能将事务继续下去的话,就会出现死锁。
死锁的第一种情况
步骤 | 用户A | 用户B |
---|---|---|
1 | begin; | begin; |
2 | select * from tb_A where id=1 for update; | |
3 | select * from tb_B where id=1 for update; | |
4 | select * from tb_B where id=1 for update; 或 select * from tb_B where id=1 lock in share mode; | |
5 | select * from tb_A where id=1 for update; 或 select * from tb_B where id=1 lock in share mode; |
分析:
用户A访问表A并锁住表A的数据(排他锁),然后用户B访问表B并锁住表B的数据(排他锁);这时用户A想要访问表B并加锁,用户B想要访问表A并加锁。这时由于用户B已经锁住表B,用户A必须等待用户B释放表B的锁才能继续,同样用户A已经锁住表A,用户B要等用户A释放表A才能继续,这就死锁就产生了。
解决方法:
仔细分析程序的逻辑,对于数据库的多表操作时,尽量按照相同的顺序进行处理,尽量避免同时锁定两个资源,如操作A和B两张表时,总是按先A后B的顺序处理, 必须同时锁定两个资源,要保证在任何时刻都应该按照相同的顺序来锁定资源。
死锁的第二种情况
步骤 | 用户A | 用户B |
---|---|---|
1 | begin; | begin; |
2 | select * from tb_A where id=1 for update; | |
3 | update tb_A set user_id=2 where id=2; | |
4 | update tb_A set user_id=1 where id=2; | |
5 | update tb_A set user_id=2 where id=1; |
步骤 | 用户A | 用户B |
---|---|---|
1 | begin; | begin; |
2 | select * from tb_A where id=1 lock in share mode; | |
3 | select * from tb_A where id=1 lock in share mode; | |
4 | update tb_A set user_id=1 where id=1; | |
5 | update tb_A set user_id=1 where id=1; |
分析:
用户A查询表A中id为1的数据并加锁(共享锁或排他锁),用户B更改表A中id为2的数据(排他锁);然后用户A想要更改表A中id为2的数据,同时用户B想要更改表A中id为1的数据。这时用户A由于用户B对id为2的数据加锁而无法获得锁,用户B由于用户A对id为1的数据加锁而无法获得锁,于是出现了死锁。
用户A查询表A中id为1的数据并加锁(共享锁),用户B查询表A中id为1的数据并加锁(共享锁);然后用户A想要更改表A中id为1的数据,同时用户B也想更改表A中id为1的数据。用户A的事务里锁的性质由查询的共享锁企图上升到独占锁,而用户B也是由查询的共享锁企图上升为独占锁,由于A 有共享锁存在所以B必须等A释放掉共享锁,同样B有共享锁存在所以A必须等B释放掉共享锁,因此A和B互相等待对方释放锁,于是出现了死锁。
如在某项目中,页面上的按钮点击后,没有使按钮立刻失效,使得用户会多次快速点击同一按钮,这样同一段代码对数据库同一条记录进行多次操 作,很容易就出现这种死锁的情况。
解决方法:
1、对于按钮等控件,点击后使其立刻失效,不让用户重复点击,避免对同时对同一条记录操作。
2、使用乐观锁进行控制。乐观锁是基于数据版本(Version)记录机制实现。通过为数据库表增加一个“version”字段,读取出数据时,将此版本号一同读出,之后更新时,对此版本号+1,将提交数据的版本号与数据库表中对应记录的当前版本号进行比对,如果提交的版本号大于数据库表当前的版本号,则予以更新,否则认为是过期数据。乐观锁机制避免了长事务中数据库加锁的开销(因为用户A和用户B操作过程中,都没有必要对数据库数据加锁了,只需比对版本号),大大提升了大并发量下的系统整体性能表现。
3、使用悲观锁进行控制。悲观锁大多数情况下依靠数据库的锁机制实现,如 select ... for update 语句,保证操作最大程度的独占性。但对长事务而言,数据库性能的开销会增大,如果面对成百上千个并发,这样的情况是不被允许的。所以,采用悲观锁进行控制时一定要考虑清楚。
死锁的第三种情况
如果在事务中执行了一条不满足条件的update语句,则执行全表扫描,如 update tb_A set user_id=1; ,这条update语句会将行级锁上升为表级锁,多个这样的事务执行后,就很容易产生死锁和阻塞。当表中的数据量非常庞大并且索引过少或不合适的时候,数据库经常发生全表扫描,最终应用系统会越来越慢,最终发生阻塞或死锁。
解决方法:
SQL语句中不要使用太复杂的关联多表的查询;使用“explain”对SQL语句进行分析,对于有全表扫描的SQL语句,建立相应的索引进行优化。
解除死锁的方法
# 第一种:
# 1.查询是否有表死锁
show open tables where in_use > 0;
# 2.查询进程(如果您有super权限,您可以看到所有线程。否则,您只能看到您自己的线程)
show processlist;
# 3.杀死线程id
kill id;
# 第二种:
# 1.查看下在锁的事务
select * from information_schema.innodb_trx;
# 2.杀死进程id
kill 进程id;
# 例子:
# 查出死锁进程: show processlist;
# 杀掉进程: kill 420821;
# 其它关于查看死锁的命令:
# 1:查看当前的事务
select * from information_schema.innodb_trx;
# 2:查看当前锁定的事务
select * from information_schema.innodb_locks;
# 3:查看当前等锁的事务
select * from information_schema.innodb_lock_waits;