LOCK_MODE=1 (Serializable) is not working when MVCC=true

[GoogleCodeExporter]

Short description:

LOCK_MODE=1 (Serializable) is not working whet MVCC=true and looks like 
LOCK_MODE=3 (Read Committed)

> What steps will reproduce the problem?
> (simple SQL scripts or simple standalone applications are preferred)

1. Prepare sample database:
CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255));
INSERT INTO TEST VALUES(1, 'Hello');

2. Open console 1, uncheck autocommit, execute:
SELECT * from
GOT: Hello

3. Open console 2, execute:
UPDATE TEST SET NAME='Hello1' WHERE ID=1

4. In console 1 exetute:
SELECT * from
GOT: Hell1

> What is the expected output? What do you see instead?

expected: table 2 must be loked for read by step 2 (Serializable: Both read 
locks and write locks are kept until the transaction commits).

> What version of the product are you using? On what operating system, file
> system, and virtual machine?

Version 1.3.150 (2011-01-28), Beta

> Do you know a workaround?

No.

> How important/urgent is the problem for you?

At least it soult be documented...

> In your view, is this a defect or a feature request?

It is a defect

Original issue reported on code.google.com by belankov@gmail.com on 31 Jan 2011 at 11:17

[GoogleCodeExporter]

And LOCK_MODE=0 (read uncommited) looks like LOCK_MODE=3 (Read Committed).

Original comment by belankov@gmail.com on 31 Jan 2011 at 11:28

[GoogleCodeExporter]

Hi,

Yes, this is not documented, I will do that:

"If MVCC is enabled, changing the lock mode (LOCK_MODE) has no effect."

I'm afraid supporting serializable mode when using MVCC is not so easy with the 
current MVCC implemenation.
(As far as I know, other databases such as Oracle and PostgreSQL also don't 
support the serializable mode, but I'm not sure).

Regards,
Thomas

Original comment by thomas.t...@gmail.com on 1 Feb 2011 at 7:35

[GoogleCodeExporter]

Hello, Thomas!

Ok, I think, we should keep in mind two cases:

Case 1. Without MVCC: "Read commited" vs "Serializable"
Case 2. With MVCC: "Read commited" vs "Repeatable read"

Original comment by belankov@gmail.com on 2 Feb 2011 at 12:22

[GoogleCodeExporter]

The plan is to use MVCC read committed in the future by default. I don't plan 
to support serializable.

Original comment by thomas.t...@gmail.com on 28 Jul 2013 at 9:37

• Changed state: WontFix