数据库锁就是一种保证数据一致性而使各种共享资源在被并发访问,并发访问人有序所设计的一种规则。
每一种存储引擎的锁机制都是为各自面对的场景所设计,所以各个存储引擎机制也有较大区别。
mysql各个存储引擎使用三类锁机制,行级锁定,页级锁定,表级锁定。
行级锁定
行级锁最大的特点就是锁定对象的颗粒度很小,也是没有各大数据库管理软件实现的最小粒度的,由于粒度小,所以发生的资源竞争概率就很低,能够给与应用程序尽可能大的并发能力,提高应用程序系统整体性能。
但是他也带来了一下弊端,由于锁资源的颗粒很小,所以每次取锁和解锁做的事情很多,自然带来了不小的开销,也会到来死锁
表级锁定
和行级锁相反,表级锁是数据库管理实现做大颗粒度的,他的最大特点是实现逻辑非常简单,带来的系统负面影响小,所以获取锁和解锁很快,由于表一次将整个锁进行锁定,所以很好的避免了死锁的问题,但是也会引起资源竞争,并发度大减折扣。
页级锁定
他是一种独特的锁机制,他的锁颗粒度介于行级锁和表级锁之间,资源开销和并发能力也是在两者之间,另外他也会发生死锁。
总结三种锁的特性
行锁:颗粒小,并发性高,加锁慢,发生资源冲突的概率低,容易引发死锁。
表锁:颗粒大,并发性低,加锁快,发生资源冲突的概率高,不同意引发死锁。
页级索:开销和性能等方面在行锁和表锁之间,也会一起死锁。
表级锁
mysql的表级锁有两种模式
-
共享读锁
-
排他写锁
我们可以使用show status like 'table%'锁的各种信息,如下
mysql> show status like 'table%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Table_locks_immediate | 85 | 产生表级锁定的次数
| Table_locks_waited | 0 | 产生表级锁等待的次数
| Table_open_cache_hits | 15 |
| Table_open_cache_misses | 23 |
| Table_open_cache_overflows | 0 |
+----------------------------+-------+
5 rows in set (0.00 sec)
表级共享读锁
我们准备两个session1 和session2 两个窗口
session1设置表共享读锁,查看数据
mysql> lock table mylock read;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from mylock;
+----+------+
| id | NAME |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
+----+------+
4 rows in set (0.00 sec)
session2 也是可以查看共享读锁表mylock
mysql> select * from mylock;;
+----+------+
| id | NAME |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
+----+------+
4 rows in set (0.00 sec)
session1不可以查看其它表如test_innodb_lock
mysql> select * from test_innodb_lock;
ERROR 1100 (HY000): Table 'test_innodb_lock' was not locked with LOCK TABLES
session2是可以查看test_innodb_lock
mysql> select * from test_innodb_lock;
+------+------+
| a | b |
+------+------+
| 1 | b1 |
| 2 | b2 |
| 4 | b4 |
+------+------+
3 rows in set (0.00 sec)
session1不可以插入和更新共享读锁表mylock,会有提示报错
mysql> insert into mylock values(6,'f');
ERROR 1099 (HY000): Table 'mylock' was locked with a READ lock and can't be updated
mysql> update mylock set name='g' where id=1;
ERROR 1099 (HY000): Table 'mylock' was locked with a READ lock and can't be updated
session1释放锁
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
表级写锁
创建session1和session2
session1:可以进行查询,更新 ,插入,
mysql> lock tables mylock write;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from mylock where id =1;
+----+------+
| id | NAME |
+----+------+
| 1 | a |
+----+------+
1 row in set (0.00 sec)
mysql> update mylock set name ='a1' where id =1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> insert into mylock(name) values ('10');
Query OK, 1 row affected (0.00 sec)
mysql> select * from mylock;
+----+------+
| id | NAME |
+----+------+
| 1 | a1 |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | 10 |
+----+------+
5 rows in set (0.00 sec)
session2:执行查询阻塞,直到session1释放锁
mysql> select * from mylock where id =1;
session1:释放锁
mysql> unlock tables;
Query OK, 0 rows affected (0.01 sec)
Innodb存储引擎锁机制
共享锁和排他锁是行锁,意向锁都是表锁
-
共享锁:允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁。
-
排他锁:允许获得排他锁的事物更新数据,阻止其他事物获得相同数据集的共享锁和排他锁。
-
意向共享锁:事务打算给数据行加行共享锁,事务在给一个数据行加行共享锁前必须先获得该表的IS锁
-
意向排他锁:事务打算给数据行加行排他锁,事物在给一个数据行加排他锁前必须获取该表的IX锁。
意向锁是mysql自动加的,不需要用户去干预,对于insert,delete ,update 语句,innodb会自动给涉及数据集加排他锁,select是不加任何锁。也可以通过下面语句进行手动加锁
共享锁(S):SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。
排他锁(X):SELECT * FROM table_name WHERE ... FOR UPDATE。
select ...in share mode 获得共享锁,主要用于数据依存关系时,确认某行是否存在,并确认没有人对这个记录进行update 和delete操作,但是如果其他事物也需要对该记录进行更新很可能造成死锁,如果锁定某行记录后,需要进行更新操作应用,使用select .for update.
innodb行锁实现方式
InnoDB行锁是通过给索引上的索引项加锁来实现的,因此InnoDB这种行锁实现特点意味着:只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁!
innodb 行锁分为三类
-
Record lock :对索引项加锁
-
Gap lock:对索引项之间的 间隙,第一条记录前的间隙和所行记录后的间隙加锁
-
Next-key lock 前两种的组合,对记录以及前面的间隙加锁。
演示行锁
使用test_innodb_lock表,查看索引情况
mysql> show index from test_innodb_lock\G
*************************** 1. row ***************************
Table: test_innodb_lock
Non_unique: 1
Key_name: tesst_innodb_a_idx
Seq_in_index: 1
Column_name: a
Collation: A
Cardinality: 3
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.00 sec)
1.使用索引时使用行锁的例子
同样创建两个session1 session2
session1查询id=1的数据行
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> update test_innodb_lock set b='b1' where a=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
session2 同样查询id=1的数据行,发生阻塞
mysql> update test_innodb_lock set b='b1' where a=1;
session1,释放锁
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
session2阻塞释放,更新数据
mysql> update test_innodb_lock set b='b1' where a=1;
Query OK, 0 rows affected (20.46 sec)
Rows matched: 1 Changed: 0 Warnings: 0
2.由于mysqlde 行锁是针对索引加的锁,不是针对数据行加的锁,索引虽然访问不同行的记录,但是如果是使用相同的索引键,是会出现锁冲突。
session1,执行更新a=1 and b='b1'
mysql> update test_innodb_lock set b='bb' where a=1 and b='b1';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
session1 执行更新 a=1 and b='b2'阻塞,直到session1释放锁
mysql> update test_innodb_lock set b='bb' where a=1 and b='b2';
3.当我们使用范围条件而不是等于条件检索数据,并请求共享锁和排他锁时,Innodb会把符合条件的数据行的索引项加锁,对于键值在条件范围内但并不存在的记录,叫做间隙,innodb也会对这个间隙加锁,这种锁机制就是Next-key锁。
session1 先查看记录,有哪些数据,在使用a<4 and a>1 更新操作
mysql> select * from test_innodb_lock;
+------+------+
| a | b |
+------+------+
| 1 | b1 |
| 2 | b2 |
| 4 | b4 |
+------+------+
3 rows in set (0.01 sec)
mysql> update test_innodb_lock set b='100' where a<4 and a>0;
Query OK, 2 rows affected (10.56 sec)
Rows matched: 2 Changed: 2 Warnings: 0
session2 插入id=3的数据阻塞,知道session1提交
mysql> insert into test_innodb_lock values(3,'333');
4.死锁的例子
session1 ,执行更新操作,where id=1
mysql> update test_innodb_lock set b ='bb' where a=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
session2 执行更新操作 where id =2
mysql> update test_innodb_lock set b ='bb' where a=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
session1再执行更新操作,where id=2,发现阻塞
mysql> update test_innodb_lock set b ='22' where a=2;
session2 再执行更新操作 where id =1,发现报错提示发生死锁
mysql> update test_innodb_lock set b ='11' where a=1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
5.无索引升级为表锁
session1 更新操作,b列没有索引,因此不会使用索引
mysql> update test_innodb_lock set b='2' where b=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
session2,更新操作,阻塞直到超时,代表他升级为表锁
mysql> update test_innodb_lock set b ='2' where b=2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction