Strange behaviour from MYSQL 5 (Database Isolation)

Question

I opened two command windows to work with my Database (MySQL5).

Below is table structure I'm working with (It should be noted that I've turned off the auto commit by executing set autocommit=0;):

Table Structure:

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;

Below is sequence of activities:

Step 1: In command window 1, I executed below commands and you could also see the output:

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

Step 2 In second command window, I fired below command and it hangs (it seems waiting for commit command to be issues from previous window)

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

Step 3 I went to Command window#1, and executed commit; you could see the output below:

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

At the same time, i could see that second window that was earlier hanging, also executed the command and printed below output:

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

Step 4 Now I issues commit in my second window to ensure that all the changes gets commited properly even the second session:

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

Step 5 Now since commit has been issued from both windows I thought that everything is all right and the two session also must be in sync, so I went to 1st command window and issues below command:

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

I was surprised because I was expecting City value to be 'Dublin' because changes from second command window (i.e. update) has been commited in Step 4, but i'm still getting Wil in City column.

What am I doing wrong here?

@a_horse_with_no_name: Oh, I missed the create table, sorry. — Bobby, Apr 20 '11 at 08:37
Can you wrap all your 5s with quotes, e.g. `WHERE zipcode = '5'`? After all, ZIPCODE is a varchar column, so you should really be putting strings in it, not integers... (I don't think this *should* affect the answer, but it's probably worth having as little implicit stuff going on as possible when tracking down odd problems.) — Matt Gibson, Apr 20 '11 at 08:42
at step 5, if you run the same select from window 2 - do you get `Dublin` or `Wil`? — Galz, Apr 20 '11 at 09:23

Simon · Accepted Answer · 2011-04-21T07:49:24.737

This is to do with isolation levels. If you raise your isolation level to SERIALIZABLE (the default in MySQL is REPEATABLE READS) you won't get "phantom reads".

Isolation levels and phantom reads are described on the Wikipedia page for database transaction isolation.

If I run this through as you did, but with the higher isolation level, I get the result you were expecting.

Session 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)

Session 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;

Session 1

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

Session 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)

Session 1

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

NB: This doesn't necessarily mean that you should always be using SERIALIZABLE - there are trade-offs. Most notable is that the database will acquire a range lock when executing a SELECT and you'll get more locking-based conflicts.

Update - Explicitly Handling Transactions

Since we have autocommit=0; set in these scripts, we really should handle the transactions explicitly, rather than expecting a START TRANSACTION - although in most cases, the database behaves as you'd expect if you'd executed START TRANSACTION.

However, run the original example while explicitly starting and ending all transactions (including those that are just SELECT, and you get a different result:

Session 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)

Session 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;

Session 1

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

Session 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)

Session 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)

REPEATABLE_READ Isolation level should not affect the results, because I've ended the transaction in both windows by firing commit command. So there is no transaction active when I'm at Step 5. — Vicky, Apr 21 '11 at 03:39
My comments above are based on REPEATABLE_READ Isolation level I could see on net: The Repeatable Read isolation level allows a transaction to acquire read locks on all rows of data it returns to an application, and write locks on all rows of data it inserts, updates, or deletes. By using the Repeatable Read isolation level, SELECT SQL statements issued multiple times within the same transaction will always yield the same result. — Vicky, Apr 21 '11 at 03:40
@ Vicky - I think there's something a little odd about what MySQL considers to be "the same transaction" when you're using `autocommit=0;` and not explicitly executing `START TRANSACTION;` before each transaction. Have a look at my update above - it uses `START TRANSACTION;` in the appropriate places and gets the result you're expecting while using just `REPEATABLE READ` — Simon, Apr 21 '11 at 07:51
Also, I found this URL that throw some light on this behaviour: http://dev.mysql.com/doc/refman/5.0/en/innodb-consistent-read.html. "multi-versioned concurrency control" in MYSQL causes this behaviour. — Vicky, Apr 21 '11 at 12:13
Good find - I was originally going to point out that doing an extra commit/rollback in session 1 would sort the problem out (it does - as that URL shows) but I didn't have the evidence to show why it works that way :) — Simon, Apr 21 '11 at 13:11

Strange behaviour from MYSQL 5 (Database Isolation)

1 Answers1

Session 1

Session 2

Session 1

Session 2

Session 1

Update - Explicitly Handling Transactions

Session 1

Session 2

Session 1

Session 2

Session 1