MySQL Bugs: #22847: Falcon: Unnecessary blockage for nulls

Bug #22847	Falcon: Unnecessary blockage for nulls
Submitted:	29 Sep 2006 22:22	Modified:	20 May 2007 5:36
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S3 (Non-critical)
Version:	5.1.12-beta-debug	OS:	Linux (SUSE 10.0 / 64-bit)
Assigned to:	Kevin Lewis	CPU Architecture:	Any

Description:
Suppose I have two connections, T1 and T2.
Suppose I have a table T, which has a unique index on its only column.
I have noticed that, if T1 and T2 both try to insert "equal" row in
the table, one of them is blocked. But if they're both trying to insert
rows which are not "equal", neither is blocked, there would be no duplication.
Since two NULLs are not "equal", I expect no blocking.
But there is blocking.
 

How to repeat:
On T1, say:

create table t23 (s1 int, unique (s1)) engine=falcon;
start transaction;
insert into t23 values (null);

On T2, say:
start transaction;
insert into t23 values (null);

Observe that T2 is blocked, waiting for T1 to finish.

Verified on Linux 32-bit with change set 1.2362, 2006-10-02.
Added test case falcon_bug_22847.test

Regards, Hakan

falcon_bug_22847.test still hangs with latest change set 1.2411, 2007-01-27.

Regards,

Hakan

This unneccessary wait for concurrent changes can happen for concurrent additions to a unique index whenever there is a hit in the index due that turns out not to actually be a duplicate visible to the transaction. In checkUniqueIndexes(), we don't want to wait on an active transaction when the scanIndex found a hit but isDuplicate() says it's not.

This can happen for for concurrent null values in a unique index since they are considered equal when searching, but are not equal when looking for duplicates.

This can also happen when key value was committed after a repeatable read transaction started. The scanIndex will find the unique key value, but isDuplicate will determine that it is not visible.

However, we cannot always avoid the wait then the pending record is not the duplicate but an older committed record version is a duplicate.

If the caller of checkUniqueIndexes() is Read Committed, the pending record is not a duplicate and the committed record is, we still need to wait on that active transaction before knowing for sure if the comitted record will stay that way.

However, if the caller of checkUniqueIndexes() is Repeatable Read, the pending record is not a duplicate and the committed record is, then we do NOT need to wait, since the older committed record will be the visible record whether the pending record committs or not.

The changes are focused on the function checkUniqueIndexes() in table.cpp. But I also added a flag to Transaction::getRelativeState() to determine whether to wait or not.

In addition, I added the new 'InnoDB compatibility mode' as a new transaction type called TRANSACTION_WRITE_COMMITTED. It generally acts like Repeatable Read when reading, but Read Committed when writing. At present, there is no way to turn it on.

falcon_bug_22847 passes now

TEST                           RESULT         TIME (ms)
-------------------------------------------------------

falcon_bug_22847               [ pass ]            425
-------------------------------------------------------
Stopping All Servers
All 1 tests were successful.
The servers were restarted 1 times
Spent 0.425 seconds actually executing testcases

Added two more test cases to falcon_bug_22847.test which illustrate another aspect of this work.  The general principle is that a client should not wait on another transaction unless the record version pending for that transaction will have an affect on the uniqueness of a new key value.  So there are differences between READ-COMMITTED and REPEATABLE-READ isolation levels.  When a REPEATABLE-READ transaction will not see a pending change which would, if committed, allow the new key value to be unique, then the pending non-duplicate value does not matter.  only the visible key values matter to a REPEATABLE-READ transaction.  READ-COMMITTED transactions are more concerned with pending values.

A note has been added to the 6.0.1 changelog.