一、数据库锁理论概述
锁是计算机协调多个进程或线程并发访问某一资源的机制。
在数据库中,除传统计算资源外(如CPU,RAM,I/O等)的争用外,数据也是一种供许多用户共享的资源,如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题,锁冲突也是影响数据库并发访问性能的一个重要因素,从这个角度来说,锁对数据库而言显得尤为重要,也更加复杂。
1、锁的分类
1.1 从对数据操作的类型来分
- 读锁
- 读锁(共享锁):针对同一份数据,多个读操作可以同时进行而不会互相影响。
写锁
行锁(偏写)
- 表锁(偏读)
- 偏向MyISAM存储引擎,开销小,加锁快;无死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。
-
二、读锁
1、关于锁的语句
1.1 手动增加表锁语句
lock table 表名 read(write) , 表名2 read(write),其他。
read 是加读锁,write 是加写锁。
1.2 查看哪些表被加锁
SHOW OPEN TABLES;
1.3 解锁
UNLOCK TABLES;
2、读锁案例
1、打开两个会话,SESSION1为mylock表添加读锁。
为mylock表添加读锁
LOCK TABLE
mylock
READ;
2、打开两个会话,SESSION1是否可以读自己锁的表?是否可以修改自己锁的表?是否可以读其他的表?那么SESSION2呢?SESSION1
问题1:SESSION1为mylock表加了读锁,可以读mylock表!
mysql> SELECT * FROM
mylock
;
+——+—————+
| id | name |
+——+—————+
| 1 | ZhangSan |
| 2 | LiSi |
| 3 | WangWu |
| 4 | ZhaoLiu |
+——+—————+
4 rows in set (0.00 sec)问题2:SESSION1为mylock表加了读锁,不可以修改mylock表!
mysql> UPDATE
mylock
SETname
= ‘abc’ WHEREid
= 1;
ERROR 1099 (HY000): Table ‘mylock’ was locked with a READ lock and can’t be updated问题3:SESSION1为mylock表加了读锁,不可以读其他的表!
mysql> SELECT * FROM
book
;
ERROR 1100 (HY000): Table ‘book’ was not locked with LOCK TABLESSESSION2
问题1:SESSION1为mylock表加了读锁,SESSION2可以读mylock表!
mysql> SELECT * FROM
mylock
;
+——+—————+
| id | name |
+——+—————+
| 1 | ZhangSan |
| 2 | LiSi |
| 3 | WangWu |
| 4 | ZhaoLiu |
+——+—————+
4 rows in set (0.00 sec)问题2:SESSION1为mylock表加了读锁,SESSION2修改mylock表会被阻塞,需要等待SESSION1释放mylock表!
mysql> UPDATE
mylock
SETname
= ‘abc’ WHEREid
= 1;
C — query aborted
ERROR 1317 (70100): Query execution was interrupted问题3:SESSION1为mylock表加了读锁,SESSION2可以读其他表!
mysql> SELECT * FROM
book
;
+————+———+
| bookid | card |
+————+———+
| 1 | 1 |
| 7 | 4 |
| 8 | 4 |
| 9 | 5 |
| 5 | 6 |
| 17 | 6 |
| 15 | 8 |
+————+———+
24 rows in set (0.00 sec)3、写锁案例
1、打开两个会话,SESSION1为mylock表添加写锁。
为mylock表添加写锁
LOCK TABLE
mylock
WRITE;
2、打开两个会话,SESSION1是否可以读自己锁的表?是否可以修改自己锁的表?是否可以读其他的表?那么SESSION2呢?SESSION1
问题1:SESSION1为mylock表加了写锁,可以读mylock的表!
mysql> SELECT * FROM
mylock
;
+——+—————+
| id | name |
+——+—————+
| 1 | ZhangSan |
| 2 | LiSi |
| 3 | WangWu |
| 4 | ZhaoLiu |
+——+—————+
4 rows in set (0.00 sec)问题2:SESSION1为mylock表加了写锁,可以修改mylock表!
mysql> UPDATE
mylock
SETname
= ‘abc’ WHEREid
= 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0问题3:SESSION1为mylock表加了写锁,不能读其他表!
mysql> SELECT * FROM
book
;
ERROR 1100 (HY000): Table ‘book’ was not locked with LOCK TABLESSESSION2
问题1:SESSION1为mylock表加了写锁,SESSION2读mylock表会阻塞,等待SESSION1释放!
mysql> SELECT * FROM
mylock
;
C — query aborted
ERROR 1317 (70100): Query execution was interrupted问题2:SESSION1为mylock表加了写锁,SESSION2读mylock表会阻塞,等待SESSION1释放!
mysql> UPDATE
mylock
SETname
= ‘abc’ WHEREid
= 1;
C — query aborted
ERROR 1317 (70100): Query execution was interrupted问题3:SESSION1为mylock表加了写锁,SESSION2可以读其他表!
mysql> SELECT * FROM
book
;
+————+———+
| bookid | card |
+————+———+
| 1 | 1 |
| 7 | 4 |
| 8 | 4 |
| 9 | 5 |
| 5 | 6 |
| 17 | 6 |
| 15 | 8 |
+————+———+
24 rows in set (0.00 sec)4、案例结论
MyISAM引擎在执行查询语句SELECT之前,会自动给涉及到的所有表加读锁,在执行增删改之前,会自动给涉及的表加写锁。
MySQL的表级锁有两种模式:
表共享读锁(Table Read Lock)。
表独占写锁(Table Write Lock)。
対MyISAM表进行操作,会有以下情况:
対MyISAM表的读操作(加读锁),不会阻塞其他线程対同一表的读操作,但是会阻塞其他线程対同一表的写操作。只有当读锁释放之后,才会执行其他线程的写操作。
対MyISAM表的写操作(加写锁),会阻塞其他线程対同一表的读和写操作,只有当写锁释放之后,才会执行其他线程的读写操作。
简而言之,就是读锁会阻塞写,但是不会阻塞读,而写锁会同时阻塞写和读5、表锁分析
mysql> SHOW STATUS LIKE ‘table%’;
+——————————————+———-+
| Variable_name | Value |
+——————————————+———-+
| Table_locks_immediate | 173 |
| Table_locks_waited | 0 |
| Table_open_cache_hits | 5 |
| Table_open_cache_misses | 8 |
| Table_open_cache_overflows | 0 |
+——————————————+———-+
5 rows in set (0.00 sec) 可以通过Table_locks_immediate和Table_locks_waited状态变量来分析系统上的表锁定。具体说明如下:
- Table_locks_immediate:产生表级锁定的次数,表示可以立即获取锁的查询次数,每立即获取锁值加1。
- Table_locks_waited:出现表级锁定争用而发生等待的次数(不能立即获取锁的次数,每等待一次锁值加1),此值高则说明存在较严重的表级锁争用情况。
此外,MyISAM的读写锁调度是写优先,这也是MyISAM不适合作为主表的引擎。因为写锁后,其他线程不能进行任何操作,大量的写操作会使查询很难得到锁,从而造成永远阻塞。
三、行锁
1、行锁特点
偏向
InnoDB
存储引擎,开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度最高。InnoDB
存储引擎和MyISAM
存储引擎最大不同有两点:一是支持事务,二是采用行锁。
事务的ACID:
Atomicity [ˌætəˈmɪsəti]
。Consistency [kənˈsɪstənsi]
。Isolation [ˌaɪsəˈleɪʃn]
。Durability [ˌdjʊərəˈbɪlɪti]
。四、索引失效行锁变表锁
五、间隙锁的危害
1.什么是间隙锁?
当我们用范围条件而不是相等条件检索数据,并请求共享或者排他锁时,InnoDB
会给符合条件的已有数据记录的索引项加锁,对于键值在条件范文内但并不存在的记录,叫做”间隙(GAP)”。InnoDB
也会对这个”间隙”加锁,这种锁的机制就是所谓的”间隙锁”。2.间隙锁的危害
因为Query
执行过程中通过范围查找的话,他会锁定整个范围内所有的索引键值,即使这个键值不存在。
间隙锁有一个比较致命的缺点,就是当锁定一个范围的键值后,即使某些不存在的键值也会被无辜的锁定,而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会対性能造成很大的危害。六、如何锁定一行