Re: Help regarding Concurrency Control

Hi Vish

Essentially under v. 7 ORACLE has improved locking contention in a multi user environment by implementing row level locking by default. So locking is fairly much handled by the DBMS. In other words: you don't have to do anything!!

But, as a word of warning you need to avoid deadlock situations in multi-user environments which arise because different applications lock rows in two (or more) tables in different orders. So develop a locking strategy so developers know the locking sequence for key tables. This problem used to be more prevalent in the days of explicit table locking, but can still prove to be a problem under v.7

Background:

ORACLE uses the concept of transaction processing, so that any transactions within commit points (punctuated by COMMIT or ROLLBACK (to savepoints if appropriate) are treated as one set of items - the effects of any UPDATE, DELETES or INSERTS are only seen by the outside world after the transaction set has been COMMITED to the database.

If a particular row is locked by another user then the user trying to affect the locked row has to wait until the row level lock has been released (when the 1st user COMMITs their transaction) - this is standard practise in RDBMS's and is not to be confused with deadlocks (mentioned above).

Hints on design:

1. Make sure your insert etc are in logical sequence i.e. if you have parent / child relationships make sure you create / update parent records first or conversly delete child rows first then parent - unless you've got CASCADE DELETE option on your tables in which case your just need to delete the parent records. 1a. Make liberal use of defining table relationships in your table definitions i.e. use the FOREIGN KEY table definitions - this should help with ensuring integrity constraints..
2. Organise code into logical transaction sets i.e. do two or more inserts / updates / deleted belong together - must they all succeed or all fail ? - group the DML together and include COMMIT / ROLLBACK (in exception handling sections) statements after each section. (For example, in a banking system a credit and debit often have to be processed together or not at all) Consider the use of defining procedures within your code - having a procedure defined for each logical section, then calling the procedures with the main body of the code. This helps with code maintenance as well. MOST IMPORTANTLY:
3. Minimise the time that rows are exclusively locked (by the default row level locking) by COMMITing data as soon as possible, subject to the logical restrictions mentioned above. There are various ways you can do this but if you're using CURSOR FOR LOOPs be warned that you can't embed COMMIT after each row fetch - you need to use explicit cursors and reference ROWID's so you can fetch across COMMIT's. The shorter the time between COMMIT / ROLLBACK statements, the shorter other users have to wait before they can use the modified data.
4. Conversly, avoid locking up lots of rows and then commiting the data at the end of the program (unless that is the logical thing to do in your case).
5. Avoid explicit table lock statements like the plague - especially EXCLUSIVE TABLE locks!!

It may be worth monitoring the database to see if locking contention is a problem in your multi-user environment - their are various V$ type 'tables' that might be useful for this.

But generally it is the job of the DBMS to ensure integrity and ORACLE seems to handle this well. In my experience, locking problems only occur because of:

1. Deadlocks
2. Tables being exclusively locked for extended periods.

Anyway, hope this helps you out. Please feel free to email me (you'll probably get a quicker response from the work email).

Regards,  

• Martyn Cavett Senior ORACLE Analyst LGT Asset Management London, UK. home: cavett_at_globalnet.co.uk work: Martyn.Cavett_at_gtplc.com

These views are my own and not those of my employer. Normal conditions apply.

Vish magapu <magapuv_at_ix.netcom.com> wrote in article <33497D86.8935CA67_at_ix.netcom.com>...
> I have a PL/SQL Procedure which has lots of inserts deletes, updates on
> multiple tables. It also has many cursors for record by record
> processing. I would like to know what steps I need to take so that
> multiple users can execute this procedure simultaneously at the same
> time so that data integrity will be protected. (I am running Oracle 7.1)
>
> Any help will be greatly appreciated.
>
> Thanks,
> Vish
>
> --------------------------------------------------------------
> Name: Vish Magapu
> E-mail: Vish Magapu <magapu_at_monet.fujitsu.com>
> Date: 04/07/97
> Time: 15:44:24
> --------------------------------------------------------------
>
>
Received on Tue Apr 08 1997 - 00:00:00 CDT