0

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?

Improve this question
2
  • btw there shouldn't be any reason to prevent many concurrent transaction from inserting new log entries into a log table. Normally, inserts do not block other inserts. You must be doing something that explicitly blocks the logging.
    – Remus Rusanu
    Jan 27, 2012 at 7:00
  • It doesn't block other inserts into the same log table. But it blocks reading of the inserted records while still within the transaction code block of stored procedure.
    – Jianguang Fang
    Feb 2, 2012 at 2:33

1 Answer 1

Reset to default
2

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

Improve this answer
8
  • thanks for your comments. I understand to save the step message in memory and get written to the lgo table at the end after commit at one go. But this can't fullfil the requirments. Each step of SP could take quite long time, I want the step message be available for users or some client web application to poll and display, so that users know which step the SP has run into. In another words, step message written into the SPLog table is requsted to be real time. I don't know how to use servcie broker, also don't understand "use Service Broker to decouple log writes." Can you possible show a exam
    – user5993
    Jan 21, 2012 at 12:25
  • The rest gets cut off, but it's "example, thanks" ... @JianguangFang can you login on this site with the same OpenID you used to sign in on Stack Overflow?
    – jcolebrand ♦
    Jan 22, 2012 at 8:35
  • @JianguangFang: Do want this to persist this in a log, or is it just for notification/polling purposes by other processes?
    – gbn
    Jan 22, 2012 at 9:31
  • I need persist the message in a log table (for back tracking and verifying) as well as for notification/polling purposes by other applications. Do you have any suggestions on how to achieve that?
    – user6209
    Jan 27, 2012 at 5:34
  • 1
    @Jianguang: see Logging messages during a transaction for an example of using SSB to decouple logging. That article shows how to actually achieve logging in a parallel transition (logging stay even if your procedure rolls back), which may or may not be desired.
    – Remus Rusanu
    Jan 27, 2012 at 6:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.