一、MySQL锁概述
表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低,又分读锁与写锁。
行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高,又分共享锁与排他锁。
页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般
MySQL的锁机制的特点是不同的存储引擎支持不同的锁机制:
MyISAM和MEMORY存储引擎采用的是表级锁(table-level locking);
BDB存储引擎采用的是页面锁(page-level locking),但也支持表级锁;
InnoDB存储引擎既支持行级锁(row-level locking),也支持表级锁,但默认情况下是采用行级锁。
二、表级锁
MyISAM 操作数据都是使用的表锁,更新一条记录就要锁整个表,导致性能较低,并发不高。当然同时它也不会存在死锁问题。但表级锁创建锁的开销小,当需要频繁对大部分数据做 GROUP BY 操作或者需要频繁扫描整个表时,推荐使用表级锁。
MySQL表级锁分为读锁和写锁。
1)读锁与写锁
读锁:
- 申请读锁成功后,所有线程都可对该表进行读操作,但不允许对该表进行写操作,包括当前线程也不允许
- 当锁住了A表之后,就只能对A表进行读操作,对其他表进行读操作会出现错误(tablename was not locked with LOCK TABLES)
LOCK TABLE table_name [ AS alias_name ] READ
# sql
UNLOCK tables
写锁:当一个线程获得对一个表的写锁后,只有持有锁的线程可以对表进行更新操作。其他线程的读、写操作都会等待,直到锁被释放为止
LOCK TABLE table_name [AS alias_name] [ LOW_PRIORITY ] WRITE
# sql
UNLOCK tables
写锁会优先读锁,可通过设置LOW_PRIORITY属性,用来降低该语句的优先级
也可以通过指定启动参数low-priority-updates,使MyISAM引擎默认给予读请求以优先的权利。
还可以给系统参数max_write_lock_count设置一个合适的值,当一个表的读锁达到这个值后,MySQL就暂时将写请求的优先级降低,给读进程一定获得锁的机会。
2)使用场景
MyISAM在执行查询语句(SELECT)前,会自动给涉及的所有表加读锁,在执行更新操作 (UPDATE、DELETE、INSERT等)前,会自动给涉及的表加写锁,这个过程并不需要用户干预,因此,用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。
给MyISAM表显示加锁,一般是为了在一定程度模拟事务操作,实现对某一时间点多个表的一致性读取。例如, 有一个订单表orders,其中记录有各订单的总金额total,同时还有一个订单明细表order_detail,其中记录有各订单每一产品的金额小计 subtotal,假设我们需要检查这两个表的金额合计是否相符,可能就需要执行如下两条SQL:
Select sum(total) from orders;
Select sum(subtotal) from order_detail;
这时,如果不先给两个表加锁,就可能产生错误的结果,因为第一条语句执行过程中,order_detail表可能已经发生了改变。因此,正确的方法应该是:
Lock tables orders read local, order_detail read local;
Select sum(total) from orders;
Select sum(subtotal) from order_detail;
Unlock tables;
上面的例子在LOCK TABLES时加了“local”选项,其作用就是在满足MyISAM表并发插入条件的情况下,允许其他用户在表尾并发插入记录,有关MyISAM表的并发插入问题,在后面还会进一步介绍。
3)查询表级锁排队情况
MyISAM存储引擎的读锁和写锁是互斥的,读写操作是串行的。
可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁排队情况:
mysql> show status like 'table%';
Variable_name | Value
Table_locks_immediate | 2979
Table_locks_waited | 0
2 rows in set (0.00 sec))
如果Table_locks_waited的值比较高,则说明存在着较严重的表级锁争用情况。
4)并发插入(Concurrent Inserts)
上文提到过MyISAM表的读和写是串行的,但在一定条件下,MyISAM表也支持查询和插入操作的并发进行。
MyISAM存储引擎有一个系统变量concurrent_insert,专门用以控制其并发插入的行为,其值分别可以为0、1或2。
- 当concurrent_insert设置为0时,不允许并发插入。
- 当concurrent_insert设置为1时,如果MyISAM表中没有空洞(即表的中间没有被删除的行),MyISAM允许在一个进程读表的同时,另一个进程从表尾插入记录。这也是MySQL的默认设置。
- 当concurrent_insert设置为2时,无论MyISAM表中有没有空洞,都允许在表尾并发插入记录。
可以利用MyISAM存储引擎的并发插入特性,来解决应 用中对同一表查询和插入的锁争用。例如,将concurrent_insert系统变量设为2,总是允许并发插入;同时,通过定期在系统空闲时段执行 OPTIMIZE TABLE语句来整理空间碎片,收回因删除记录而产生的中间空洞。
三、行级锁
InnoDB 与 MyISAM 的最大不同有两点:一是 InnoDB 支持事务;二是 InnoDB 采用了行级锁。
行级锁是Mysql中锁定粒度最细的一种锁,能大大减少数据库操作的冲突,由于其粒度小,加锁的开销最大。
行级锁分为共享锁和排他锁。
注意:行级锁都是基于索引的,如果一条SQL语句用不到索引是不会使用行级锁的,会使用表级锁。
1)共享锁与排他锁
共享锁:对某一资源加共享锁,自身可以读该资源,其他人也可以读该资源(也可以再继续加共享锁,即 共享锁可多个共存),但无法修改。要想修改就必须等所有共享锁都释放完之后。语法为:
select * from table lock in share mode
排他锁:对某一资源加排他锁,自身可以进行增删改查,其他事务不能再在其上加其他的锁。语法为:
select * from table for update
注意这里有一个误区,以为排他锁锁住一行数据后,其他事务就不能读取和修改该行数据,其实不是这样的。排他锁指的是其他事务不能再在其上加其他的锁。
mysql InnoDB引擎默认update,delete,insert都会自动给涉及到的数据加上排他锁,而select语句默认不会加任何锁类型,所以可以直接通过select …from…查询数据,因为普通查询没有任何锁机制。
2)使用场景
1,InnoDB数据库的增删改操作默认都会加排他锁,而查询不会加任何锁。
2,in share mode和for update显式加锁必须在事务中才生效,在事务commit或者rollback或者断开连接后自动解锁。
3,InnoDB事务四个级别中(Repeatable Read和Serializable)2个事务隔离级别是不需要手动加锁的,因为其他会话的事务是无法取得这2种事务中执行的数据的。而InnoDB默认事务基本是Repeatable Read,所以可以理解为不用手动加锁
3)获取InonoD行锁争用情况
可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况:
mysql> show status like 'innodb_row_lock%';
如果发现锁争用比较严重,如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高,还可以通过设置InnoDB Monitors来进一步观察发生锁冲突的表、数据行等,并分析锁争用的原因。
四、死锁
死锁是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去.此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等的进程称为死锁进程.
T1:begin tran
select * from table lock in share mode
update table set column1='hello'
T2:begin tran
select * from table lock in share mode
update table set column1='world'
假设 T1 和 T2 同时达到 select,T1 对 table 加共享锁,T2 也对 table 加共享锁,当 T1 的 select 执行完,准备执行 update 时,根据锁机制,T1 的共享锁需要升级到排他锁才能执行接下来的 update。在升级排他锁前,必须等 table 上的其它共享锁(T2)释放,同理,T2 也在等 T1 的共享锁释放。于是死锁产生了。
解决办法:
遇到死锁可以执行如下的查询语句观察等待的事务:
-- 查看当前的事务
select * from information_schema.innodb_trx;
-- 查看当前锁定的事务
select * from information_schema.innodb_locks;
-- 查看当前等锁的事务
select * from information_schema.innodb_lock_waits;
也可以执行如下的查询将死锁的日志导出:
show engine innodb status
五、乐观锁与悲观锁
乐观锁与悲观锁是一个抽象的概念描述,实际中并没有这种锁的存在
乐观锁
总是假设最好的情况,每次去拿数据的时候都认为别人不会修改,所以不加锁,但是在更新的时候会判断一下在此期间别人有没有去更新这个数据,可以使用版本号机制来实现。乐观锁适用于多读的应用类型,这样可以提高吞吐量。
悲观锁
总是假设最坏的情况,每次去拿数据的时候都认为别人会修改,所以每次在拿数据的时候都会上锁,这样别人想拿这个数据就会阻塞直到它拿到锁。上文说到的表锁、行锁,共享锁(读锁)与排它锁(写锁)等等,都是在做操作之前先上锁,也就是所谓的悲观锁。
版本号机制
一般是在数据表中加上一个数据版本号version字段,表示数据被修改的次数,当数据被修改时,version值会加一。当线程A要更新数据值时,在读取数据的同时也会读取version值,在提交更新时,若刚才读取到的version值为当前数据库中的version值相等时才更新,否则重试更新操作,直到更新成功。
例子:
假设数据库中帐户信息表中有一个 version 字段,当前值为 1 ;而当前帐户余额字段( balance )为 $100 。
1-1:操作员 A 此时将其读出( version=1 ),并从其帐户余额中扣除 $50( $100-$50 )。
1-2:在操作员 A 操作的过程中,操作员B 也读入此用户信息( version=1 ),并从其帐户余额中扣除 $20 ( $100-$20 )。
2-1:操作员 A 完成了修改工作,将数据版本号加一( version=2 ),连同帐户扣除后余额( balance=$50 ),提交至数据库更新,此时由于提交数据版本大于数据库记录当前版本,数据被更新,数据库记录 version 更新为 2 。
2-2:操作员 B 完成了操作,也将版本号加一( version=2 )试图向数据库提交数据( balance=$80 ),但此时比对数据库记录版本时发现,操作员 B 提交的数据版本号为 2 ,数据库记录当前版本也为 2 ,不满足 “ 当前最后更新的version与操作员第一次的版本号相等 “ 的乐观锁策略,因此,操作员 B 的提交被驳回。
这样,就避免了操作员 B 用基于 version=1 的旧数据修改的结果覆盖操作员A 的操作结果的可能。