MySQL事务学习-->隔离级别

MySQL事务学习-->隔离级别

1 在my.cnf中设置,在mysqld选项中如下设置
[mysqld]
 transaction-isolation = READ-COMMITTED
 
2 在mysql窗口用set命令重置
[sql] 
mysql> set global tx_isolation='REPEATABLE-READ';  
Query OK, 0 rows affected (0.01 sec)  
    
mysql>  
查询当前的会话事务级别,可以使用:
[sql] 
mysql> select @@tx_isolation;  
+----------------+  
| @@tx_isolation |  
+----------------+  
| READ-COMMITTED |  
+----------------+  
1 row in set (0.00 sec)  
    
mysql>  
 
 
查询全局的事务隔离级别,可以使用
[sql] 
mysql> select @@global.tx_isolation;  
+-----------------------+  
| @@global.tx_isolation |  
+-----------------------+  
| READ-COMMITTED        |  
+-----------------------+  
1 row in set (0.00 sec)  
    
mysql>  
 
 
 
在Serializable模式下。
[sql] 
mysql> system cat /usr/local/mysql56m2/my.cnf |grep  transaction-isolation 
transaction-isolation = READ-COMMITTED 
mysql>  
 
 
 
复制二进制与隔离级别的关系
在SERIALIZABLE模式下,Innodb存储引擎会对每个select语句自动加Lock in sharedmode,给每一个读操作加共享锁。因此在这个隔离级别下,读占用锁了,一致性的非锁定读不再予以支持。因为Innodb存储引擎在repeatable read 模式下就已经达到了3度的隔离,所以一般不在本地事务中使用serializable隔离级别,serializable的事务隔离级别主要用于innodb存储引擎的分布式事务。
 
在Read committed的隔离模式下,除了唯一性约束检查以及外键约束检查需要Gap lock,innodb存储引擎不会使用gap lock的锁算法。不过使用read committed隔离级别需要注意一些问题,mysql5.1中,Read committed的事务隔离级别默认只能在replication的二进制为row格式下,如果二进制默认在statement模式下,则会报如下错误:
[sql] 
mysql> select @@version;  
+-------------+  
| @@version   |  
+-------------+  
| 5.5.25a-log |  
+-------------+  
1 row in set (0.00 sec)  
    
mysql>  
mysql> select @@binlog_format;  
+-----------------+  
| @@binlog_format |  
+-----------------+  
| STATEMENT       |  
+-----------------+  
1 row in set (0.00 sec)  
    
mysql> select @@tx_isolation;  
+-----------------+  
| @@tx_isolation  |  
+-----------------+  
| REPEATABLE-READ |  
+-----------------+  
1 row in set (0.00 sec)  
    
mysql> set tx_isolation='READ-COMMITTED';  
Query OK, 0 rows affected (0.00 sec)  
    
mysql> use test;  
Database changed  
mysql> create table a (b int, primary key (b)) engine=innodb;  
ERROR 1050 (42S01): Table 'a' already exists  
mysql> select @@tx_isolation;  
+----------------+  
| @@tx_isolation |  
+----------------+  
| READ-COMMITTED |  
+----------------+  
1 row in set (0.00 sec)  
    
mysql> begin 
    -> ;  
Query OK, 0 rows affected (0.00 sec)  
    
mysql> insert into a select 100000;  
ERROR 1665 (HY000): Cannotexecute statement: impossible to write to binary log since BINLOG_FORMAT =STATEMENT and at least one table uses a storage engine limited to row-basedlogging. InnoDB is limited to row-logging when transaction isolation level isREAD COMMITTED or READ UNCOMMITTED.  
 
 ERROR 1665 (HY000): Cannotexecute statement: impossible to write to binary log since BINLOG_FORMAT =STATEMENT and at least one table uses a storage engine limited to row-basedlogging. InnoDB is limited to row-logging when transaction isolation level isREAD COMMITTED or READ UNCOMMITTED. 
[Note]:在mysql5.1以及mysql5.6模式下实验过都是如此。也许可以知道通过将innodb_locks_unsafe_for_binlog设置为1,来可以使binlog日志在statement下使用readcommitted的事务隔离级别:
[sql] 
mysql> select @@innodb_locks_unsafe_for_binlog;  
+----------------------------------+  
| @@innodb_locks_unsafe_for_binlog |  
+----------------------------------+  
|                               0 |  
+----------------------------------+  
1 row in set (0.00 sec)  
mysql> set global innodb_locks_unsafe_for_binlog=1;  
ERROR 1238 (HY000): Variable 'innodb_locks_unsafe_for_binlog' is a readonly variable  
mysql>  
 
 
此参数是只读模式,需要修改my.cnf重新启动才行。
在my.cnf里面的[mysqld]添加
[mysqld]
innodb_locks_unsafe_for_binlog = 1
然后重启,然后去check模仿一个事务操作,如下所示:
[sql] 
mysql> select @@innodb_locks_unsafe_for_binlog;  
+----------------------------------+  
| @@innodb_locks_unsafe_for_binlog |  
+----------------------------------+  
|                               1 |  
+----------------------------------+  
1 row in set (0.00 sec)  
    
mysql>  
mysql> use test;  
Reading table information for completion of table and column names  
You can turn off this feature to get a quicker startup with -A  
    
Database changed  
mysql> select @@tx_isolation;  
+----------------+  
| @@tx_isolation |  
+----------------+  
| READ-COMMITTED |  
+----------------+  
1 row in set (0.00 sec)  
mysql> begin;  
Query OK, 0 rows affected (0.00 sec)  
    
mysql> insert into t select 15;  
Query OK, 1 row affected (0.00 sec)  
Records: 1  Duplicates: 0  Warnings: 0  
    
mysql> commit;  
Query OK, 0 rows affected (0.00 sec)  
mysql> select * from t;  
+--------+  
| id     |  
+--------+  
|      1 |  
|     12 |  
|     15 |  
|  11111 |  
| 111110 |  
+--------+  
5 rows in set (0.00 sec)  

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏逸鹏说道

SQL Server 数据库清除日志的方法

SQLSERVER的数据库日志占用很大的空间,下面提供三种方法用于清除无用的数据库日志文件 方法一: 1、打开查询分析器,输入命令 BACKUP LOG d...

46150
来自专栏撸码那些事

【眼见为实】自己动手实践理解数据库READ UNCOMMITED && SERIALIZABLE

16350
来自专栏「3306 Pai」社区

浅析ProxySQL用户管理

对于读写分离特别重要,保证了同一个事务中所有的语句都会路由到同一组示例,防止出现同一个事务中,上下文数据不一致的情况。例如,在不开启这个属性的情况下:

32710
来自专栏更流畅、简洁的软件开发方式

检查两个数据库里的表名、字段是否一致的一种方法

不知道大家有没有遇到过这种情况。 程序已经给客户安装上了,并且客户已经录入了一些信息,然后程序还需要作比较大的变动(修改功能、增加模块等),数据库就不可避免要...

30480
来自专栏乐沙弥的世界

Oracle 基于用户管理恢复的处理

Oracle支持多种方式来管理数据文件的备份与恢复来保证数据库的可靠与完整。除了使用RMAN工具以及第三方备份与恢复工具之外,基于

7120
来自专栏乐沙弥的世界

只读表空间的备份与恢复

--====================== --  只读表空间的备份与恢复 --====================== 一、只读表空间的特性...

10220
来自专栏linjinhe的专栏

隔离级别、SI 和 SSIACID隔离级别Snapshot IsolationSerializable Snapshot Isolation

16340
来自专栏全栈架构

Elasticsearch Java API 搜索之简介(三)

搜索查询,返回查询匹配的结果,搜索一个index / type 或者多个index / type,可以使用 queryJavaAPI(https://www.e...

9630
来自专栏蓝天

mysql_init调用卡住原因分析

看似简单的代码,但非常不幸,程序运行时,卡在了mysql_init处。语法上看不出任何破绽,原因会是什么了?

11920
来自专栏数据和云

Oracle数据库的初始化与跟踪学习方法

编辑说明:《Oracle性能优化与诊断案例精选》出版以来,收到很多读者的来信和评论,我们会通过连载的形式将书中内容公布出来,希望书中内容能够帮助到更多的读者朋友...

40590

扫码关注云+社区

领取腾讯云代金券