If multiple threads access a MySql table from different JVMs; can normal JDBC guarantee transactional behaviour with autocommit off ? Or do I have to implement optimistic transaction detection myself ? Thanks
Don't know. But it looks like the sort of situation where the simplest answer is "no." If you have two threads accessing the database simultaneously then you lose the "A" part of "ACID" because your transaction is no longer "A"tomic.
If you have two threads accessing the database simultaneously then you lose the "A" part of "ACID" because your transaction is no longer "A"tomic.
Two threads should equal two transactions shouldn't it? I'd be surprised if a database handled multiple threads in the context of one session (that is, assuming the multiple threads don't also share the same connection)?
Of course if I want ABSOLUTE atomicity, I might as well just set it to SERIALIZABLE isolation level (which basically amounts to single threaded processing) and will not support 10000 concurrent requests.
If this is not possible, does it mean I need to use a JTA manager like JOTM etc..
But if you simply turn autocommit off they might interfere with each other. Obviously if you say start transaction; . . . commit; that will restore the "A" in ACID.
Hmm. I would hope in this instance the database would commit nothing. Assuming again that the two threads are not sharing Connection, and you are using read commited transaction isolation or above would either thread be able to perform a dirty read or commit any changes? They should be (implicitly) using seperate transactions which they never commit. It could be the the behaviour is different in MySQL?
This appears to be about a different cirecumstance that that both I and Campbell have understood you to mean. If you have two seperate threads using two seperate connections you can get lost updates - this is understood behaviour, and you use a locking strategy to ensure this doesn't happen. However, the transactional behaviour of the application is fine - one transaction didn't interfere with the other, both were successful, one just over wrote the other. With autocomit on or off this behaviour is the same.
Joined: Oct 13, 2005
Agree; whether you have autocommit on or off, "start transaction" turns it off, and commit commits the changes.
Let's suppose I want to create a table to store transaction logs. The TxLog table will just be a standalone table without relationships to other tables.
Since I don't care whether the JDBC write is transactional (I will never be bothered by lost updates), should I: a) set autocommit to true. b) choose a non transactional table type (MyISAM instead of Innodb).
What other JDBC options can I enable to ensure that I can get the best write performance ? [ October 22, 2008: Message edited by: Pho Tek ]
This may be similar case related to cluster. If the same data is added to the database with out proper keys(may be composite) the data gets added to the database and the insert will fail if try to add that is already inserted [ October 23, 2008: Message edited by: Murthy Tanniru ]