Bug #22856 "start transaction" syntax: add isolation level
Submitted: 30 Sep 2006 9:41 Modified: 30 Sep 2006 12:45
Reporter: Maciej Pilichowski Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: General Severity:S4 (Feature request)
Version:5.0.24a OS:Linux (opensuse 10.0)
Assigned to: CPU Architecture:Any
Triage: Triaged: D5 (Feature request)

[30 Sep 2006 9:41] Maciej Pilichowski
Description:
Such enhacenment would be great for people who moved to MySQL from other databases but also to make MySQL more SQL compliant.
I am talking here about simple extension

For example
start transaction serializable
would be equivalent to
set transaction isolation level serializable
start transaction

I make too many mistakes -- I set transaction level and forget to start it! 

Btw.
start trans = start transaction
would be welcome too :-)

How to repeat:
This is a wish, not a bug.

Suggested fix:
This is a wish, not a bug.
[30 Sep 2006 12:18] Valeriy Kravchuk
Thank you for a feature request. First of all, you have to do:

start transaction;
set transaction isolation level serializable;

to follow SQL standard. Same for Oracle, Infromix etc. And as "set transaction isolation level" is already implemented, and you have a way to set default isolation level for all transactions, why should MySQL implement something else, non standard?

I do not think that this feature should be implemented.
[30 Sep 2006 12:34] Maciej Pilichowski
> Thank you for a feature request. First of all, you have to do:
> start transaction;
> set transaction isolation level serializable;

Hmm, did you check the doc
"This statement sets the transaction isolation level for the next transaction, globally, or for the current session."

This is about "set transaction" -- next <> current. I checked it in mysql, works the way it is described in doc, so I believe you are wrong.

> to follow SQL standard. 

I don't have any official document about SQL, but I have pretty decent book about SQL. And syntax of "start transaction" is richer.

> Same for Oracle, Infromix etc. 

Maybe, I don't know those DB, but AFAIR MSSQL conforms the richer syntax, i.e. you can execute:
start transaction serializable;
for example

> And as "set
> transaction isolation level" is already implemented, and you have a way
> to set default isolation level for all transactions, 

I know, but I am not talking about _all_ transactions, but next.

> why should MySQL
> implement something else, non standard?

1) the non-standard is questionable, based on books&docs I have, this is standard "start transaction level_of_isolation"
2) why not add some decent features even non-standard (see (1))
3) MySQL has already some non-standard features, is it wrong?
[30 Sep 2006 12:39] Valeriy Kravchuk
OK, let me check...
[30 Sep 2006 12:45] Valeriy Kravchuk
Sorry, I was wrong with previous answer, based on some ideas from the top of my head. According to SQL 2003 Standard (draft, but anyway):

"<start transaction statement> ::=
START TRANSACTION
[ <transaction mode> [ { <comma> <transaction mode> }... ] ]
<transaction mode> ::=
<isolation level>
| <transaction access mode>
| <diagnostics size>
<transaction access mode> ::=
READ ONLY
| READ WRITE
<isolation level> ::= ISOLATION LEVEL <level of isolation>
<level of isolation> ::=
READ UNCOMMITTED
| READ COMMITTED
| REPEATABLE READ
| SERIALIZABLE
<diagnostics size> ::= DIAGNOSTICS SIZE <number of conditions>
<number of conditions> ::= <simple value specification>"

you are absolutely right. Thank you for a reasonable feature request.