Locking needed for concurrent access to one row
521317Jul 3 2006 — edited Jul 6 2006This 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