• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

JDBC Transaction

 
Pho Tek
Ranch Hand
Posts: 782
Chrome Python Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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

Regards,

Pho
 
Campbell Ritchie
Sheriff
Posts: 51368
87
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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)?
 
Campbell Ritchie
Sheriff
Posts: 51368
87
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Pho Tek
Ranch Hand
Posts: 782
Chrome Python Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
@Campbell,

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..

Regards,

Pho
 
Pho Tek
Ranch Hand
Posts: 782
Chrome Python Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I just found this transaction concurrency blogpost which confirms Campbell's hunch.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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?
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Campbell Ritchie
Sheriff
Posts: 51368
87
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Agree; whether you have autocommit on or off, "start transaction" turns it off, and commit commits the changes.
 
Pho Tek
Ranch Hand
Posts: 782
Chrome Python Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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?).
 
Murthy Tanniru
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic