Middleman Middleman - 1 year ago 57
SQL Question

T-SQL - Release Lock in Transaction

Is it possible to release the lock of a certain table inside a transaction?

here is an example:

query window 1:

BEGIN TRAN
UPDATE table_1 SET col_1 = '1' WHERE ID = 1

-- loop update (this takes time)
WHILE EXISTS (SELECT * FROM table_2 WHERE col_1 = 'a')
BEGIN
UPDATE table_2 SET col_1 = 'b' WHERE col_1 = 'a'
END
COMMIT TRAN


query window 2:

SELECT * FROM table_1 WHERE ID = 1


it possible to access table_1 while the transaction in window 1 is still running?

Answer Source

You can use "dirty read" with hint NOLOCK:

SELECT * FROM table_1 WITH(NOLOCK)
WHERE ID = 1

Read more here.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download