I have a streaming ingestion into my staging table. Every 5 minutes I execute a stored procedure to insert/update all the data from this staging table into my final table.
Process of the stored procedure:
BEGIN
- UPDATE RECORDS IN FINAL TABLE FROM STAGING TABLE
- INSERT RECORDS IN FINAL TABLE FROM STAGING TABLE
- TRUNCATE STAGING TABLE
HERE I WANT TO RELEASE THE LOCK
END
Any idea how I can explicitely say that I want to lock the staging table for inserts until I have truncated it?
You can execute these scripts in transactions block like this;
BEGIN
BEGIN TRANSACTION
- UPDATE RECORDS IN FINAL TABLE FROM STAGING TABLE
- INSERT RECORDS IN FINAL TABLE FROM STAGING TABLE
- TRUNCATE STAGING TABLE
COMMIT TRANSACTION
HERE I WANT TO RELEASE THE LOCK
END
Another transactions will wait to finish your transaction block. And you should create try/catch block to handle error. If the script encouraged with error, you should ROLLBACK TRANSACTION in catch block. Because if a transaction is being started, it must be finished as COMMIT or ROLLBACK. You don't want that transaction is being hung.
