Mysql基础篇--面试锁机制

小土豆Yuki

发布于 2020-06-15 17:32:23

4010

发布于 2020-06-15 17:32:23

文章被收录于专栏：洁癖是一只狗

数据库锁就是一种保证数据一致性而使各种共享资源在被并发访问，并发访问人有序所设计的一种规则。

每一种存储引擎的锁机制都是为各自面对的场景所设计，所以各个存储引擎机制也有较大区别。

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

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2019-12-16，如有侵权请联系 cloudcommunity@tencent.com 删除

云数据库 SQL Server

本文分享自洁癖是一只狗微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

云数据库 SQL Server

登录后参与评论

0 条评论

热度

Mysql基础篇--面试锁机制

Mysql基础篇--面试锁机制

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐