MySQL程序员Java学习笔记

MySQL的锁

2018-03-09  本文已影响280人  紫霞等了至尊宝五百年

1 MySql的三种锁

1.1 表锁

1.2行锁

1.3页锁

1.4 引擎与锁

//查询表锁争用情况
检查`table_locks_waited`和`table_locks_immediate`状态变量来分析
show status like 'table%'
//table_locks_waited 的值越高，则说明存在严重的表级锁的争用情况

2 表锁的锁模式

是否兼容 请求none 请求读锁 请求写锁
当前处于读锁 是 是 否
当前处于写锁 是 否 否

对MyISAM表的读操作，不会阻塞其他用户对同一张表的读，但会阻塞对同一张表的写

MyISAM

这个过程不需要用户干预，因此不需要用户直接显式用lock table命令

对于给MyISAM显式加锁，一般是为了在一定程度上模拟事务操作，实现对某一个时间点多个表一致性读取

2.1实例

订单表-orders
记录各订单的总金额total

订单明细表-order_detail
记录各订单每一产品的金额小计subtotal

假设我们需要检查这两个表的金额合计是否相符

select sum(total) from orders;
select sum(subtotal) from order_tail;

如果不给表加锁，可能出现错误，在第一条执行的过程，第二张表发生了改表，正确的方法

lock tables orders read local,order_detail read local;
select sum(total) from orders;
select sum(subtotal) from order_tail;
unlock  tables

2.2 注意点

在用lock tables显式加表锁时,必须同时取得所有涉及的表的锁,并且MySQL支持锁升级
即在执行lock tables后,只能访问显式加锁的这些表,不能访问未加锁的表

加的是读锁,就只能查询,不能更新

session1 session2
获得表film_textd 写锁 lock table film_text read;
可select * from film_text 可select * from film_text
不能查询没有锁定的表 :select * from film 可查询/更新未锁定的表: select * from film
插入或更新锁定表会提示错误 update...from film_text 更新锁定表会等待 update...from film_text
释放锁 unlock tables 等待
获得锁，更新成功

在自动加锁的情况下也如此,MySQL会一次获得SQL语句所需要的全部锁
所以MyISAM的表不会死锁

session1 session2
获得表film_textd 写锁 lock table film_text read;
可select * from film_text 可select * from film_text
不能查询没有锁定的表 :select * from film 可查询/更新未锁定的表: select * from film
插入或更新锁定表会提示错误 update...from film_text 更新锁定表会等待 update...from film_text
释放锁 unlock tables 等待
获得锁，更新成功

2.3 tips

当使用lock tables时,不仅需要一次锁定用到的所有表
且同一表在SQL语句中出现多少次,就要通过与SQL语句中别名锁多少次

lock table actor read

会提示错误

select a.first_name.....

需要对别名分别锁定

lock table actor as a read,actor as b read;

3 MyISAM的并发锁

在一定条件下，MyISAM也支持并发插入和读取

可以利用MyISAM的并发插入特性，来解决应用中对同表查询和插入的锁争用
例如，将concurrent_insert系统变量为2，总是允许并发插入

删除操作不会重整整个表,只是把行标记为删除,在表中留下"空洞",MyISAM倾向于在可能时填满这些空洞,插入时就会重用 这些空间,无空洞则把新行插到表尾

3.1 MyISAM的锁调度

MyISAM的读和写锁互斥,读操作串行的

一个进程请求某个MyISAM表的读锁,同时另一个进程也请求同表的写锁，MySQL如何处理呢？
答案是写进程先获得锁。不仅如此，即使读进程先请求先到锁等待队列，写请求后到，写锁也会插到读请求之前！
这是因为MySQL认为写请求一般比读请求重要
这也正是MyISAM表不适合有大量更新和查询操作应用的原因
因为，大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞

幸好我们可以通过一些设置来调节MyISAM的调度行为

另外，MySQL也提供了一种折中的办法来调节读写冲突，即给系统参数max_write_lock_count设置一个合适的值，当一个表的读锁达到这个值后，MySQL便暂时将写请求的优先级降低，给读进程一定获得锁的机会

4 InnoDB锁问题

与MyISAM最大不同

行锁和表锁本来就有许多不同之处，另外，事务的引入也带来了一些新问题

4.1 事务及其ACID

事务是由一组SQL语句组成的逻辑处理单元，事务具有ACID属性

4.2 事务带来的问题

相对于串行处理来说，并发事务处理能大大增加数据库资源的利用率，提高数据库系统的事务吞吐量，从而可以支持可以支持更多的用户
但并发事务处理也会带来一些问题，主要包括以下几种情况

4.3 事务隔离级别

  1. 在并发事务处理带来的问题中，“更新丢失”通常应该是完全避免的。但防止更新丢失，并不能单靠数据库事务控制器来解决，需要应用程序对要更新的数据加必要的锁来解决，因此，防止更新丢失应该是应用的责任。
  2. “脏读”、“不可重复读”和“幻读”，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。数据库实现事务隔离的方式，基本可以分为以下两种。

数据库的事务隔离级别越严格，并发副作用越小，但付出的代价也越大
因为事务隔离实质上就是使事务在一定程度上“串行化”进行，这显然与“并发”矛盾，
不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对“不可重复读”和“幻读”并不敏感，可能更关心数据并发访问的能力

为了解决“隔离”与“并发”的矛盾，ANSI SQL定义了４种隔离级别

隔离级别/读数据一致性及允许的并发副作用 读数据一致性 脏读 不可重复读 幻读
未提交读（Read uncommitted） 最低级别，只能保证不读取物理上损坏的数据 是 是 是
已提交度（Read committed） 语句级 否 是 是
可重复读（Repeatable read） 事务级 否 否 是
可序列化（Serializable） 最高级别，事务级 否 否 否 
//查看Innodb行锁争用情况
show status like 'innodb_row_lock%'
//如果发现争用比较严重，如Innodb_row_lock_waits和Innodb_row_lock_time_avg的值比较高
//通过查询information_schema相关表来查看锁情况
select * from innodb_locks
select * from innodb_locks_waits
//或者通过设置Innodb monitors来进一步观察发生锁冲突的表，数据行等，并分析锁争用的原因
show ENGINE innodb status
//停止监视器
drop table innodb_monitor;
//默认情况每15秒回向日志中记录监控的内容，如果长时间打开会导致.err文件变得非常巨大，所以确认原因后，要删除监控表关闭监视器，或者通过使用--console选项来启动服务器以关闭写日志功能

4.4 InnoDB的行锁

InnoDB支持以下两种类型的行锁

另外，为了允许行/表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁

当前锁/是否兼容/请求锁 X IX S IS
X 冲突 冲突 冲突 冲突
IX 冲突 兼容 冲突 兼容
S 冲突 冲突 兼容 兼容
IS 冲突 兼容 兼容 兼容

如果一个事务的请求锁与当前锁兼容，InnoDB就请求的锁授予该事务
如果不兼容，该事务就要等待锁释放

对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集排他锁（X）
对于普通SELECT语句，InnoDB不会任何锁

可以通过以下语句显示地给记录加读/写锁

用select * from table_name where ... lock in share mode获得共享锁，主要用在需要数据依存关系时确认某行记录是否存在，并确保没有人对这个记录UPDATE或DELETE
但如果当前事务也需要对该记录进行更新，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用select * from table_name where ... FOR UPDATE方式获取排他锁

4.5 实例

4.5.1 Innodb共享锁

session_1 session_2
set autocommit=0,select * from actor where id =1 set autocommit=0,select * from actor where id =1
当前seesion对id为1的记录加入共享锁 select * from actor where id =1 lock in share mode
其他seesion仍然可以查询，并对该记录加入 select * from actor where id =1 lock in share mode
当前session对锁定的记录进行更新，等待锁 update。。。where id=1
当前session对锁定记录进行更新，则会导致死锁退出 update。。。where id=1
获得锁，更新成功

4.5.2 Innodb排他锁

session_1 session_2
set autocommit=0,select * from actor where id =1 set autocommit=0,select * from actor where id =1
当前seesion对id为1的记录加入for update 共享锁 select * from actor where id =1 for update
可查询该记录select *from actor where id =1,但是不能再记录共享锁，会等待获得锁select *from actor where id =1 for update
更新后释放锁 update。。。 commit
其他session，获得所，得到其他seesion提交的记录

4.6 Innodb行锁实现

InnoDb行锁是通过给索引上的索引项加锁来实现
如果没有索引，InnoDB将通过隐藏的聚簇索引来对记录加锁

InnoDb的行锁，实现特点意味着：
如果不通过索引条件检索数据，那么Innodb将对表的所有记录加锁，和表锁一样

间隙锁（Next-Key锁）

SELECT * FROM emp WHERE empid > 100 FOR UPDATE
//    是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。

InnoDB使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求，对于上面的例子，要是不使用间隙锁，如果其他事务插入了empid大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；另一方面，是为了满足其恢复和复制的需要。很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。

4.7 什么时候使用表锁

对于InnoDB,在绝大部分情况下都应该使用行锁
因为事务和行锁往往是我们之所以选择InnoDB的理由

但在个别特殊事务中,也可以考虑使用表锁

当然，应用中这两种事务不能太多，否则，就应该考虑使用ＭyISAＭ

在InnoDB下 ，使用表锁要注意以下两点

// 如果需要写表t1并从表t读
SET AUTOCOMMIT=0;
LOCAK TABLES t1 WRITE, t2 READ, ...;
[do something with tables t1 and here];
COMMIT;
UNLOCK TABLES;

5 关于死锁

ＭyISAM表锁是deadlock free的，这是因为ＭyISAM总是一次性获得所需的全部锁，要么全部满足，要么等待，因此不会出现死锁

但在InnoDB中，除单个SQL组成的事务外，锁是逐步获得的，这就决定了InnoDB发生死锁是可能的

发生死锁后，InnoDB一般都能自动检测到，并使一个事务释放锁并退回，另一个事务获得锁，继续完成事务

但在涉及外部锁，或涉及锁的情况下，InnoDB并不能完全自动检测到死锁
这需要通过设置锁等待超时参数innodb_lock_wait_timeout来解决
需要说明的是，这个参数并不是只用来解决死锁问题，在并发访问比较高的情况下，如果大量事务因无法立即获取所需的锁而挂起，会占用大量计算机资源，造成严重性能问题，甚至拖垮数据库
我们通过设置合适的锁等待超时阈值，可以避免这种情况发生。

通常来说，死锁都是应用设计的问题，通过调整业务流程、数据库对象设计、事务大小、以及访问数据库的SQL语句，绝大部分都可以避免
下面就通过实例来介绍几种死锁的常用方法。

如果出现死锁，可以用SHOW INNODB STATUS命令来确定最后一个死锁产生的原因和改进措施。

6 总结

6.1 对于MyISAM的表锁

6.2 对于InnoDB表

7 索引与锁

在了解InnoDB的锁特性后，用户可以通过设计和SQL调整等措施减少锁冲突和死锁

上一篇 下一篇

猜你喜欢

热点阅读