Sql-server – how to make a table unlocked within transaction block

sql servertransaction

I am using SQL Server 2008. I have several stored procedures that process different reports. Each stored procedure makes use of Begin Transaction' and 'Commit Transaction, using the default isolation level.

Everything works fine since the target tables use by these stored procedures are different for each stored procedure, except that all stored procedures call another common stored procedure to write log messages into the SPLog table. I need the SPLog table to be available to all stored procedures, and not to be locked by any stored procedure. The table is designed so that each message has a column indicating which stored procedure it belongs to.

The problem is the SPLog table is locked once a stored procedure is ran, causing other stored procedures to wait until the first stored procedure commits. How can I allow INSERTs into this SPLog table by concurrent stored procedures from within a TRANSACTION block?

Below is the pseudo code of these stored procedures:

Stored Procedure SP

Begin Transaction

 business logic....perform operation A on dedicated tables
    call common SP to insert a message row to SPLOG table (for common use)
 business logic....perform operation B on dedicated tables
    call common SP to insert a message row to SPLOG table (for common use)
 ....
Commit Trasaction

All stored procedures have similar pseudo code. Appending rows to SPLOG table should be allowed at any time.

How do I achieve that?

Best Answer

You'd have to do it outside of the transaction

For example: Spool the data in a table variable which don't participate in transactions and write once at the end (after commit). Or use Service Broker to decouple log writes.

Any logging otherwise is subject to the transaction semantics eg locks, can be rolled back etc

Related Question