| Table Lock [message #35795] |
Tue, 16 October 2001 08:22  |
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 #35804 is a reply to message #35795] |
Tue, 16 October 2001 11:14  |
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
----------------------------------------------------------------------
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
