?
Solved

Concurrent access to Oracle - Locking problems

Posted on 2005-03-18
18
Medium Priority
?
774 Views
Last Modified: 2009-04-17
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
Comment
Question by:anbernas
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 4
18 Comments
 
LVL 11

Accepted Solution

by:
cjjclifford earned 1000 total points
ID: 13575502
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
 

Author Comment

by:anbernas
ID: 13575620
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
 
LVL 11

Expert Comment

by:cjjclifford
ID: 13575733
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:anbernas
ID: 13576899
But that will require a complex fail/retry mechanism. Isn't there a way that Oracle Guaranties no collisions???
0
 
LVL 48

Expert Comment

by:schwertner
ID: 13582174
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
 

Author Comment

by:anbernas
ID: 13583349
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
 
LVL 48

Expert Comment

by:schwertner
ID: 13590135
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
 
LVL 11

Expert Comment

by:cjjclifford
ID: 13590231
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
 

Author Comment

by:anbernas
ID: 13591632
But SELECT...FOR UPDATE guarantees no deadlocks will happend?
0
 
LVL 11

Expert Comment

by:cjjclifford
ID: 13591843
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
 

Author Comment

by:anbernas
ID: 13591933
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
 
LVL 48

Expert Comment

by:schwertner
ID: 13592380
The locking mechanism is handled by Oracle in a very good way!
0
 

Author Comment

by:anbernas
ID: 13592406
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
 
LVL 48

Assisted Solution

by:schwertner
schwertner earned 1000 total points
ID: 13598726
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
 
LVL 11

Expert Comment

by:cjjclifford
ID: 14419123
split
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question