亲宝软件园·资讯

展开

MySQL 表级锁

万里顾一程 人气:0

表级锁

该锁会锁定整张表,它是MySQL中最基本的锁策略,并不依赖于存储引擎(不管你是MySQL的什么存储引擎,对于表锁的策略都是一样的),并且表锁是开销最小的策略(因为粒度比较大)。由于表级锁一次会将整个表锁定,所以可以很好的避免死锁问题。当然,锁的粒度大所带来最大的负面影响就是出现锁资源争用的概率也会最高,导致并发率大打折扣。

1、表级别的S锁,X锁

InnoDB存储引擎

在对某个表执行SELECT、INSERT、DELETE、UPDATE 语句时,InnoDB存储引擎是不会为这个表添加表级别的S锁或者X锁的。

一般情况下,不会使用InnoDB存储引擎提供的表级别的S锁和X锁。只会在一些特殊情况下,比方说崩溃恢复过程中用到。

InnoDB存储引擎下,手动添加表t的S锁或X锁:

lock tables t read  -- S锁
lock tables t write  -- X锁

不过尽量避免在使用InnoDB存储引擎的表上使用LOCK TABLES这样的手动锁表语句,它们并不会提供什么额外的保护,只是会降低并发能力而已。

MyISAM存储引擎

MyISAM 的表级锁有2种模式,分别为:表共享读锁(S锁) 和 表独占写锁(X锁)。

表共享读锁(S锁):当开启事务A 获取表共享读锁, 则其他新开启事务只能读取数据,不能对操作的同张表进行更新或者插入操作,删除操作,

表独占写锁(X锁):当开启事务A 获取独占写锁,则其他新开启的事物 读取,新增,修改,删除 等操作会处于阻塞状态, 只到 事务A 主动释放锁。

MyISAM存储引擎下,手动添加表t的S锁或X锁:

lock tables t read  -- S锁
lock tables t write  -- X锁

可通过 show status like 'tables%'; 命令来 查看 mysql 内部表级锁定的情况:

2、意向锁

意向锁概述

InnoDB支持多粒度锁(multiple granularity locking),它允许行级锁与表级锁共存,而意向锁就是其中的一种表锁。

==意向锁的存在是为了协调行锁和表锁的关系,支持多粒度(表锁与行锁)的锁并存。==

意向锁是一种不与行级锁冲突的表级锁,这一点非常重要。

意向锁分为两种:

select column from table ... lock in share mode; -- 
select column from table ... for mode; -- 

申请意向锁的动作是数据库完成的,就是说,事务A申请一行的行锁的时候,数据库会自动先开始申请表的意向锁,不需要我们程序员使用代码来申请。

意向锁解决的问题

事务A锁住了表中的一行,让这一行只能读,不能写。之后,事务B申请整个表的写锁。

如果事务B申请成功,那么理论上它就能修改表中的任意一行,这与A持有的行锁是冲突的。

数据库需要避免这种冲突,就是说要让B的申请被阻塞,直到A释放了行锁。于是就有了意向锁。

事务B只需检查表上的意向锁,发现表上有意向共享锁IS,说明表中有些行被共享行锁锁住了,因此,事务B申请表的写锁会被阻塞。

在数据表的场景中,如果我们给某一行数据加上了排它锁,数据库会自动给更大一级的空间,比如数据页或数据表加上意向锁,告诉其他人这个数据页或数据表已经有人上过排它锁了,这样当其他人想要获取数据表排它锁的时候,只需要了解是否有人已经获取了这个数据表的意向排他锁即可。

意向锁的并发性

开启一个事务,并给查询记录加上X锁:此时针对查询的记录还加上了一个表级别的共享排它锁(IX)

再开启一个事务,查询不同记录,并给查询记录加上X锁:表级别的 IX共享排它锁加锁成功,因为两次事务加的IX是针对不同的记录的

结论:

3、自增锁(AUTO-INC锁)

自增锁是MySQL一种特殊的锁,如果表中存在自增字段,当向表中插入数据时,MySQL便会自动维护一个表级的自增锁。

在执行插入语句时就在表级别加一个AUTO-INC锁,然后为每条待插入记录的AUTO_INCREMENT修饰的列分配递增的值,在该语句执行结束后,再把AUTO-INC锁释放掉。

一个事务在持有AUTO-INC锁的过程中,其他事务的插入语句都要被阻塞,可以保证一个语句中分配的递增值是连续的。也正因为此,其并发性显然并不高,当我们向一个有AUTO_INCREMENT关键字的主键插入值的时候,每条语句都要对这个表锁进行竞争,这样的并发潜力其实是很低下的。

所以 innodb 引擎通过设置 innodb_autoinc_lock_mode 的值来提供不同的锁定机制,来显著提高sQL语句的可伸缩性和性能。

innodb_autoinc_lock_mode有三个取值:0,1,2

tradition(innodb_autoinc_lock_mode = 0) 模式:==传统==锁定模式

consecutive(innodb_autoinc_lock_mode = 1) 模式:==连续==锁定模式

interleaved(innodb_autoinc_lock_mode = 2) 模式:==交错==锁定模式

4、元数据锁(MDL锁)

在对某个表执行一些诸如ALTER TABLE、DROP TABLE 这类的 DDL 语句时,其他事务对这个表并发执行诸如 SELECT、INSERT、DELETE、UPDATE的语句会发生阻塞。

同理,某个事务中对某个表执行SELECT、INSERT、DELETE、UPDATE语句时,在其他会话中对这个表执行DDL语句也会发生阻塞。

这个过程其实是通过在server层使用一种称之为元数据锁(英文名: Metadata Locks,简称MDL)结构来实现的。

MySQL5.5引入了meta data lock,简称MDL锁,属于表锁范畴。MDL的作用是,保证读写的正确性。比如,如果一个查询正在遍历一个表中的数据,而执行期间另一个线程对这个表结构做变更,增加了一列,那么查询线程拿到的结果跟表结构对不上,肯定是不行的。

因此,当对一个表做增删改查操作的时候,加MDL读锁;当要对表做结构变更操作的时候,加MDL写锁。

==读锁之间不互斥,因此你可以有多个线程同时对一张表增删改查。读锁和写锁之间、写锁和写锁之间是互斥的==,用来保证变更表结构操作的安全性,解决了 DML 和 DDL 操作之间的一致性问题。MDL锁不需要显式使用,在访问一个表的时候会被自动加上。

加载全部内容

相关教程
猜你喜欢
用户评论