博客园  :: 首页  :: 新随笔  :: 联系 :: 订阅 订阅  :: 管理

在MySQL中设置事务隔离级别有2种方法:

Posted on 2016-08-09 15:15  moss_tan_jun  阅读(11017)  评论(0)  编辑  收藏  举报

在MySQL中设置事务隔离级别有2种方法:

1 在my.cnf中设置，在mysqld选项中如下设置

[mysqld]

 transaction-isolation = READ-COMMITTED

 

2 在mysql窗口用set命令重置

[sql] view plain copy print?
  1. mysql> set global tx_isolation='REPEATABLE-READ';  
  2. Query OK, 0 rows affected (0.01 sec)  
  3.    
  4. mysql>  

查询当前的会话事务级别，可以使用：

[sql] view plain copy print?
  1. mysql> select @@tx_isolation;  
  2. +----------------+  
  3. | @@tx_isolation |  
  4. +----------------+  
  5. | READ-COMMITTED |  
  6. +----------------+  
  7. 1 row in set (0.00 sec)  
  8.    
  9. mysql>  


查询全局的事务隔离级别，可以使用

[sql] view plain copy print?
  1. mysql> select @@global.tx_isolation;  
  2. +-----------------------+  
  3. | @@global.tx_isolation |  
  4. +-----------------------+  
  5. | READ-COMMITTED        |  
  6. +-----------------------+  
  7. 1 row in set (0.00 sec)  
  8.    
  9. mysql>  


 

在Serializable模式下。

[sql] view plain copy print?
      1. mysql> system cat /usr/local/mysql56m2/my.cnf |grep  transaction-isolation  
      2. transaction-isolation = READ-COMMITTED  
      3. 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] view plain copy print?
  1. mysql> select @@version;  
  2. +-------------+  
  3. | @@version   |  
  4. +-------------+  
  5. | 5.5.25a-log |  
  6. +-------------+  
  7. 1 row in set (0.00 sec)  
  8.    
  9. mysql>  
  10. mysql> select @@binlog_format;  
  11. +-----------------+  
  12. | @@binlog_format |  
  13. +-----------------+  
  14. | STATEMENT       |  
  15. +-----------------+  
  16. 1 row in set (0.00 sec)  
  17.    
  18. mysql> select @@tx_isolation;  
  19. +-----------------+  
  20. | @@tx_isolation  |  
  21. +-----------------+  
  22. | REPEATABLE-READ |  
  23. +-----------------+  
  24. 1 row in set (0.00 sec)  
  25.    
  26. mysql> set tx_isolation='READ-COMMITTED';  
  27. Query OK, 0 rows affected (0.00 sec)  
  28.    
  29. mysql> use test;  
  30. Database changed  
  31. mysql> create table a (b int, primary key (b)) engine=innodb;  
  32. ERROR 1050 (42S01): Table 'a' already exists  
  33. mysql> select @@tx_isolation;  
  34. +----------------+  
  35. | @@tx_isolation |  
  36. +----------------+  
  37. | READ-COMMITTED |  
  38. +----------------+  
  39. 1 row in set (0.00 sec)  
  40.    
  41. mysql> begin  
  42.     -> ;  
  43. Query OK, 0 rows affected (0.00 sec)  
  44.    
  45. mysql> insert into a select 100000;  
  46. 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] view plain copy print?
  1. mysql> select @@innodb_locks_unsafe_for_binlog;  
  2. +----------------------------------+  
  3. | @@innodb_locks_unsafe_for_binlog |  
  4. +----------------------------------+  
  5. |                               0 |  
  6. +----------------------------------+  
  7. 1 row in set (0.00 sec)  
  8. mysql> set global innodb_locks_unsafe_for_binlog=1;  
  9. ERROR 1238 (HY000): Variable 'innodb_locks_unsafe_for_binlog' is a readonly variable  
  10. mysql>  


此参数是只读模式，需要修改my.cnf重新启动才行。

在my.cnf里面的[mysqld]添加

[mysqld]

innodb_locks_unsafe_for_binlog = 1

然后重启，然后去check模仿一个事务操作，如下所示：

[sql] view plain copy print?
    1. mysql> select @@innodb_locks_unsafe_for_binlog;  
    2. +----------------------------------+  
    3. | @@innodb_locks_unsafe_for_binlog |  
    4. +----------------------------------+  
    5. |                               1 |  
    6. +----------------------------------+  
    7. 1 row in set (0.00 sec)  
    8.    
    9. mysql>  
    10. mysql> use test;  
    11. Reading table information for completion of table and column names  
    12. You can turn off this feature to get a quicker startup with -A  
    13.    
    14. Database changed  
    15. mysql> select @@tx_isolation;  
    16. +----------------+  
    17. | @@tx_isolation |  
    18. +----------------+  
    19. | READ-COMMITTED |  
    20. +----------------+  
    21. 1 row in set (0.00 sec)  
    22. mysql> begin;  
    23. Query OK, 0 rows affected (0.00 sec)  
    24.    
    25. mysql> insert into t select 15;  
    26. Query OK, 1 row affected (0.00 sec)  
    27. Records: 1  Duplicates: 0  Warnings: 0  
    28.    
    29. mysql> commit;  
    30. Query OK, 0 rows affected (0.00 sec)  
    31. mysql> select * from t;  
    32. +--------+  
    33. | id     |  
    34. +--------+  
    35. |      1 |  
    36. |     12 |  
    37. |     15 |  
    38. |  11111 |  
    39. | 111110 |  
    40. +--------+  
    41. 5 rows in set (0.00 sec)