“My reaction was mainly against your specific wording…”
I accept.

“at the end of the day, the isolation levels have got nothing to do with data integrity…”
Consider: if I were to INSERT some data depending on current state (i.e. depending on SELECT resukt), then by reading uncommitted data I lose integrity. Maybe it’s more of application integrity, not database integrity.

Shlomi

nice to hear your working on that tool! I’ll be glad to check it out.

Now, my main point re. transaction isolation levels is not so much whether it is or is not standard. My reaction was mainly against your specific wording:

“But MySQL, and through it, InnoDB, allow a strange thing”

which to me seemed to imply it is specific to MySQL. It is not – standard SQL allows it.

More importantly though, at the end of the day, the isolation levels have got nothing to do with data integrity. Even though you can see inconsistent states if you really want to, you cannot commit one. Hence, it is not a data integrity issue – at least not from the POV of the database as a whole.

The relaxed isolation levels were never born as a feature. What happened is that people discovered it is pretty hard to implement full ACID and at the same time not kill performance (that said I don’t think that it matters a lot for InnoDB as long as you do not use SERIALIZABLE). Relaxing isolation is simply a way to allow some things to be a lot faster, so the practical solution is to quantize levels of isolation. As long as no data integrity is violated at commit time, I cannot see the flaw in it.

kind regards,

Roland

Shlomi

#1
I’ve also got a utility which relates to foreign keys, and which DELETEs or does a SET NULL upon violation. It’s part of a small toolkit I will soon release.

#2
“It’s not confined to MySQL or InnoDB – it’s defined in the standard.”
That’s interesting, I didn’t know that. But it doesn’t change anything. Many things in the standard are not implemented in MySQL, and vice versa.

“start transaction statement ::= START TRANSACTION [transaction characteristics]”
In MySQL it goes by SET TRANSACTION …

While looking at it, I was surprised to encounter the following statements:
START TRANSACTION WITH CONSISTENT SNAPSHOT;
COMMIT AND CHAIN;
Wasn’t aware of their existence, they were new to me.

Shlomi

There are several bug reports for sql_mode, none that I know of for FOREIGN_KEY_CHECKS, and I’m not sure how to approach the tx_isolation issue, as this is clearly a feature, not a bug 🙁

I’ll try and make a good argument and file a bug on the FOREIGN_KEY_CHECKS issue.

#1
“But I don’t think normal users should be allowed to set the FOREIGN_KEY_CHECKS variable. This should be restricted to users with the SUPER privilege.”

I think this is only partially a matter of privileges. IMO, the gravest problem with disabling foreign key checks is that re-enabling does not check for violations. The second biggest beef I have with it is that it is all-or-nothing. I would like to do it on a constraint level, or otherwise table level. IMO, those 2 are the real WTFs.

I would like for an Oracle-like ENABLE/DISABLE syntax on a constraint level. Then, the privilege system could be enhanced to allow or disallow the user doing ENABLE/DISABLE for particular constraints (the ones that the user is in charge of)

BTW – I have a utility that may be of help checking for violations:
http://forge.mysql.com/tools/tool.php?id=11

#2 “it undermines the very foundation of using transactions.”

I don’t agree. Reading uncommitted data does not do anybody harm – that is nobody but the current transaction. And even then the worst thing that can happen is that the current transaction thinks it can commit some inconsistent data, which is subsequently refused by the server. Data integrity is thus never compromised. (by this I mean that no database constraint will ever be violated, the only thing that might happen is that some transactions may run into an error more often, but I can’t feel that is a data integrity problem, just an application problem).

#3 “But MySQL, and through it, InnoDB, allow a strange thing: the transaction isolation level can be modified on the run.”

It’s not confined to MySQL or InnoDB – it’s defined in the standard.

start transaction statement ::= START TRANSACTION [transaction characteristics]
transaction characteristics ::= transaction mode*
transaction mode ::= isolation level | transaction access mode | diagnostics size

(See iso 9075:2003, 16.8)