Skip to Main Content

Java Database Connectivity (JDBC)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

JDBC Update Batching - When does it commit?

452924Sep 9 2005 — edited Sep 10 2005
Sorry for the cross-posting, but I think I posted this in the wrong forum before.

When Oracle receives a batch of updates from prepared statements. Does it execute each one seperately, acquiring the lock, changing the data, releasing the lock, then going to the next statement in the batch?

Or does it acquire the locks for all the statements before it begins executing?

We are wondering if there is anyway a deadlock can occur with JDBC Update Batching?

For example if all locks are acquired before updates are performed:

One session has batched updates on rows 5, 6, 1, and 3.
Another session has batched updates on rows 3, 6, 5, and 1.

Both sessions execute their batches at about the same point in time.

Oracle starts with the first session.
Session 1 locks row 5, and 6.
Then there's a context switch to session 2.

Session 2 locks row 3 and stops because it can't lock row 6.
There's a context switch to session 1.

Session 1 locks row 1 and then blocks because it can't get lock 3.
Then there's a deadlock.

I think it is unlikely that Oracle would acquire all locks before executing, but I need to verify this. I can't find any documentation about this issue.

In the other forum, they mentioned that it has to do with when Oracle commits the statement. So I guess the real question is: Does Oracle commit after every item in a batched update or does it commit after the entire batch is completed?

Thanks for your help.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 8 2005
Added on Sep 9 2005
1 comment
1,166 views