MySQL Bugs: #46932: SET TRANSACTION ISOLATION LEVEL documentation wrong

Bug #46932	SET TRANSACTION ISOLATION LEVEL documentation wrong
Submitted:	26 Aug 2009 8:45	Modified:	31 Aug 2009 9:13
Reporter:	Sheldon Hearn	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.0.32	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
Empirical testing shows that the following documentation is wrong:

"Without any SESSION or GLOBAL keyword, the statement sets the isolation level for the next (not started) transaction performed within the current session."

It's good that the documentation is wrong, because that would be a violation of the SQL standard.

The statement applies to the _current_ transaction on MySQL, as is the case on MS SQL, Oracle and PostgreSQL.

How to repeat:
-- On node writer:
-- ---------------
 
DROP TABLE test;
create table test (id int auto_increment primary key) engine=InnoDB;
set autocommit=0;
begin;
set transaction isolation level read committed;
 
-- On node reader:
-- ---------------
 
set autocommit=0;
begin;
set transaction isolation level read committed;
 
-- On node writer:
-- ---------------
 
INSERT INTO test VALUES ();
 
-- On node reader:
-- ---------------
 
SELECT * from test;
-- Does not return any rows; the documentation suggests that it would.

Suggested fix:
bring  the documentation inline with the existing, standards-compliant behaviour.

I agree that this page, http://dev.mysql.com/doc/refman/5.1/en/set-transaction.html, and corresponding manual pages for other versions needs clarification and more examples of how SET TRANSACTION can be used in MySQL.

This is not a docs bug.

The manual correctly states the intent of MySQL implementation. Bug#20837 indicates that the intent is not correctly implemented. The real problem is server Bug#20837.

SQL standard (2003, part II Foundations, section 16.2 <set
transaction statement>, subsecion "General Rules") says:

1) Case:
   a) If a <set transaction statement> that does not specify
      LOCAL is executed, then
      Case:
      i) If an SQL-transaction is currently active, then
        an exception condition is raised: invalid
        transaction state - active SQL-transaction.

As you can see, changing the isolation level of the current
transaction is not standard.

So, this phrase on SET TRANSACTION page:

"Any session is free to change its session isolation level (even in the middle of a transaction)"

in documentation described desired behavior of MySQL server? We are really going to allow change of isolation level in the middle of transaction using SET TRANSACTION statement?

Yikes.

Thanks for clarifying.  So it's only de facto standard behaviour.

Nevertheless, I'd encourage you to leave the existing behaviour as is, and update the documentation.  This is the path of least disruption, since it's unlikely that there's code out there that believes the documentation, in stubborn denial of the reality of the code. :-)

But that's the strongest argument I can offer in favour of the existing behaviour, since de facto standards aren't conclusive motivators.

Valeriy, this paragraph means that you can change the *session* isolation level, even in the middle of transaction. But it won't affect the isolation level of this transaction. It only specifies what value future transactions will use.