这是我参与8月更文挑战的第4天,活动详情查看:8月更文挑战
MySQL/MariaDB设置事务隔离级别
InnoDB存储引擎默认的隔离级别是可重复读REPEATABLE READ
——RR
。为了避免产生幻读,会使用Next-Key Lock
锁的算法(行记录锁Record Lock
和间隙锁Gap Lock
)。从而实现事务的完全隔离。
MySQL在可重复读隔离级别下为了解决幻读,引入了间隙锁,但这也会导致死锁等问题更频繁发生,降低事务执行的并发度等。
间隙锁和行锁合称 next-key lock ,每个 next-key lock 是前开后闭区间(锁的范围值前开后闭,在这个范围内的值的记录不允许插入、删除)。
实际应用中,大多数会选择读提交(RC)隔离级别,同时设置binlog_format=row
(解决可能出现的数据和日志不一致问题)。
具体如何使用还是看实际业务需求,如果业务不需要可重复读的保证,则在读提交级别下,操作数据的锁范围更小,提高并发等。
在InnoDB存储引擎中SERIALIZABLE事务隔离级别主要用于分布式事务,分布式事务中隔离级别必须为SERIALIZABLE。
查看当前的隔离级别
事务的隔离级别应用的最低粒度是会话级别,因此可以查看或设置全局或当前会话的隔离级别。
注:隔离级别的变量,有的版本为
transaction_isolation
。
- 当前会话的隔离级别
show variables like 'tx_isolation';
> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.001 sec)
- 当前会话的隔离级别
select @@tx_isolation;
> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.000 sec)
查看全局的隔离级别
- 全局的隔离级别
select @@global.tx_isolation;
> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ |
+-----------------------+
1 row in set (0.000 sec)
设置事务的隔离级别
set [global | session] transaction isolation level [read uncommitted| read committed | repeatable read | serializable]
session不可省略,否则无法设置成功!
设置当前会话的隔离级别
set @@session.tx_isolation= 'read-committed';
或
set session transaction isolation level serializable;
设置全局的隔离级别
set @@global.tx_isolation= 'read-committed';
或
set global transaction isolation level serializable;
MySQL的配置文件永久设置事务隔离级别
还可以在MySQL的配置文件中,永久设置事务的隔离级别。在[mysqld]下添加如下行:
[mysqld]
transaction-isolation = READ-COMMITTED
MySQL实际常用的隔离级别设置
MySQL实际中基于锁的隔离级别设置,常用的有以下两种:
- Gap+RR (默认)
- Row+RC (更常用)
Gap+RR:MySQL默认设置,是使用可重复读(RR),加Gap锁的形式。
RR+Gap实现可串行化的效果,但间隙锁会导致死锁等问题更频繁发生,降低事务执行的并发度等。
Row+RC:是指使用读提交(RC)隔离级别,二进制日志binlog的格式为ROW
设置binlog_format=row
,是为保证数据和日志的一致,即二进制日志binlog的格式为ROW。
无论使用何种级别的隔离,都应将二进制日志(binlog)的格式设置为ROW。因为ROW格式记录的是行的变更,而不是简单的SQL语句,可以避免一些数据和日志不一致、主从不同步等的现象。
MySQL5.1以上的版本才支持 binlog_format 的 row 格式。
设置和查看binlog_format
==前提要启用了二进制日志binlog==
set binlog_format = 'row';
或配置文件中
[mysqld]
binlog-format = ROW
- 设置和查看binlog_format
> set binlog_format = 'row';
Query OK, 0 rows affected (0.005 sec)
> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.001 sec)
Oracle的隔离级别
Oracle、pg、SQL Server等默认的隔离级别都是读提交(RC
)。如果从其他数据库系统迁移到MySQL,为了保持隔离级别的一致,也需要将MySQL的隔离级别设置为"读提交"。
Oracle数据库不支持READ UNCOMMITTED
和REPEATABLE READ
隔离级别。