Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 784
  • Last Modified:

Concurrent access to Oracle - Locking problems

Hi experts,

I'm writing a process that will run in parallel in many app server instances. Al processes are equal.
Each process needs to read X rows from a table and then process them. No row should be processed twice (or more than once).  At the same time this processes read the table, anothe process adds new rows to it, so no full-table-locking mechanism can be used. Each row has a unique ID and i can add as many columns as needed.

Since processes do not communicate with each other i'm worried about locking issues. Im using Oracle DB 9.0.2.6

What is the best way to guarantee that no row will be processed twice and to avoid deadlocks and minimize locking?

I tried using ISOLATION LEVEL SERIALIZABLE but it doesnt work because i can't add new rows while selecting, and if 2 processes select at the same time then a "cant serialize.." error occurs.

I wanted to try
update mytable set process_id_mark = <the_current_process_id> where process_id_mark is null and rownum < X

and then...

select * from mytable where process_id_mark = <the_current_process_id>

but someone told me that solution has deadlock problemas.

We were also considering using an extra table as a "semaphore". This table would have the last ID processed and all processes would look there for the "first" id to get. We can guarantee this way that the database is consistent, but the coding process is much more complicated (because of retries and possible failures, etc.)

Thanks in advance
Sotretus.
0
anbernas
Asked:
anbernas
  • 6
  • 5
  • 4
2 Solutions
 
cjjcliffordCommented:
use "SELECT ... FOR UPDATE" to get the rows for processing, and then update these on completion. This locks the rows selected against other updates until the session that did the SELECT FOR UPDATE commits...

I would suggest that you add a column that is a flag to say whether the row is currently being processed, and the SELECT FOR UPDATE... holds the lock long enough to just update that flag column. Then the selects themselves should take this flag into consideration (i.e. don't select rows that have been tagged for work already!). This way the length of the lock will be minimised by the application itself can manage longer periods of concurrency....
0
 
anbernasAuthor Commented:
Someone in the team says that SELECT...FOR UPDATE and UPDATE both locks BLOCKS of rows, and that is what causes the deadlock. If it locked ONLY rows i think there should be no problem with any of them.
0
 
cjjcliffordCommented:
the Oracle documentation says that it locks the rows, but this could be implemented as block lock... however, if you use the approach of using this lock just to update a flag attribute that your application then uses for "locking" the possibility of deadlock will be minimised.
Also, you could use SELECT ... FOR UPDATE NO WAIT" which should return immediately if a lock cannot be achieved...
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
anbernasAuthor Commented:
But that will require a complex fail/retry mechanism. Isn't there a way that Oracle Guaranties no collisions???
0
 
schwertnerCommented:
cjjclifford  is correct!!!!

Oracle locks only the rows. Blocks have header part and this header part is used to isolate the locked rows.

Collision of course will happen. You can not avoid them if you use locks. This is like the delays if there are traffic lamps. You can not avoid delays and the only way to avoid the delay is to cross on red. If you really experience delay you have to think how to speed up your applications that locks rows - this is the key for avoiding delays.
0
 
anbernasAuthor Commented:
I dont want to avoid collisions, just minimize them. I want to avoid deadlocks. I was told that using an UPDATE can cause deadlocks, but if Oracle is locking rows and not blocks then that doesn't seem possible.
0
 
schwertnerCommented:
No, it can cause deadlocks.
It can cause deadlocks only if there is a second wave of SELECT which ask records holded by other application. And the other application waits for the records locked by the first SELECT ...FOR UPDATE.
there is an easy way to understand if some of the rows asked by SELECT FOR UPDATE are locked or not:


select something from your_table for update nowait;

If it rise error -54 you know that the selected rows are in use.

Notify the user for that.
0
 
cjjcliffordCommented:
That's why I suggested making the application use a separate column to decide state, and use SELECT...FOR UPDATE to lock this column and use this column as a mutex, e.g. add a "FLAG" column to the data table, have the application immediately update the flag to indicate "being-processed", and committing this change to free the row locks - the application will then have to ensure that it only tries to update/lock rows that are not already flagged... This will minimise the duration that any row is locked, but the application itself will become a little more complex....
 
0
 
anbernasAuthor Commented:
But SELECT...FOR UPDATE guarantees no deadlocks will happend?
0
 
cjjcliffordCommented:
the deadlock question depends on what the application in question is doing - as schwertner pointed out above, if one SELECT .. FOR UPDATE is performed after a previous one a deadlock can happen... If the application is careful to perform only a single SELECT FOR UPDATE in each transaction then it should be safe enough... (i.e. each session only performs a single SELECT FOR UPDATE in a single transaction...)

0
 
anbernasAuthor Commented:
I understand that. But the problem is that multiple unconnected threads are trying to get the same rows. Those rows are "next un-processed rows that need processing".
I wanted the locking mechanism to be handled by oracle, but apparently this can not be. It's just "undestroyable"
0
 
schwertnerCommented:
The locking mechanism is handled by Oracle in a very good way!
0
 
anbernasAuthor Commented:
I still don't understand how to do what i want avoiding deadlocks. the UPDATE method doesnt work and neither does the SELECT...FOR UPDATE method. I'm running out of choices.

Best regards
sotretus
0
 
schwertnerCommented:
1. DEADLOCK and LOCKED ROWS IN A TABLE are different things.

DEADLOCK: A deadlock is a situation in which two computer programs sharing the same resource are effectively preventing each other from accessing the resource, resulting in both programs ceasing to function.

LOCKED ROWS: Normally if a session issues a DML statement against a row that has already been changed the session will become lock waited.  Oracle is very patient and will wait forever for the locking session to issue a commit or rollback.  This is usually undesirable for online transactions and internally Oracle often made use of a parameter called nowait to prevent certain operations like create index, prior to online index builds, from going into a lock waiting state.  This parameter was later made available for general use with the select for update statement.  When an attempt is made to access a locked row with the nowait parameter set the error: ORA-00054: resource busy and acquire with NOWAIT specified is returned.

2. You can not prevent Oracle to lock row

3. SELECT...FOR UPDATE method works fine in Oracle

4. If you want to prevent to double same process on a set of rows you should add additional columns with reasonable values in order to signal that a particular operation was done on particul;ar date or under particular circumstances and your application should be programmed to detect this situation.
Be aware that Oracle has no mechanism to prevent to double an operation on precessed rows. This features depend on the business logic and normally are solved on application or trigger level and also need additional data in the rows.

3. The idea that locked rows automatically lead to DEADLOCK is a rumour and isn't true in the normal practice. If a subset of rows are locked and other resource tries to access them it will simply get ORA-00054 Oracle error.
0
 
cjjcliffordCommented:
split
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 6
  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now