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
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?