|
|
|
Forum Guru
 
Group: Forum Members
Last Login: 4/17/2008 10:56:01 AM
Posts: 58,
Visits: 9
|
|
An existing ETL job at my site is coded with an exclusive lock applied to delete rows from a table.
I presume this was coded this way to ensure no one accessed an empty table between the delete and insert.
There are 1200 rows in table.
The delete and re-insert of current rows (again 1200) is a subsecond operation,
however occasionally another user or app. is running a long running query with an access lock on this table. Since this causes a block for the job trying to get the exclusive lock I get paged in the middle of the night to resolve.
Has anyone heard of a legitimate reason for coding this way?
Our ETL lead is reluctant to change because the world may come to an end.
Any Ideas of how to convince him would be appreciated.
TBob
|
|
|
|
|
Forum Guru
Group: Forum Members
Last Login: 8/26/2008 4:53:36 PM
Posts: 50,
Visits: 220
|
|
For a delete/insert task I donot think they should put an exclusive lock. If they are concerned about dirty read inbetween delete and insert they can put the code inside BT/ET block.
something like;
BT;
Delete Table...;
.If Errorcode <> 0 Then .Goto Continue;
Insert Into Table ....;
.If Errorcode <> 0 Then .Goto Continue;
ET;
.Label Continue;
Otherwise, if your company uses external scheduler for jobs, you may consider for changing dependency(one job dependant on the other).
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 7/11/2008 10:57:48 AM
Posts: 7,
Visits: 9
|
|
|
if you can use views with "lock row for access" for reading thats best otherwise "lock row for read" on the table by the long running queries and not executing rowhash level locks may also be contributing to the blocking.
|
|
|
|