Bug #23165 READ COMMITTED doesn't work immediately
Submitted: 11 Oct 2006 6:12 Modified: 9 Nov 2006 14:26
Reporter: Arkadiusz Miskiewicz (Basic Quality Contributor) Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.0.26 OS:
Assigned to: Assigned Account CPU Architecture:Any

[11 Oct 2006 6:12] Arkadiusz Miskiewicz
Description:
I'm not sure if this is a real bug or I missed something in documentation.

If we have two connections to mysql server. First one starts transaction in repeatable read isolation mode, does select something. Then the second connection updates something. Then back in first connection we change isolation level to read commited and do select something again - we do not see a change made by second connection! We do second select and now the change is visible.

It should be visible immediately for first select, too I guess (?).

I'm using mysql 5.0.26 on pld-linux i686 with InnoDB tables.

How to repeat:
mysql> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE version SET version_name='test1';
Query OK, 24 rows affected (0.03 sec)
Rows matched: 24  Changed: 24  Warnings: 0

mysql> SELECT version_name FROM version;
+--------------+
| version_name |
+--------------+
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
+--------------+
24 rows in set (0.00 sec)

  mysql> BEGIN
    -> ;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT version_name FROM version;
+--------------+
| version_name |
+--------------+
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
+--------------+
24 rows in set (0.00 sec)

now the seconnd connection to mysql server is created and the second one does:
 mysql> UPDATE version SET version_name='test-from-second-connection';
Query OK, 24 rows affected (0.03 sec)
Rows matched: 24  Changed: 24  Warnings: 0

now back to first connection:
 mysql> SELECT version_name FROM version;
+--------------+
| version_name |
+--------------+
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
| test1        |
+--------------+
24 rows in set (0.00 sec)

mysql> SELECT version_name FROM version;
+-----------------------------+
| version_name                |
+-----------------------------+
| test-from-second-connection |
| test-from-second-connection |
| test-from-second-connection |
| test-from-second-connection |
| test-from-second-connection |
| test-from-second-connection |
| test-from-second-connection |
| test-from-second-connection |
| test-from-second-connection |
| test-from-second-connection |
| test-from-second-connection |
| test-from-second-connection |
| test-from-second-connection |
| test-from-second-connection |
| test-from-second-connection |
| test-from-second-connection |
| test-from-second-connection |
| test-from-second-connection |
| test-from-second-connection |
| test-from-second-connection |
| test-from-second-connection |
| test-from-second-connection |
| test-from-second-connection |
| test-from-second-connection |
+-----------------------------+
24 rows in set (0.00 sec)

The change is visible but only after 2 SELECTs.

Shouldn't it be visible immediately (since connection 2 works in autocommit=1 mode) ?
[11 Oct 2006 6:17] Arkadiusz Miskiewicz
I forgot to paste one important part. It should be:

[...]
now the seconnd connection to mysql server is created and the second one does:
 mysql> UPDATE version SET version_name='test-from-second-connection';
Query OK, 24 rows affected (0.03 sec)
Rows matched: 24  Changed: 24  Warnings: 0

now back to first connection:
 mysql> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)
 mysql> SELECT version_name FROM version;
[...]
[11 Oct 2006 11:28] Valeriy Kravchuk
Thank you for a problem report. Please, send the results of

SHOW VARIABLES LIKE 'query_cache%';

I want to check if query cache is involved.
[11 Oct 2006 11:39] Arkadiusz Miskiewicz
mysql> SHOW VARIABLES LIKE 'query_cache%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 0       |
| query_cache_type             | ON      |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+
5 rows in set (0.00 sec)
[11 Oct 2006 12:21] Heikki Tuuri
Hi!

Are you running in AUTOCOMMIT=1 mode?

Remember that SET TRANSACTION ISOLATION LEVEL ... only affects the single  transaction that is just being started in that connection. If you want to change the isolation level for all transactions in the connection you need to use SET SESSION TRANSACTION ISOLATION LEVEL ...

Regards,

Heikki
[11 Oct 2006 12:30] Arkadiusz Miskiewicz
I'm running in AUTOCOMMIT=1 (which is default and I'm not changing it).

What I tried to do is to refresh some values taken by SELECT inside of transaction started in (also default) REPEATABLE READ by switching temporaily to READ COMMITTED, doing SELECT and switching back to REPEATABLE READ. This works but I need 2 selects instead of just one inside of read commited mode.

Do I understand correctly that you have problems with repeating the problem?
[11 Oct 2006 13:52] Heikki Tuuri
Arkadiusz,

::external_lock() in ha_innodb.cc in 5.0:
"
                if (trx->n_mysql_tables_in_use == 1) {
                        trx->isolation_level = innobase_map_isolation_level(
                                                (enum_tx_isolation)
                                                thd->variables.tx_isolation);
                }

...

        if (trx->n_mysql_tables_in_use == 0) {

                trx->mysql_n_tables_locked = 0;
                prebuilt->used_in_HANDLER = FALSE;

                /* Release a possible FIFO ticket and search latch. Since we
                may reserve the kernel mutex, we have to release the search
                system latch first to obey the latching order. */

                innobase_release_stat_resources(trx);

                if (!(thd->options & (OPTION_NOT_AUTOCOMMIT | OPTION_BEGIN))) {
                        if (trx->active_trans != 0) {
                                innobase_commit(thd, TRUE);
                        }
                } else {
                        if (trx->isolation_level <= TRX_ISO_READ_COMMITTED
                                                && trx->global_read_view) {

                                /* At low transaction isolation levels we let
                                each consistent read set its own snapshot */

                                read_view_close_for_mysql(trx);
                        }
"

InnoDB does calculate the isolation level at the start of each SQL statement, but it only closes the old read view (= snapshot of the database) at the END of the SQL statement. If the transaction already has an open read view when you issue SET TRANSACTION, then InnoDB will use it in the next SELECT ... . Only the second SELECT will get a fresh read view.

Hmm... you should set the isolation level at the START of the transaction. Then the code would work in the intuitive way.

We could make InnoDB to close the read view also at the start of an SQL statement if the isolation level is READ COMMITTED. Then it would work in a sensible way also if you downgrade the isolation level to READ COMMITTED in the middle of a transaction.

I am assigning this little fix to Sunny. This should be made in 5.1. We cannot change the behavior in 5.0, because some 5.0 application might depend on the old, undefined behavior.

This code:

                if (trx->n_mysql_tables_in_use == 1) {
                        trx->isolation_level = innobase_map_isolation_level(
                                                (enum_tx_isolation)
                                                thd->variables.tx_isolation);
                }

should be replaced with:

                if (trx->n_mysql_tables_in_use == 1) {
                        trx->isolation_level = innobase_map_isolation_level(
                                                (enum_tx_isolation)
                                                thd->variables.tx_isolation);

                        if (trx->isolation_level <= TRX_ISO_READ_COMMITTED
                                                && trx->global_read_view) {

                                /* At low transaction isolation levels we let
                                each consistent read set its own snapshot */

                                read_view_close_for_mysql(trx);
                        }
               }

and tested carefully.

Thank you,

Heikki
[11 Oct 2006 14:54] Guilhem Bichot
Note that the SQL:2003 standard (at least in its draft version, don't know about the final one) forbids downgrading the transaction isolation level (like from REPEATABLE READ to READ COMMITTED) if the transaction has already started.
[11 Oct 2006 15:16] Heikki Tuuri
Guilhem,

hmm... we may want to deviate from the ANSI standard, because it does make sense to downgrade the InnoDB isolation level also during a running transaction.

If MySQL wants to give a warning or error in SET TRANSACTION ISOLATION LEVEL, that has to happen on the MySQL interpreter layer.

Regards,

Heikki
[8 Nov 2006 16:04] Heikki Tuuri
The code in 5.1 is different.

::store_lock() in 5.1:
        row_prebuilt_t* prebuilt        = (row_prebuilt_t*) innobase_prebuilt;
        trx_t*          trx;

        /* Note that trx in this function is NOT necessarily prebuilt->trx
        because we call update_thd() later, in ::external_lock()! Failure to
        understand this caused a serious memory corruption bug in 5.1.11. */

        trx = check_trx_exists(ht, thd);

        /* NOTE: MySQL can call this function with lock 'type' TL_IGNORE!
        Be careful to ignore TL_IGNORE if we are going to do something with
        only 'real' locks! */

        /* If no MySQL table is in use, we need to set the isolation level
        of the transaction. */

        if (lock_type != TL_IGNORE
        && trx->n_mysql_tables_in_use == 0) {
                trx->isolation_level = innobase_map_isolation_level(
                                                (enum_tx_isolation)
                                                thd->variables.tx_isolation);
        }

###############

I think the fix in 5.1 is to add inside the last 'if' block above:

                        if (trx->isolation_level <= TRX_ISO_READ_COMMITTED
                                                && trx->global_read_view) {

                                /* At low transaction isolation levels we let
                                each consistent read set its own snapshot */

                                read_view_close_for_mysql(trx);
                        }
[9 Nov 2006 14:26] Heikki Tuuri
This is a duplicate of http://bugs.mysql.com/bug.php?id=21409