?
MySQL事务学习-->隔离级别(二)
l> 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)