| Bug #33282 | broken consistency when read-committed or innodb_locks_unsafe_for_binlog | ||
|---|---|---|---|
| Submitted: | 17 Dec 2007 6:00 | Modified: | 17 Dec 2007 16:42 |
| Reporter: | Yasufumi Kinoshita | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server | Severity: | S1 (Critical) |
| Version: | 5.1.22-rc | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[17 Dec 2007 11:03]
Domas Mituzas
Seems to be reincarnation of Bug#31310
[17 Dec 2007 13:50]
Heikki Tuuri
Yasufumi, thank you again for a very good bug report! Trx 2 should return with a 'semi-consistent read' to MySQL the latest committed state of the row, which is (1, 0). MySQL sees that the UPDATE statement SHOULD update that. MySQL should reissue the UPDATE for that row, this time doing a normal locking read. That is, trx 2 should wait for trx 1 to commit. This may be a bug in MySQL's SQL interpreter like Bug#31310. MySQL probably fails to reissue the UPDATE for the row.
[17 Dec 2007 16:42]
Susanne Ebrecht
Many thanks for writing a bug report. This is a duplicate of bug #31310.
Description: UPDATE seems to skip locked record must wait for, while using read-committed or innodb_locks_unsafe_for_binlog. How to repeat: <DDL> use test; CREATE TABLE t1 ( a int(6) PRIMARY KEY, b int(6) ) ENGINE = innodb; INSERT INTO t1 (a, b) VALUES(1, 0); <Transaction 1> set tx_isolation='READ-COMMITTED'; set autocommit=0; use test; update t1 set b = b + 1 where a = 1; <Transaction 2> set tx_isolation='READ-COMMITTED'; set autocommit=0; use test; update t1 set b = b + 1 where a = 1; /* This must wait for commit of Transaction 1 */ But... ======================= mysql> update t1 set b = b + 1 where a = 1; Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0 ======================= The record seems to be skipped. <Transaction 1> commit; <Transaction 2> commit; <Check> select * from t1; /* 'b' must be '2' */ But... ======================= mysql> select * from t1; +---+------+ | a | b | +---+------+ | 1 | 1 | +---+------+ 1 row in set (0.00 sec) ======================= Suggested fix: In this condition, I think we shouldn't call row_sel_build_committed_vers_for_mysql() row0sel.c (reference): switch (err) { rec_t* old_vers; case DB_SUCCESS: break; case DB_LOCK_WAIT: if (UNIV_LIKELY(prebuilt->row_read_type != ROW_READ_TRY_SEMI_CONSISTENT) || index != clust_index) { goto lock_wait_or_error; } /* The following call returns 'offsets' associated with 'old_vers' */ err = row_sel_build_committed_vers_for_mysql( clust_index, prebuilt, rec, &offsets, &heap, &old_vers, &mtr);