最近系统多次因对数据库锁使用不当引起问题,故从基础学习一下mysql锁机制。
基本概念:
共享锁
共享锁的代号是S,是Share的缩写,共享多的锁粒度是行或者元组(多个行),一个事务获取了共享多之后,可以对锁定范围内的数据执行读操作。
排它锁
排它锁的代号是X,是eXclusive的缩写,排他锁的粒度是行或元组,与共享锁相同,一个事务获取了排它锁之后,可以对锁定范围内的数据执行写操作。
例:假设有两个事务t1和t2
如果事务t1获取了一个元组的共享锁,事务t2还可以立即获取这个元组的共享锁,但不能立即获取这个是元组的排它锁,(必须等到t1释放共享锁之后,才能获取这个元组的排它锁)。
如果事务t1获取了一个元组的排它锁,事务t2不能立即获取这个元组的共享锁,也不能立即获取这个元组的排它锁,(必须等到t1释放排它锁之后)。
意向锁
意向锁是一种表锁,锁定的粒度是整张表,分为意向共享锁(IS)和意向排它锁(IX)两类。
意向共享锁表示一个事务“有意“对数据上共享锁。“有意“这两个字表达的意思比较微妙,说的明白点就是指事务想干这个事但还没有真去干。
例:一个事务t1执行了这样一个语句“select * from table lock in share model”,如果这个语句执行成功,就对表table上了一个意向共享锁。lock in share model就是说事务t1在接下来要执行的语句中要获取S锁。
意向排它锁的含义同理可知,上例中获取意向排它锁,可以使用“select * from table for update”。
lock in share model和for update这个两个东西在数据理论中还有个学名交悲观锁,与悲观锁相对的当然还有乐观锁。可见各种锁都是成双成对出现的,关于悲观锁和乐观锁的问题暂且不表。
锁的互斥和相容关系
锁与锁之间的关系,要么相容,要么互斥
锁a和锁b的相容是指:操作同样一组数据时,如果事务t1获取了锁a,另一个事务t2还可以获取锁b。
锁a和锁b的互斥是指:操作同样一组数据时,如果事务t1获取了锁a,另一个事务t2必须在t1释放锁a之后方可获取锁b。
上面的共享锁、排它锁、意向共享锁,意向排它锁相互之间都是有兼容/互斥关系的,可以用一个兼容性矩阵表示(y表示兼容,n表示不兼容):
X | S | IX | IS | |
X | n | n | n | n |
S | n | y | n | y |
IX | n | n | y | y |
IS | n | y | y | y |
兼容矩阵为什么这个样子的?
X和S的相互关系在上文中解释过了,IX和IS的相互关系全是兼容的,这也好理解,因为他们都只是“有意”,还处于YY阶段,没有真干,所以是可以兼容的。剩下的就是X和IX,X和IS,S和IX,S和IS的关系了,我们可以由X和S的关推倒出这四组关系。
简单的说:X和IX的关系=X和X的关系,因为事务在获取IX锁后,接下来就有权利获取X锁。如果X和IX兼容的话,就会出现两个事务都获取了X的情况,这与我们已知的X与X互斥是矛盾的,所以X与IX只能是互斥关系。其余三组同理可知。
MySQL三种锁级别
MySQL有三种锁级别:表级锁(table-level locking),页级锁(page-levellocking),行级锁(row-levellocking)
三种锁的特性可大致归纳如下:
表级锁:开销小,加锁快,锁定粒度大,不会出现死锁,发生锁冲突的概率最高,并发度最低。
行级锁:开销大,加锁慢,锁定粒度最小,会出现死锁,发生锁冲突的概率最低,并发度最高。
页级锁:开销大小、加锁速度、锁定粒度、发生所冲突的概率及并发度都介于表级锁与行级锁之间,会出现死锁。
而MySQL的存储引擎中:MyISAM和MEMORY采用表级锁;BDB采用(默认)页级锁,但也支持表级锁;InnoDB采用(默认)行级锁,但也支持表级锁;
一、MyISAM表锁
MyISAM存储引擎只支持表锁
1、查询表级锁的争用情况
可以通过查询table_locks_waited(表示不能立即获取锁的次数)和table_locks_immediate(表示可以立即获取锁的查询次数)状态变量来分析系统上的表锁定争夺
mysql>show status like"table%";
+———————–+———-+
| Variable_name | Value |
+———————–+———-+
| Table_locks_immediate | 76939364 |
| Table_locks_waited | 305089 |
+———————–+———-+
2 rows in set (0.00 sec)
Table_locks_waited的值比较高,说明存在着较严重的表级锁争用情况。
2、表级锁的锁模式
表级锁有两种模式:表共享读锁(Table Read Lock)和表独占写锁(Table WriteLock)。MyISAM在执行查询语句(SELECT)前,会自动给涉及的所有表加读锁,而在执行(UPDATE,INSERT)前,会自动给涉及的所有表加写锁。
所以对MyISAM表进行操作,会有以下几种情况:
a、对MyISAM表的读操作(加读锁),不会组三其他进程对同一表的读请求,但会阻塞对同一表的写请求。只有当读锁释放后才会执行其他进程的写操作。
b、对MyISAM表的写操作(加写锁),会组三其他进程对同一表的读和写操作,只有当写锁释放后才会执行其他进程的读写操作。
3、并发插入
原则上数据表有一个读锁时,其他进程无法对此表进行写操作,但在一定条件下,MyISAM表也支持查询和插入操作的并发进行。MyISAM有一个系统变量concurrent_insert,专门用以控制其并发插入的行为,其值分别可以为0、1、2
a、当concurrent_insert为0时,不允许并发插入。
b、当concurrent_insert为1时,如果MyISAM表中没有空洞(即表的中间没有被删除的行),MyISAM允许在一个进程读表的同时,另一个进程从表尾插入记录。这个是MyISAM的默认设置。
c、当concurrent_insert为2时,无论MyISAM表中有没有空洞,都允许下表尾并发插入
4、锁调度
由于MySQL认为写请求一般比读请求重要,所以如果读写请求同时进行的话,MySQL将会优先执行写操作。这样MyISAM表在进行大量的写操作时(特别是更新的字段存在索引的情况下),会造成查询操作很难获取读锁,从而导致查询阻塞。
我们可以通过一些设置来调试MyISAM的调度行为:
a、通过指定启动参数low-priority-updates,使MyISAM引擎默认给予读请求以优先的权利。
b、通过执行命令SET LOW_PRIORITY_UPDATES=1,使该链接发出的更新请求有限级降低。
c、通过INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性,降低该语句的优先级。
上面三种方法都是要么更新优先,要么查询优先的方法。这里要说明的是,不要盲目的给MySQL设置为读优先,因为一些需要长时间运行的查询操作,也会使进程“饿死”。只有根据你你的实际情况,来决定设置那种操作优先。这些方法还是没有从根本上同时解决查询和更新的问题。
在一个有大数据量高并发的MySQL里,我们还可以采用另一种策略来进行优化,那就是通过MySQL主从(读写)分离来实现负载均衡,这样可以避免优先哪种操作从而可能导致另一种操作的阻塞,下面将用一个篇幅来说明MySQL的读写分离。