MYSQL 5中的奇怪行为(数据库隔离)

Question

我打开两个命令窗口来使用我的数据库(MySQL5)。

下面是我正在使用的表结构(应该注意，我已经通过执行set autocommit=0;关闭了自动提交)：

表结构：

CREATE TABLE  `ajax`.`zipcodes` (
  `ZIPCODE` varchar(5) NOT NULL,
  `CITY` varchar(50) DEFAULT NULL,
  `STATE` varchar(2) DEFAULT NULL,
  PRIMARY KEY (`ZIPCODE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

以下是活动顺序：

步骤1 :命令窗口1中的，我执行了以下命令，您还可以看到输出：

mysql> insert into ajax.zipcodes values(5, 'Wil', 'AK');
Query OK, 1 row affected (0.00 sec)

步骤2在第二个命令窗口中，我在命令下面触发，它挂起(似乎等待提交命令从前一个窗口发出)

mysql> update ajax.zipcodes set city='Dublin' where zipcode=5;

步骤3 I转到命令window#1，并执行commit；您可以看到下面的输出：

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

同时，我可以看到前面挂起的第二个窗口，它也执行命令，并输出如下：

mysql> update ajax.zipcodes set city='Dublin' where zipcode=5;
Query OK, 1 row affected (3.63 sec)
Rows matched: 1  Changed: 1  Warnings: 0

步骤4--现在我在第二个窗口中发出命令，以确保所有更改都正确地完成，甚至是第二次会话：

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

自从从两个窗口发出步骤5后，我认为一切正常，两个会话也必须是同步的，所以我转到第一个命令窗口并发出以下命令：

mysql> select * from zipcodes where zipcode=5;
+---------+------+-------+
| ZIPCODE | CITY | STATE |
+---------+------+-------+
| 5       | Wil  | AK    |
+---------+------+-------+
1 row in set (0.00 sec)

我很惊讶，因为我期望City值是'Dublin'，因为第二个命令窗口(即update)的更改已经在Step 4中实现了，但我仍然在City列中获得Wil。

我在这里做错什么了？

Answer 1

这与隔离级别有关。如果将隔离级别提高到SERIALIZABLE ( MySQL中的缺省值是REPEATABLE READS)，就不会得到“幻影读取”。

隔离级别和幻影读取在用于数据库事务隔离的Wikipedia页面上进行了描述。

如果我像您一样执行这个过程，但是使用更高的隔离级别，我就会得到您期望的结果。

第1场会议

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE  `ajax`.`zipcodes` (
    ->   `ZIPCODE` varchar(5) NOT NULL,
    ->   `CITY` varchar(50) DEFAULT NULL,
    ->   `STATE` varchar(2) DEFAULT NULL,
    ->   PRIMARY KEY (`ZIPCODE`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.07 sec)

mysql> insert into ajax.zipcodes values(5, 'Wil', 'AK');
Query OK, 1 row affected (0.00 sec)

第2场会议

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Query OK, 0 rows affected (0.00 sec)

mysql> update ajax.zipcodes set city='Dublin' where zipcode=5;

第1场会议

mysql> commit;
Query OK, 0 rows affected (0.04 sec)

第2场会议

/* continued from previous (was frozen) */
Query OK, 1 row affected (7.54 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from zipcodes;
+---------+--------+-------+
| ZIPCODE | CITY   | STATE |
+---------+--------+-------+
| 5       | Dublin | AK    |
+---------+--------+-------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.04 sec)

mysql> select * from zipcodes;
+---------+--------+-------+
| ZIPCODE | CITY   | STATE |
+---------+--------+-------+
| 5       | Dublin | AK    |
+---------+--------+-------+
1 row in set (0.00 sec)

第1场会议

mysql> select * from zipcodes;
+---------+--------+-------+
| ZIPCODE | CITY   | STATE |
+---------+--------+-------+
| 5       | Dublin | AK    |
+---------+--------+-------+
1 row in set (0.00 sec)

注：--这并不一定意味着你应该一直使用SERIALIZABLE --这是一种权衡。最值得注意的是，数据库在执行SELECT时将获得一个范围锁，并且您将获得更多基于锁定的冲突。

更新-显式处理事务

因为我们在这些脚本中设置了autocommit=0;，所以我们确实应该显式地处理事务，而不是期待一个START TRANSACTION --尽管在大多数情况下，数据库的行为与您在执行START TRANSACTION时所期望的一样。

但是，在显式启动和结束所有事务(包括那些仅为SELECT的事务)时运行原始示例，您将得到一个不同的结果：

第1场会议

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE  `ajax`.`zipcodes` (
    ->   `ZIPCODE` varchar(5) NOT NULL,
    ->   `CITY` varchar(50) DEFAULT NULL,
    ->   `STATE` varchar(2) DEFAULT NULL,
    ->   PRIMARY KEY (`ZIPCODE`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.07 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into ajax.zipcodes values(5, 'Wil', 'AK');
Query OK, 1 row affected (0.00 sec)

第2场会议

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.00 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> update ajax.zipcodes set city='Dublin' where zipcode=5;

第1场会议

mysql> commit;
Query OK, 0 rows affected (0.04 sec)

第2场会议

/* continued from previous (was frozen) */
Query OK, 1 row affected (8.32 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from zipcodes;
+---------+--------+-------+
| ZIPCODE | CITY   | STATE |
+---------+--------+-------+
| 5       | Dublin | AK    |
+---------+--------+-------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.04 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from zipcodes;
+---------+--------+-------+
| ZIPCODE | CITY   | STATE |
+---------+--------+-------+
| 5       | Dublin | AK    |
+---------+--------+-------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.04 sec)

第1场会议

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from zipcodes;
+---------+--------+-------+
| ZIPCODE | CITY   | STATE |
+---------+--------+-------+
| 5       | Dublin | AK    |
+---------+--------+-------+
1 row in set (0.00 sec)