MySQL Bugs: #14954: Inserting row is blocking other connections

Bug #14954	Inserting row is blocking other connections
Submitted:	15 Nov 2005 17:00	Modified:	21 Aug 2006 22:19
Reporter:	Hakan Küçükyılmaz	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Server: BDB	Severity:	S2 (Serious)
Version:	5.0.17-BK, 5.0.16	OS:	Linux (Linux)
Assigned to:	Alexey Botchkov	CPU Architecture:	Any

Description:
Inserting a row in a connection is blocking other connections, even if transaction isolation level is set to READ-UNCOMMITTED.

How to repeat:
Connection 1:
root@test>drop table if exists t1;
Query OK, 0 rows affected (0.00 sec)

root@test>create table t1 (a int) engine berkeleydb;
Query OK, 0 rows affected (0.12 sec)

root@test>set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

root@test>set tx_isolation="READ-UNCOMMITTED";
Query OK, 0 rows affected (0.00 sec)

root@test>insert into t1 values (1);
Query OK, 1 row affected (0.07 sec)

Connection 2:
root@test>set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

root@test>set tx_isolation="READ-UNCOMMITTED";
Query OK, 0 rows affected (0.00 sec)

root@test>select * from t1;
 ... this statement waits till Connection 1 commits or rollbacks

Connection 1: --> ROLLBACK
root@test>rollback;
Query OK, 0 rows affected (0.00 sec)

Connection 2:
root@test>select * from t1;
Empty set (28.84 sec)

Suggested fix:
Connection 2 should see the changes of connection 1.

When running this example in READ-COMMITTED connection 2 still waits on connection 1 to finish. In READ-COMMITTED connection 2 should get an empty set.

Thank you for a problem report. Looks like a feature request for me, but need some check.

Please, send the results of the

show variables like 'bdb%';

stetement. Just to be sure when verifying...

root@test>show variables like 'bdb%';
+---------------------+--------------+
| Variable_name       | Value        |
+---------------------+--------------+
| bdb_cache_size      | 8388572      |
| bdb_home            | /data/mysql/ |
| bdb_log_buffer_size | 2097152      |
| bdb_logdir          |              |
| bdb_max_lock        | 10000        |
| bdb_shared_data     | OFF          |
| bdb_tmpdir          | /tmp/        |
+---------------------+--------------+
7 rows in set (0.00 sec)

Verified just as described on 5.0.17-BK (ChangeSet@1.1969, 2005-11-17 15:08:49+01:00, ...) on Linux. 

The result (hang of session 2 untill transaction finished in session 1) does not depent on bdb_shared_data setting (as I though for some reason initially...)

I suspect that the Bdb Database (table) is not being opened with the DB_READUNCOMMITTED
flag.  This is required for database handles which are used for updates that will be concurrent with uncommitted reads.  Please see:
http://www.sleepycat.com/docs/api_c/db_open.html#DB_READ_UNCOMMITTED

Note that the patch for supporting READ COMMITTED is still pending, as part of Bug #15541.

Someone needs to decide if all BDB tables should be opened using DB_READUNCOMMITTED. There is some performance penalty involved in doing so.