老男孩mysql笔记_老男孩MySQL笔记 第二天2-2

两种修改隔离级别的方法

事务具有ACID四种特性。

但是Isolation并发可能引起如下问题：

1.脏读

允许读取到未提交的脏数据。

2.不可重复读

如果你在时间点T1读取了一些记录，在T2时再想重新读取一次同样的这些记录时，这些记录可能已经被改变、或者消失不见。

3.幻读

解决了不重复读，保证了同一个事务里，查询的结果都是事务开始时的状态(一致性)。但是，如果另一个事务同时提交了新数据，本事务再更新时，就会“惊奇的”发现了这些新数据，貌似之前读到的数据是“鬼影”一样的幻觉。

由ANSI/ISO定义的SQL-92标准定义的四种隔离级别

1.Read Uncommitted

2.Read Committed

3.Repeatable Read

4.Serializable

隔离解别脏读不可重复读幻读

Read UncommittedYYY

Read CommittedNYY

Repeatable(default)NNY

SerializableNNN

下面用Mysql数据库做一些小实验

方法1：

用户可以用SET TRANSACTION语句改变单个会话或者所有新进连接的隔离级别。它的语法如下：

SET[SESSION | GLOBAL] TRANSACTIONISOLATION LEVEL{READ UNCOMMITTED| READ COMMITTED | REPEATABLE READ| SERIALIZABLE}

注意：默认的行为(不带session和global)是为下一个(未开始)事务设置隔离级别。如果你使用GLOBAL关键字，语句在全局对从那点开始创建的所有新连接(除了不存在的连接)设置默认事务级别。你需要SUPER权限来做这个。使用SESSION关键字为将来在当前连接上执行的事务设置默认事务级别。任何客户端都能自由改变会话隔离级别(甚至在事务的中间)，或者为下一个事务设置隔离级别。你可以用下列语句查询全局和会话事务隔离级别：

SELECT@@global.tx_isolation;

SELECT@@session.tx_isolation;

SELECT@@tx_isolation;

网上有人使用set tx_isolation命令：

mysql> settx_isolation='read-committed';Query OK, 0 rows affected (0.00sec)+----------------+| @@tx_isolation |+----------------+| READ-COMMITTED|+----------------+1 row in set (0.00 sec)

mysql> select@@session.tx_isolation;+------------------------+| @@session.tx_isolation|+------------------------+|READ-COMMITTED        |+------------------------+1 row in set (0.00 sec)事务隔离变了。网上还有人这样写set @@tx_isolation命令，但这个命令是有问题的。

mysql>  set @@tx_isolation='read-committed';QueryOK, 0 rows affected (0.00 sec)

mysql> select@@session.tx_isolation;+------------------------+| @@session.tx_isolation|+------------------------+| REPEATABLE-READ      |+------------------------+1 row in set (0.00 sec)

mysql> select @@tx_isolation;+-----------------+|@@tx_isolation  |+-----------------+| REPEATABLE-READ |+-----------------+1row in set (0.00 sec)session事物的隔离级别并没有改变。

方法2：

[sql]viewplaincopy

1.mysql>selectversion();

2.+------------+

3.| version()  |

4.+------------+

5.| 5.1.52-log |

6.+------------+

7.1 row inset(0.00 sec)

查看InnoDB存储引擎系统级的隔离级别和会话级的隔离级别

[sql]viewplaincopy

1.mysql>select@@global.tx_isolation,@@tx_isolation;

2.+-----------------------+-----------------+

3.| @@global.tx_isolation | @@tx_isolation  |

4.+-----------------------+-----------------+

5.|REPEATABLE-READ|REPEATABLE-READ|

6.+-----------------------+-----------------+

7.1 row inset(0.00 sec)

更改会话级的隔离级别

[sql]viewplaincopy

1.Session 1:

2.mysql>setsession tx_isolation='read-uncommitted';

3.Query OK, 0rowsaffected (0.00 sec)

4.mysql>select@@global.tx_isolation,@@tx_isolation;

5.+-----------------------+------------------+

6.| @@global.tx_isolation | @@tx_isolation   |

7.+-----------------------+------------------+

8.|REPEATABLE-READ|READ-UNCOMMITTED|

9.+-----------------------+------------------+

10.1 row inset(0.00 sec)

11.

12.

13.Session 2:

14.mysql>select@@global.tx_isolation, @@tx_isolation;

15.+-----------------------+-----------------+

16.| @@global.tx_isolation | @@tx_isolation  |

17.+-----------------------+-----------------+

18.|REPEATABLE-READ|REPEATABLE-READ|

19.+-----------------------+-----------------+

20.1 row inset(0.00 sec)

更改系统级的隔离级别

[sql]viewplaincopy

1.Session 1:

2.mysql>setglobaltx_isolation='read-uncommitted';

3.Query OK, 0rowsaffected (0.00 sec)

4.mysql>select@@global.tx_isolation,@@tx_isolation;

5.+-----------------------+------------------+

6.| @@global.tx_isolation | @@tx_isolation   |

7.+-----------------------+------------------+

8.|READ-UNCOMMITTED|READ-UNCOMMITTED|

9.+-----------------------+------------------+

10.1 row inset(0.00 sec)

11.

12.Session 2:

13.mysql>select@@global.tx_isolation, @@tx_isolation;

14.+-----------------------+-----------------+

15.| @@global.tx_isolation | @@tx_isolation  |

16.+-----------------------+-----------------+

17.|READ-UNCOMMITTED|REPEATABLE-READ|

18.+-----------------------+-----------------+

19.1 row inset(0.00 sec)

关闭SQL语句的自动提交

[sql]viewplaincopy

1.mysql>setautocommit=off;

2.Query OK, 0rowsaffected (0.00 sec)

查看SQL语句自动提交是否关闭

[plain]viewplaincopy

1.mysql> show variables like 'autocommit';

2.+---------------+-------+

3.| Variable_name | Value |

4.+---------------+-------+

5.| autocommit    | OFF   |

6.+---------------+-------+

7.1 row in set (0.00 sec)

不重启修改MySQL的方法

在线修改mysql的系统变量：

mysql> show variables like 'log_slave_updates';+-------------------+-------+|Variable_name|Value |+-------------------+-------+| log_slave_updates | ON|+-------------------+-------+1 row in set (0.00 sec)

mysql> set global log_slave_updates=0;ERROR1193 (HY000): Unknown system variable 'log_slave_updates'

mysql>system gdb -p $(pidof mysqld) -ex"set opt_log_slave_updates=0" -batchExcess command line argumentsignored. (9922 ...)19885: No such file or directory.(no debugging symbolsfound)Using host libthread_db library "/lib64/libthread_db.so.1".(nodebugging symbols found)(no debugging symbols found)(no debugging symbolsfound)(no debugging symbols found)[Thread debugging using libthread_dbenabled][New Thread 47873757603968 (LWP 12328)][New Thread 1166383424 (LWP12341)][New Thread 1166117184 (LWP 12339)][New Thread 1092294976 (LWP12338)][New Thread 1165850944 (LWP 12337)][New Thread 1155361088 (LWP12336)][New Thread 1144871232 (LWP 12335)][New Thread 1134381376 (LWP12333)][New Thread 1123891520 (LWP 12332)][New Thread 1113401664 (LWP 12331)][NewThread 1102911808 (LWP 12330)](no debugging symbols found)(no debugging symbolsfound)(no debugging symbols found)(no debugging symbols found)(no debuggingsymbols found)(no debugging symbols found)(no debugging symbols found)(nodebugging symbols found)0x00000039314cb332 in select () from /lib64/libc.so.6

mysql> show variables like 'log_slave_updates';+-------------------+-------+|Variable_name|Value |+-------------------+-------+| log_slave_updates | OFF|+-------------------+-------+1row in set (0.00 sec)

MySQL系统参数设置

InonoDB参数设置

演练参数

innodb_buffer_pool_size