Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Jan 20 '12 at 21:16

This question came from our site for professional and enthusiast programmers.

    
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 '12 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 '12 at 2:33

1 Answer 1

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

share|improve this answer
    
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 '12 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 '12 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 '12 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 '12 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 '12 at 6:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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