I have a Stored procedure which accept the list of tables at run time, basically this process do the soft deletes for the list of tables. Below are the steps followed for each of the table.
Step 1: Mark all the records in a table to N ( active)
Update FINAL_TABLE SET active_flag ='N';
Step 2: Mark the records as inactive when records are not available in staging table for the primary key combination.
Update FINAL_TABLE SET active_flag = 'Y' WHERE ( Col1, Col2) NOT IN ( SEL Col1,Col2 FROM STG_TABLE) ;
I am in a situation where users are complaining about dirty reads when they try to query the data on FINAL_TABLE between Step 1 and Step2 . Please help me on the syntax to apply exclusive locks while executing multiple DMLs. Basically the table should be locked when Step1 is started and it should be released after the Step 2 completes.Clients don't mind If the query is waiting , they are happy looking at old data than wrong data.
Thanks in Advance!
What you need to do is to:
- set up a transaction,
- place the exclusive lock,
- do the changes,
- end the transaction.
If you're running in Teradata mode then:
BT; LOCKING TABLE FINAL_TABLE FOR EXCLUSIVE; do your work here (step 1 and step 2) ET;
If you are running in ANSI mode then:
LOCKING TABLE FINAL_TABLE FOR EXCLUSIVE; do your work here (step 1 and step 2) COMMIT;
If this is going inside an SP then remember that the SP must be executed in the same transaction mode as it is compiled.
...or of course just run both UPDATEs as part of an MSR with the LOCKING at the front:
LOCKING TABLE FINAL_TABLE FOR EXCLUSIVE
In a SQL Stored Procedure, a Multi-Statement Request is indicated via the BEGIN REQUEST / END REQUEST keywords.