Is it possible to release the lock of a certain table inside a transaction?
here is an example:
query window 1:
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')
UPDATE table_2 SET col_1 = 'b' WHERE col_1 = 'a'
SELECT * FROM table_1 WHERE ID = 1
You can use "dirty read" with hint
SELECT * FROM table_1 WITH(NOLOCK) WHERE ID = 1
Read more here.