I have a situation where I need to be able to update data in a table from within a transaction, but don't what that update to be part of the transaction.
begin tran
exec dbp_get_ref
insert into trans_table using new ref
waitfor delay '00:05:00'
commit
the dbp reads the last reference used from a table and updates it for the next to use and returns it.
(don't ask why it's done this way, we just have to live with it)
Is there any way to get the dbp to not lock the reference table once its update is complete? The above example is just one insert, but it could be thousands and I don't want to lock the reference table for that long as it will hang other user's sessions.
I've tried using sp_executesql but that seems to participate in the transaction as well because the table remains locked until the commit is reached.