File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes JDBC Transaction Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "JDBC Transaction" Watch "JDBC Transaction" New topic
Author

JDBC Transaction

Pho Tek
Ranch Hand

Joined: Nov 05, 2000
Posts: 761

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

Joined: Oct 13, 2005
Posts: 38788
    
  23
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

Joined: Apr 14, 2004
Posts: 10336


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)?


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 38788
    
  23
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

Joined: Nov 05, 2000
Posts: 761

@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

Joined: Nov 05, 2000
Posts: 761

I just found this transaction concurrency blogpost which confirms Campbell's hunch.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


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

Joined: Apr 14, 2004
Posts: 10336

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

Joined: Oct 13, 2005
Posts: 38788
    
  23
Agree; whether you have autocommit on or off, "start transaction" turns it off, and commit commits the changes.
Pho Tek
Ranch Hand

Joined: Nov 05, 2000
Posts: 761

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

Joined: Apr 14, 2004
Posts: 10336


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

Joined: Jun 03, 2008
Posts: 14
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 ]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: JDBC Transaction