前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >Mysql基础篇--面试锁机制

Mysql基础篇--面试锁机制

作者头像
小土豆Yuki
发布2020-06-15 17:32:23
4010
发布2020-06-15 17:32:23
举报
文章被收录于专栏:洁癖是一只狗

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

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

mysql各个存储引擎使用三类锁机制,行级锁定,页级锁定,表级锁定。

行级锁定

行级锁最大的特点就是锁定对象的颗粒度很小,也是没有各大数据库管理软件实现的最小粒度的,由于粒度小,所以发生的资源竞争概率就很低,能够给与应用程序尽可能大的并发能力,提高应用程序系统整体性能。

但是他也带来了一下弊端,由于锁资源的颗粒很小,所以每次取锁和解锁做的事情很多,自然带来了不小的开销,也会到来死锁

表级锁定

和行级锁相反,表级锁是数据库管理实现做大颗粒度的,他的最大特点是实现逻辑非常简单,带来的系统负面影响小,所以获取锁和解锁很快,由于表一次将整个锁进行锁定,所以很好的避免了死锁的问题,但是也会引起资源竞争,并发度大减折扣。

页级锁定

他是一种独特的锁机制,他的锁颗粒度介于行级锁和表级锁之间,资源开销和并发能力也是在两者之间,另外他也会发生死锁。

总结三种锁的特性

行锁:颗粒小,并发性高,加锁慢,发生资源冲突的概率低,容易引发死锁

表锁:颗粒大,并发性低,加锁快,发生资源冲突的概率高,不同意引发死锁

页级索:开销和性能等方面在行锁和表锁之间,也会一起死锁

表级锁

mysql的表级锁有两种模式

  • 共享读锁
  • 排他写锁

我们可以使用show status like 'table%'锁的各种信息,如下

代码语言:javascript
复制
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设置表共享读锁,查看数据

代码语言:javascript
复制
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

代码语言:javascript
复制
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

代码语言:javascript
复制
mysql> select  *  from test_innodb_lock;
ERROR 1100 (HY000): Table 'test_innodb_lock' was not locked with LOCK TABLES

session2是可以查看test_innodb_lock

代码语言:javascript
复制
mysql> select  *  from test_innodb_lock;
+------+------+
| a    | b    |
+------+------+
|    1 | b1   |
|    2 | b2   |
|    4 | b4   |
+------+------+
3 rows in set (0.00 sec)

session1不可以插入和更新共享读锁表mylock,会有提示报错

代码语言:javascript
复制
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释放锁

代码语言:javascript
复制
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

表级写锁

创建session1和session2

session1:可以进行查询,更新 ,插入,

代码语言:javascript
复制
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释放锁

代码语言:javascript
复制
mysql> select  *  from mylock where id =1;

session1:释放锁

代码语言:javascript
复制
mysql> unlock tables;
Query OK, 0 rows affected (0.01 sec)

Innodb存储引擎锁机制

共享锁和排他锁是行锁,意向锁都是表锁

  • 共享锁:允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁。
  • 排他锁:允许获得排他锁的事物更新数据,阻止其他事物获得相同数据集的共享锁和排他锁。
  • 意向共享锁:事务打算给数据行加行共享锁,事务在给一个数据行加行共享锁前必须先获得该表的IS锁
  • 意向排他锁:事务打算给数据行加行排他锁,事物在给一个数据行加排他锁前必须获取该表的IX锁。

意向锁是mysql自动加的,不需要用户去干预,对于insert,delete ,update 语句,innodb会自动给涉及数据集加排他锁,select是不加任何锁。也可以通过下面语句进行手动加锁

代码语言:javascript
复制
共享锁(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 行锁分为三类

  1. Record lock :对索引项加锁
  2. Gap lock:对索引项之间的 间隙,第一条记录前的间隙和所行记录后的间隙加锁
  3. Next-key lock 前两种的组合,对记录以及前面的间隙加锁。

演示行锁

使用test_innodb_lock表,查看索引情况

代码语言:javascript
复制
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的数据行

代码语言:javascript
复制
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的数据行,发生阻塞

代码语言:javascript
复制
mysql> update test_innodb_lock set b='b1' where a=1;

session1,释放锁

代码语言:javascript
复制
mysql> commit;
Query OK, 0 rows affected (0.01 sec)

session2阻塞释放,更新数据

代码语言:javascript
复制
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'

代码语言:javascript
复制
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释放锁

代码语言:javascript
复制
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 更新操作

代码语言:javascript
复制
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提交

代码语言:javascript
复制
mysql> insert into test_innodb_lock values(3,'333');

4.死锁的例子

session1 ,执行更新操作,where id=1

代码语言:javascript
复制
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

代码语言:javascript
复制
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,发现阻塞

代码语言:javascript
复制
mysql> update test_innodb_lock set b ='22' where a=2;

session2 再执行更新操作 where id =1,发现报错提示发生死锁

代码语言:javascript
复制
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列没有索引,因此不会使用索引

代码语言:javascript
复制
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,更新操作,阻塞直到超时,代表他升级为表锁

代码语言:javascript
复制
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 删除

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

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

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

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档