共享锁与排他锁
MySQL 不同的存储引擎支持不同的锁机制,所有的存储引擎都以自己的方式显现了锁机制,服务器层完全不了解存储引擎中的锁实现:
默认情况下,表锁和行锁都是自动获得的, 不需要额外的命令。但是在有的情况下, 用户需要明确地进行锁表或者进行事务的控制, 以便确保整个事务的完整性,这样就需要使用事务控制和锁定语句来完成。
表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。
这些存储引擎通过总是一次性同时获取所有需要的锁以及总是按相同的顺序获取表锁来避免死锁。
表级锁更适合于以查询为主,并发用户少,只有少量按索引条件更新数据的应用,如Web 应用
行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
最大程度的支持并发,同时也带来了最大的锁开销。
在 InnoDB 中,除单个 SQL 组成的事务外,锁是逐步获得的,这就决定了在 InnoDB 中发生死锁是可能的。
行级锁只在存储引擎层实现,而Mysql服务器层没有实现。 行级锁更适合于有大量按索引条件并发更新少量不同数据,同时又有并发查询的应用,如一些在线事务处理(OLTP)系统
页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。
一、全局锁
全局锁就是对整个数据库实例加锁。MySQL提供了一个加全局读锁的方法,命令是Flush tables with read lock。当需要让整个库处于只读状态的时候,可以使用这个命令,之后其他线程的以下语句会被阻塞:数据更新语句(数据的增删改)、数据定义语句(包括建表、修改表结构等)和更新类事务的提交语句。全局锁的典型使用场景是,做全库逻辑备份。也就是把整库每个表都select出来存成文本
二、表级锁
MySQL里面表级别的锁有两种:一种是表锁,一种是元数据锁(meta data lock,MDL)
表锁的语法是lock tables … read/write。可以用unlock tables主动释放锁,也可以在客户端断开的时候自动释放。lock tables语法除了会限制别的线程的读写外,也限定了本线程接下来的操作对象。如果在某个线程A中执行lock tables t1 read,t2 wirte;这个语句,则其他线程写t1、读写t2的语句都会被阻塞。同时,线程A在执行unlock tables之前,也只能执行读t1、读写t2的操作。连写t1都不允许。
另一类表级的锁是MDL。MDL不需要显式使用,在访问一个表的时候会被自动加上。MDL的作用是,保证读写的正确性。如果一个查询正在遍历一个表中的数据,而执行期间另一个线程对这个表结构做了变更,删了一列,那么查询线程拿到的结果跟表结构对不上,肯定不行。
在MySQL5.5版本引入了MDL,当对一个表做增删改查操作的时候,加MDL读锁;当要对表做结构变更操作的时候,加MDL写锁
读锁之间不互斥,因此可以有多个线程同时对一张表增删改查
读写锁之间、写锁之间是互斥的,用来保证变更表结构操作的安全性。因此,如果有两个线程要同时给一个表加字段,其中一个要等另一个执行完才能开始执行。给一个表加字段,或者修改字段,或者加索引,需要扫描全表的数据。在对大表操作的时候,需要特别小心,以免对线上服务造成影响
三、行锁
MySQL的行锁是在引擎层由各个引擎自己实现的。但不是所有的引擎都支持行锁,比如MyISAM引擎就不支持行锁。行锁就是针对数据表中行记录的锁。比如事务A更新了一行,而这时候事务B也要更新同一行,则必须等事务A的操作完成后才能进行更新
InnoDB在事务执行过程中,使用两阶段锁协议:
随时都可以执行锁定,InnoDB会根据隔离级别在需要的时候自动加锁;
锁只有在执行commit或者rollback的时候才会释放,并且所有的锁都是在同一时刻被释放。
select ... lock in share mode //共享锁 select ... for update //排他锁
select for update:
在执行这个 select 查询语句的时候,会将对应的索引访问条目进行上排他锁(X 锁),也就是说这个语句对应的锁就相当于update带来的效果。
select *** for update 的使用场景:为了让自己查到的数据确保是最新数据,并且查到后的数据只允许自己来修改的时候,需要用到 for update 子句。
select lock in share mode :in share mode 子句的作用就是将查找到的数据加上一个 share 锁,这个就是表示其他的事务只能对这些数据进行简单的select 操作,并不能够进行 DML 操作。select *** lock in share mode 使用场景:为了确保自己查到的数据没有被其他的事务正在修改,也就是说确保查到的数据是最新的数据,并且不允许其他人来修改数据。但是自己不一定能够修改数据,因为有可能其他的事务也对这些数据 使用了 in share mode 的方式上了 S 锁。
性能影响:
select for update 语句,相当于一个 update 语句。在业务繁忙的情况下,如果事务没有及时的commit或者rollback 可能会造成其他事务长时间的等待,从而影响数据库的并发使用效率。
select lock in share mode 语句是一个给查找的数据上一个共享锁(S 锁)的功能,它允许其他的事务也对该数据上S锁,但是不能够允许对该数据进行修改。如果不及时的commit 或者rollback 也可能会造成大量的事务等待。
for update 和 lock in share mode 的区别:
前一个上的是排他锁(X 锁),一旦一个事务获取了这个锁,其他的事务是没法在这些数据上执行 for update ;后一个是共享锁,多个事务可以同时的对相同数据执行 lock in share mode。
当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时,InnoDB会给符合条件的已有数据记录的索引项加锁;对于键值在条件范围内但并不存在的记录,叫做“间隙(GAP)”,InnoDB也会对这个“间隙”加锁,这种锁机制就是所谓的间隙锁(Next-Key锁)。
很显然,在使用范围条件检索并锁定记录时,InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入,这往往会造成严重的锁等待。因此,在实际应用开发中,尤其是并发插入比较多的应用,我们要尽量优化业务逻辑,尽量使用相等条件来访问更新数据,避免使用范围条件。
InnoDB使用间隙锁的目的:
MySQL 通过 BINLOG 录入执行成功的 INSERT、UPDATE、DELETE 等更新数据的 SQL 语句,并由此实现 MySQL 数据库的恢复和主从复制。MySQL 的恢复机制(复制其实就是在 Slave Mysql 不断做基于 BINLOG 的恢复)有以下特点:
一是 MySQL 的恢复是 SQL 语句级的,也就是重新执行 BINLOG 中的 SQL 语句。
二是 MySQL 的 Binlog 是按照事务提交的先后顺序记录的, 恢复也是按这个顺序进行的。
由此可见,MySQL 的恢复机制要求:在一个事务未提交前,其他并发事务不能插入满足其锁定条件的任何记录,也就是不允许出现幻读。
如果出现死锁,可以用 SHOW INNODB STATUS 命令来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息,如引发死锁的 SQL 语句,事务已经获得的锁,正在等待什么锁,以及被回滚的事务等。据此可以分析死锁产生的原因和改进措施。