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)?
Yes. Of course I would do something like:
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?
Originally posted by Pho Tek:
I just found this transaction concurrency blogpost which confirms Campbell's hunch.
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.
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 ]
Let's suppose I want to create a table to store transaction logs.
Most database already ship with transaction logs. Could you not just use that? ALternatively, something like a log4j appender would do the same job.
Since I don't care whether the JDBC write is transactional
So, loosing data is acceptable? If this is the case, I'm not sure I'd bother with a database (unless you have some other requirement that makes this necessary?).
[ October 23, 2008: Message edited by: Murthy Tanniru ]