Home » SQL & PL/SQL » SQL & PL/SQL » Table Lock
Table Lock [message #35795] Tue, 16 October 2001 08:22 Go to next message
jack
Messages: 123
Registered: September 2000
Senior Member
I have a need to lock a table for the duration of my procedure. Within the procedure I will do commits every 1,000 records for a table of 2 million+ rows. This will take about 2 hours when there "shouldn't" be any users online.

I have not found a way to lock the table up front and then unlock it on exit.

Any ideas or a place to seek the answer?

TIA, Jack

----------------------------------------------------------------------
Re: Table Lock [message #35800 is a reply to message #35795] Tue, 16 October 2001 09:53 Go to previous messageGo to next message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
what about lock table?

----------------------------------------------------------------------
Re: Table Lock [message #35804 is a reply to message #35795] Tue, 16 October 2001 11:14 Go to previous message
jack
Messages: 123
Registered: September 2000
Senior Member
Not exactly what I was looking for.

I am ocassionaly getting a 'snapshot too old' error. I believe this is due to records getting touched and this invaildates the update. This results in a wasted effort. All I can do is start all over. My window to complete does not allow for retries. I need to get it done completly the first time.

I can lock the table again after a commit with no problem.

Is it possible if another user does want to update a record that they could access any row between the commit and lock?

Could they be in cue waiting for the lock to release (they get their task handled) and then the lock would take effect? If so this is what I need to get resolved. I need the lock without any risk of others accessing this table for the duration of my cursor loop.

Sample psuedocode:

Lock Table
for r in c_Customers loop
...do tasks
every 1000 commit

end loop

Release Lock

----------------------------------------------------------------------
Previous Topic: Insufficient privileges !?
Next Topic: File size
Goto Forum:
  


Current Time: Thu Mar 28 16:20:03 CDT 2024