阅读 441

各种隔离级别的群魔乱舞 | SQL全面教程十一:事务(6)厘清MySQL/MariaDB中如何查看和设置隔离级别,设置binlog_format为row

这是我参与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 UNCOMMITTEDREPEATABLE READ隔离级别。

文章分类
后端
文章标签