LOCK TABLE EXCLUSIVE, HELP!!

[lorein]

Hi
I have a procedure that update a record in a table. This table is accessed for many work stations and these stations update this table several times. In average this table is updated 900,000 times a day. My oracle version is 9.02
In this table there are no deletes.
The records that are in this table are inserted by other process that is working ok.


My procedure do this:

Receive some parameters ( param1 , param2, param3)
...
IF condition = true THEN
LOCK TABLE my_table IN EXCLUSIVE MODE;

SELECT my_table_id INTO v_my_table_id FROM my_table WHERE available = 'Y' AND rownum = 1;

Update my_table set SET my_date = sysdate, my_field1 = param1, available = 'N' WHERE my_table_id = v_my_table_id;

COMMIT;

END IF;

The problem here is that some times this process is very slow , and the lock in the table remains for a long time and suddenly the table is released.
I review the CPU of the server whereis the database; and is normal.
This process has been working for 2 years and now is failing.

Could you help me to know if ther is a better way to do this process?

I can't undesrtand why this behaivor, if is a simple transaction.

I really appreciate your help

Lorein

Lorein....

 

[SantaMufasa]

Lorein,

Doing a "LOCK TABLE my_table IN EXCLUSIVE MODE;" is usually an unnecessary and inappropriate thing to do. Why does your code even do it? You are locking the entire table when only perhaps 2-3 rows are being updated.

Unless you or someone else can explain the business/technical necessity, I would say remove the "LOCK TABLE..." command.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[lorein]

Mufasa

Let my explain a lite more...

Is necesary lock the table because I have to update a diferent record in each station.
The records that I update are uniques codes. That's why I lock the table , I select a record , I update a record and commit.
If I didn't lock the table many stations can take the same record.

I update 900,000 recs a day.


thanks

Lorein....

 

[SantaMufasa]

Actually, Lorein, no...Only one session on one station at a time can update a record. (Not even a second session on the same workstation can update the same row that the station's other session has modified until the first session COMMITs their changes.) Anytime someone INSERTs, UPDATEs, or DELETEs a row, no one else can make changes to that row until they COMMIT. If you want to ensure that no one can do anything to a row between the time that you read it (SELECT) and UPDATE the row, then you can do a:

SELECT <expression list>
  FROM <some table>
 WHERE <condition(s)>
FOR UPDATE OF <any column from the table>;

(It doesn't matter what column you mention in the "FOR UPDATE OF..." clause. This locks the record until you are done making changes and you either ROLLBACK or COMMIT the change.

Designing your code in this fashion ensures that

a) no one else can change the data you have read and
b) no one prevents a user from another work station from getting their work done (unless their work is to modify a row that someone else has already locked, which is exactly what should happen).

Let us know if you still have questions about this methodology.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Beilstwh]

Dave (as always) is correct. Your code should look like


IF condition = true THEN

SELECT my_table_id INTO v_my_table_id FROM my_table WHERE available = 'Y' AND rownum = 1 for update;

Update my_table set SET my_date = sysdate, my_field1 = param1, available = 'N' WHERE my_table_id = v_my_table_id;

COMMIT;

END IF;

Bill
Oracle DBA/Developer
New York State, USA

 

[lorein]

I'll try this option

thanks!

Lorein....

 

[lorein]

I have another question.

When I lock the table this operation takes a lot of time or CPU?
Or the operation to lock a table is a fast operation?

Or when I cant find this type of information?

Lorein....

 

[lorein]

I have another question.

If I lock a record with a select for update, but I didn't do a commit or rollback. Can other station select the same record?

I want to be sura that once alock a record anyone can select, update or delete the record.

thanks



Lorein....

 

[SantaMufasa]

Lorein,

The first rule of Oracle's locking strategy is "Readers do not block updaters and updaters do not block readers." (The only exception is when you do a "SELECT...FOR UPDATE...", this is a "reading intending explicitly to update", so this "reader" locks the row and no other updaters can make changes to this row until the "SELECT(er) FOR UPDATE" either does a COMMIT or a ROLLBACK.

The reason that your transaction takes so long to process is that other UPDATErs are making changes, which prevents you from obtaining locks until the earlier transaction either COMMITs or ROLLsBACK. The way you can find out if other transactors are slowing down your transaction due to their locks is to do your "SELECT...FOR UPDATE...NOWAIT". The "NOWAIT" option means that if their is one or more other transactions with current locks on rows that you wish to lock, then Oracle responds with an error saying basically that your transaction could not occur because another transaction already had a lock on one or more rows that you wanted and you have "NOWAIT" option specified.

Let us know if this little "NOWAIT" option helps to identify why your transaction takes so long to materialise.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[lorein]

Hi Dave

I tried to use the nowait option but I don't know how to get the error when another transaction has the same record.

I receive the error ORA-00054: resource busy and acquire with NOWAIT specified. I want to capture this error and then try again.

How can I do that? This is in order to get the error and try to get another record.


Thanks!



Lorein....