MySQL的锁策略
2021/8/8 2:06:28
本文主要是介绍MySQL的锁策略,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
本文转载自https://www.dazhuanlan.com/hsun0/topics/980417
锁用来保证数据并发访问的一致性、有效性
MySQL 的锁机制比较简单,其最显著的特点是不同的存储引擎支持不同的锁机制。
- MyISAM 和 MEMORY 存储引擎采用的是表级锁(table-level locking)
- BDB 存储引擎采用的是页面锁(page-level locking),但也支持表级锁
- InnoDB 存储引擎既支持行级锁(row-level locking),也支持表级锁
MySQL 这 3 种锁的特性可大致归纳如下:
- 表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低
- 行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
- 页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。
MYISAM 存储引擎的锁机制
MyISAM 存储引擎只支持表锁
查询表级锁争用情况
show status like 'table%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Table_locks_immediate | 2979 | | Table_locks_waited | 0 | +-----------------------+-------+
如果Table_locks_waited
的值比较高,则说明存在着较严重的表级锁争用情况
表级锁的锁模式
MySQL 的表级锁有两种模式:
- 表共享读锁(Table Read Lock)
- 表独占写锁(Table Write Lock)
MyISAM 在执行查询语句(SELECT)前,会自动给涉及的所有表加读锁,在执行更新操作(UPDATE、DELETE、INSERT 等)前,会自动给涉及的表加写锁
简单来说就是 :
读锁可以被读操作共享,不影响读操作,但会影响写操作
写锁会影响所有的读写操作
并发插入
MyISAM 表的读和写是串行的,但这是就总体而言的。在一定条件下,MyISAM 表也支持查询和插入操作的并发进行 MyISAM 存储引擎有一个系统变量concurrent_insert
,专门用以控制其并发插入的行为,其值分别可以为 0、1 或 2。
- 当 concurrent_insert 设置为 0 时,不允许并发插入。
- 当 concurrent_insert 设置为 1 时,如果 MyISAM 表中没有空洞(即表的中间没有被删除的行),MyISAM 允许在一个进程读表的同时,另一个进程从表尾插入记录。这也是 MySQL 的
默认设置
。 - 当 concurrent_insert 设置为 2 时,无论 MyISAM 表中有没有空洞,都允许在表尾并发插入记录
a. 可以利用 MyISAM 存储引擎的并发插入特性,来解决应用中对同一表查询和插入的锁争用。例如,将 concurrent_insert 系统变量设为 2,总是允许并发插入; b. 同时,通过定期在系统空闲时段执行 OPTIMIZE TABLE 语句来整理空间碎片,收回因删除记录而产生的中间空洞。
主动加表锁
Lock tables orders read local, order_detail read local; Select sum(total) from orders; Select sum(subtotal) from order_detail; Unlock tables;
- 上面的例子在 LOCK TABLES 时加了 “local” 选项,其作用就是在满足 MyISAM 表并发插入条件的情况下,允许其他用户在表尾并发插入记录,
- 在执行 LOCK TABLES 后,只能访问显式加锁的这些表,不能访问未加锁的表
- 同时,如果加的是读锁,那么只能执行查询操作,而不能执行更新操作
- 在自动加锁的情况下也基本如此,MyISAM 总是一次获得 SQL 语句所需要的全部锁
- 这也正是 MyISAM 表不会出现死锁(Deadlock Free)的原因。
MyISAM 的锁调度
由于 MySQL 认为写请求一般比读请求要重要,所以如果有读写请求同时进行的话,MYSQL将会优先执行写操作
。 这样 MyISAM 表在进行大量的更新操作时(特别是更新的字段中存在索引的情况下),会造成查询操作很难获得读锁,从而导致查询阻塞。
我们可以通过一些设置来调节 MyISAM 的调度行为:
- 通过指定启动参数 low-priority-updates,使 MyISAM 引擎默认给予读请求以优先的权利
- 通过执行命令 SET LOW_PRIORITY_UPDATES=1,使该连接发出的更新请求优先级降低。
INNODB 存储引擎的锁机制
InnoDB 与 MyISAM 的最大不同有两点:
- 一是支持事务(TRANSACTION);
- 二是采用了行级锁。
获取 InnoDB 行锁争用情况
mysql> show status like 'innodb_row_lock%'; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | InnoDB_row_lock_current_waits | 0 | | InnoDB_row_lock_time | 0 | | InnoDB_row_lock_time_avg | 0 | | InnoDB_row_lock_time_max | 0 | | InnoDB_row_lock_waits | 0 | +-------------------------------+-------+
如果InnoDB_row_lock_waits 和 InnoDB_row_lock_time_avg
的值比较高,说明锁争用比较严重
InnoDB 行锁模式
行锁不影响读操作,只影响写操作
- update ,delete 会自动给涉及到的数据集加上排他锁
- 普通的 select 则不加任何锁
insert 会加什么锁呢? MySQL5.1.22 之后的版本,出现了一个新的配置选项:
innodb_autoinc_lock_mode
,它是专门用来在使用 auto_increment 的情况下调整锁策略的,目前有三种选择:innodb_autoinc_lock_mode = 0 (“traditional” lock mode)
# 仍然是表锁innodb_autoinc_lock_mode = 1 (“consecutive” lock mode)
#默认方式
, 只锁分配 A_I 的过程,可一次分配多个innodb_autoinc_lock_mode = 2 (“interleaved” lock mode)
# 只锁分配 A_I 的过程,来一个分配一个
主动加行锁
事务可以通过以下语句显式给记录集加共享锁或排他锁。
- 共享锁(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 行锁是通过给索引上的索引项加锁来实现的,这一点 MySQL 与 Oracle 不同,后者是通过在数据块中对相应数据行加锁来实现的。 InnoDB 这种行锁实现特点意味着:只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁
!
乐观锁与悲观锁
在多用户环境中,在同一时间可能会有多个用户更新相同的记录,这会产生冲突。这就是著名的并发性问题
。
典型的冲突有:
-
丢失更新:一个事务的更新覆盖了其它事务的更新结果,就是所谓的更新丢失。例如:用户 A 把值从 6 改为 2,用户 B 把值从 2 改为 6,则用户 A 丢失了他的更新。
-
脏读:当一个事务读取其它完成一半事务的记录时,就会发生脏读取。例如:用户 A,B 看到的值都是 6,用户 B 把值改为 2,用户 A 读到的值仍为 6。
为了解决这些并发带来的问题。 我们需要引入并发控制机制。
并发控制机制
- 悲观锁:假定会发生并发冲突,屏蔽一切可能违反数据完整性的操作。
- 乐观锁:假设不会发生并发冲突,只在提交操作时检查是否违反数据完整性。乐观锁不能解决脏读的问题。
乐观锁应用
- 使用自增长的整数表示数据版本号。更新时检查版本号是否一致,比如数据库中数据版本为 6,更新提交时 version=6+1,使用该 version 值 (=7) 与数据库 version+1(=7) 作比较,如果相等,则可以更新,如果不等则有可能其他程序已更新该记录,所以返回错误。
- 使用时间戳来实现.基本思路与版本号一样
悲观锁应用
悲观锁的实现,往往依靠数据库提供的锁机制(也只有数据库层提供的锁机制才能真正保证数据访问的排他性,否则,即使在本系统中实现了加锁机制,也无法保证外部系统不会修改数据)。
#0.开始事务 begin work;
#1.查询出商品信息,并为这一行加上排他锁 select status from t_goods where id=1 for update;
#2.根据商品信息生成订单 insert into t_orders (id,goods_id) values (null,1);
#3.修改商品status为2 update t_goods set status=2;
#4.提交事务 commit;
- 上面的第一步我们执行了一次查询操作:select status from t_goods where id=1
for update
; - 这样就通过数据库实现了悲观锁
- 此时在 t_goods 表中,id 为 1 的 那条数据就被我们锁定了,其它的事务必须等本次事务提交之后才能执行
- 需要注意的是,在事务中,只有 SELECT … FOR UPDATE 或 LOCK IN SHARE MODE 同一笔数据时会等待其它事务结束后才执行,一般 SELECT … 则不受此影响
这篇关于MySQL的锁策略的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-06-25MySQL报错Duplicate entry '0' for key 'PRIMARY'
- 2024-05-29阿里 Canal 实时同步 MySQL 增量数据至 ClickHouse 数据库
- 2024-05-24在Linux下管理MySQL的大小写敏感性
- 2024-04-26MySQL查出时间比实际晚8小时的解决方案
- 2024-04-01JPA不识别MySQL的枚举类型
- 2024-03-30mysql数据库表卡死解决方法
- 2024-03-15MySQL多数据源笔记5-ShardingJDBC实战
- 2024-03-11natural join mysql
- 2024-03-11关于VS2017,VS2015 中利用 EF使用Mysql 不显示数据源问题解决方案
- 2024-02-26mysql 阿里云xb后缀备份文件恢复-icode9专业技术文章分享