Skip to Main Content

SQL & PL/SQL

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!

Locking needed for concurrent access to one row

521317Jul 3 2006 — edited Jul 6 2006
This seems simple enough, but let me throw it out there to make sure I am not missing something.
I have only one row and one column in a table that will be modified concurrently by multiple threads. SERVER_AVAILABILITY.USED_TASKS is the table/column.

let's say USED_TASKS starts out as int value 0. Here is what my program does:

2 threads are fired off at the same time.

Thread 1: iterates through a for loop 1000 times and increments USED_TASKS by one each time
Thread 2: iterates through a for loop 1000 times and decrements USED_TASKS by one each time

If everything runs correctly, the net result after this operation is that USED_TASKS is still at 0; however, that is not happening. I always end up with some positve or negative value (normally ranging between -+10).

I am using a java client via jdbc connections. Oracle 10g, autocommit is true, transaction isolation level is READ_UNCOMMITTED (but this shouldn't matter since I am manually locking the row).

My sql for the two methods are as follows (there is only one row in this table):

decrementing:
1. select used_tasks from server_availability for update
2. update server_availability set used_tasks=(select used_tasks - 1 from server_availability for update)

incrementing:
1. select used_tasks from server_availability for update
2. update server_availability set used_tasks=(select used_tasks + 1 from server_availability for update)


any ideas? I thought the connection trying to access the row would wait until it was no longer being blocked, but that does not appear to happen on at least a few occassions.

thanks in advance,
Terrance
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 3 2006
Added on Jul 3 2006
10 comments
1,162 views