Description:
I'm building an object persistence module for perl. Among other things, my
test suite tests transaction integrety. I currently do no caching and depend
on the underlying database engine to provide transactional integrety. I use
"SET TRANSACTION ISOLATION LEVEL SERIALIZABLE".
With mysql 4.0.16 and 4.0.18 (the only versions I tested) I had consistent,
though non-deterministic, transaction failures.
I found a fix: I stopped using SERIALIZABLE and instead added "LOCK IN SHARE
MODE" to every one of my SELECTs.
According to the documentation, SERIALIZABLE == REPEATABLE READ + LOCK IN
SHARE MODE, but my experience says otherwise. The latter provides isolation
between my transactions and the former doesn't.
This report largely duplicates bug #705, but #705 is listed as
non-critical/low and the only response to it is a suggestion that it was user
error.
How to repeat:
Repeat this by running the test suite of my perl module from before I found
the LOCK IN SHARE MODE workaround.
Get it from:
ftp://ftp.idiom.com/users/muir/bugstuff/OOPS-0.1001-mysql-broken.tar.gz
Run the tests t/tran1.t and t/tran5.t. They'll both fail some of the time.
As I write this my module, OOPS, has not yet been released. I anticipate
a release within 24 hours. Once it is released, it will be findable on CPAN
or from:
ftp://ftp.idiom.com/users/muir/CPAN/modules/OOPS-*.tar.tz
Suggested fix:
Short term: put a warning in the documentation: SERIALIZABLE doesn't.
