"carlo" <spammer.rea...@googlemail.com> wrote:

> Have I found a serious error in mysql's handling of transactions and
> threads?

> No. You detected your ignor^Wmisunderstanding of MySQL :-)

Thanks for your reply, but lets put your comment to the test.. ! ;-)

> the code spawns two threads which each reads a column, decrements it by
> 1 and writes it back... unfortunately both threads reads e.g. 10 and
> writes 9... clearly the end result should have been 8! Any comment is
> welcome

1. Transactions are supported for InnoDB tables only. You do not
specify the ENGINE in CREATE TABLE. What is the default engine in
your MySQL installation? (the default default is MyISAM)


The table is a innoDB, from version 5 of mysql, as default innoDB is
used.


2. Nobody does SELECT + decrement + UPDATE separately. Just use
UPDATE stock SET physical=physical-1 WHERE id=1
This is atomic and works across all engines.

Of course you just did a test. But keep in mind: atomic operations
save some locks and avoid possible deadlock situations.

yes, that is all nice, but the fact is that such updates are not always
possible. sometimes updates do require many selects and various
interactions with user input which is hard to place in one
transaction.. such as getting user input on several screens before the
update... I just try to simulate real-life situation.



3. You connect to the DBMS as superuser. Never do that (except for
administrative tasks)!

I can hardly believe that should change the functionality of the
transactions... and no my password is not root on my machine!



4. You INSERT into a table without a column list. This is dangerous!
Especially if CREATE TABLE and INSERT are in different parts of the
code. You might change one and forget the other.

its just setting up the test code...



5. Your cut&paste is broken. Class User does not have a start() method.
I guess you meaned run().

no, in java you invoke start() rather than run. Otherwise you do not
get a parallel execution of the code.



> Axel Schwenke, Senior Software Developer, MySQL AB

Are you working for mysql? I think you should have a serious look at
the jdbc driver implementation for various transactions levels... both
the v3 and v5 drivers are flawed!