Raven's Wiki

清风皓月任我飞~~~

0%

mysql-lock

发表于 更新于 分类于 
1
show status like 'table%';

MySQL各存储引擎使用了三种类型 (级别) 的锁定机制: 表级锁定, 行级锁定和页级锁定.
1.表级锁定 (table-level)
表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制.该锁定机制最大的特点是实现逻辑非常简单, 带来的系统负面影响最小.所以获取锁和释放锁的速度很快.由于表级锁一次会将整个表锁定, 所以可以很好的避免困扰我们的死锁问题.
当然, 锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高, 致使并大度大打折扣.
使用表级锁定的主要是MyISAM, MEMORY, CSV等一些非事务性存储引擎.
2.行级锁定 (row-level)
行级锁定最大的特点就是锁定对象的颗粒度很小, 也是目前各大数据库管理软件所实现的锁定颗粒度最小的.由于锁定颗粒度很小, 所以发生锁定资源争用的概率也最小, 能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能.
虽然能够在并发处理能力上面有较大的优势, 但是行级锁定也因此带来了不少弊端.由于锁定资源的颗粒度很小, 所以每次获取锁和释放锁需要做的事情也更多, 带来的消耗自然也就更大了.此外, 行级锁定也最容易发生死锁.
使用行级锁定的主要是InnoDB存储引擎.
3.页级锁定 (page-level)
页级锁定是MySQL中比较独特的一种锁定级别, 在其他数据库管理软件中也并不是太常见.页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间, 所以获取锁定所需要的资源开销, 以及所能提供的并发处理能力也同样是介于上面二者之间.另外, 页级锁定和行级锁定一样, 会发生死锁.
在数据库实现资源锁定的过程中, 随着锁定资源颗粒度的减小, 锁定相同数据量的数据所需要消耗的内存数量是越来越多的, 实现算法也会越来越复杂.不过, 随着锁定资源颗粒度的减小, 应用程序的访问请求遇到锁等待的可能性也会随之降低, 系统整体并发度也随之提升.

读锁: 也叫共享锁, S锁, 若事务T对数据对象A加上S锁, 则事务T可以读A但不能修改A, 其他事务只能再对A加S锁, 而不能加X锁, 直到T释放A上的S 锁.这保证了其他事务可以读A, 但在T释放A上的S锁之前不能对A做任何修改.

写锁: 又称排他锁, X锁.若事务T对数据对象A加上X锁, 事务T可以读A也可以修改A, 其他事务不能再对A加任何锁, 直到T释放A上的锁.这保证了其他事务在T释放A上的锁之前不能再读取和修改A.

表锁: 操作对象是数据表.Mysql大多数锁策略都支持(常见mysql innodb), 是系统开销最低但并发性最低的一个锁策略.事务t对整个表加读锁, 则其他事务可读不可写, 若加写锁, 则其他事务增删改都不行.

行级锁: 操作对象是数据表中的一行.是MVCC技术用的比较多的, 但在MYISAM用不了, 行级锁用mysql的储存引擎实现而不是mysql服务器.但行级锁对系统开销较大, 处理高并发较好.

Innodb中基本锁有以下4种:
行级锁
1). 共享锁(S Lock) : 允许事务读一行数据
2). 排它锁(X Lock) : 允许事务删除或更新一行数据
表级锁
3). 意向共享锁(IS Lock): 事务想要获得一张表中某几行的共享锁
4). 意向排它锁(IX Lock): 事务想要获得一张表中某几行的排它锁
由于Innodb引擎支持的均为行锁, 所以意向锁其实不会阻塞除全表扫描之外的任何请求

Innodb中行锁有三种基本的算法:
1). record lock : 单个行记录上的锁
2). gap lock: 锁定一个范围, 但不包含记录本身
3). next-key lock : record lock + gap lock锁定一个范围, 并且锁定记录本身.避免幻读

1. 并发控制

1.1. 读写锁

在处理并发读写时, 可以通过实现一个由 2 种类型组成的锁系统来解决问题.

  • 读锁
    也称 共享锁.
    读锁是共享的, 也就是说相互不阻塞的.
    多个客户端在同一时刻可以同时读取同一个资源, 而互不干扰.
  • 写锁
    也称 排它锁.
    写锁是排他的, 也就是说一个写锁会阻塞其他的写锁和读锁.
    只有这样, 才能确保在给定的时间内, 只有一个用户能执行写入, 并防止其他用户读取正在写入的同一资源.

1.2. 锁粒度

  • 表锁
  • 行级锁

1.3. 死锁

死锁是指两个或多个事务在统一资源上相互占用, 并请求锁定对方占用的资源, 从而导致恶性循环的现象.
当多个事务试图以不同的顺序锁定资源时, 就可能产生死锁.
多个事务同时锁定同一个资源时, 也会产生死锁.

例如:

1
2
3
4
5
6
7
8
9
10
11
-- 事务1
START TRANSACTION;
UPDATE StockPrice SET close = 45.50 WHERE  stock_id = 4 and date = '2002-05-01';
UPDATE StockPrice SET close = 19.80 WHERE  stock_id = 3 and date = '2002-05-02';
COMMIT;

-- 事务2
START TRANSACTION;
UPDATE StockPrice SET close = 20.12 WHERE  stock_id = 3 and date = '2002-05-02';
UPDATE StockPrice SET close = 47.20 WHERE  stock_id = 4 and date = '2002-05-01';
COMMIT;

如果凑巧, 两个事务都执行了第一条 UPDATE 语句, 更新了一行数据, 同时也锁定了该行数据, 接着每个书屋都尝试去执行第二条 UPDATE 语句, 却发现该行为已经被对方锁定了, 然后两个事务都等待对方释放锁, 同时又持有对方需要的锁, 则陷入死循环. 除非有外部因素介入才可能解除死锁.

为了解决这个问题, 数据库系统实现了各种死锁检测和死锁超时机制.
越复杂的系统, 比如 InnoDB 存储引擎, 越能检测到死锁的循环依赖, 并立即返回一个错误.
这种解决方式很有效, 否则死锁会导致出现非常慢的查询.

还有一种解决方式就是, 当查询的时间达到锁等待超时的设定后放弃锁请求.
这种方式通常来说不太好.

InnoDB 目前处理死锁的方式是, 将持有最少行级排他锁的事务回滚 (这是相对比较简单的死锁回滚算法).

锁的行为和顺序是和存储引擎相关的.
以同样的顺序执行语句, 有些存储引擎会产生死锁, 有些则不会.
死锁产生的有双重原因:有些是因为真正的数据冲突, 这种请求通常很难避免;但有些则完全是由于存储引擎的实现方式导致的.

死锁发生以后, 只有部分或者完全回滚其中一个事务, 才能打破死锁.
对于事务型的系统, 这是无法避免的, 所以应用程序在设计时必须考虑如何处理死锁, 大多数情况下需要重新执行因死锁回滚的事务即可,

1.4. 多版本并发控制 MVCC

https://blog.csdn.net/zx64881926/article/details/75150421