How can I make a stored procedure commit immediately?

Question

EDIT This questions is no longer valid as the issue was something else. Please see my explanation below in my answer.

I'm not sure of the etiquette so i'l leave this question in its' current state

I have a stored procedure that writes some data to a table.

I'm using Microsoft Practices Enterprise library for making my stored procedure call. I invoke the stored procedure using a call to ExecuteNonQuery.

After ExecuteNonQuery returns i invoke a 3rd party library. It calls back to me on a separate thread in about 100 ms.

I then invoke another stored procedure to pull the data I had just written. In about 99% of cases the data is returned. Once in a while it returns no rows( ie it can't find the data). If I put a conditional break point to detect this condition in the debugger and manually rerun the stored procedure it always returns my data.

This makes me believe the writing stored procedure is working just not committing when its called.

I'm fairly novice when it comes to sql, so its entirely possible that I'm doing something wrong. I would have thought that the writing stored procedure would block until its contents were committed to the db.

Writing Stored Procedure

 ALTER PROCEDURE [dbo].[spWrite] 
    @guid varchar(50),
        @data varchar(50)
    AS
    BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

-- see if this guid has already been added to the table
DECLARE @foundGuid varchar(50);
SELECT @foundGuid = [guid] from [dbo].[Details] where [guid] = @guid; 
    IF @foundGuid IS NULL
    -- first time we've seen this guid
    INSERT INTO [dbo].[Details] ( [guid], data ) VALUES (@guid, @data)
ELSE
    -- updaeting or verifying order
    UPDATE [dbo].[Details]  SET data =@data WHERE [guid] = @guid
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Reading Stored Procedure

ALTER PROCEDURE [dbo].[spRead] 
@guid varchar(50)   
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

SELECT * from [dbo].[Details] where [guid] = @guid; 
END 

Camelback

Answer 1

To actually block other transactions and manually commit, maybe adding

BEGIN TRANSACTION
--place your
--transactions you wish to do here

--if everything was okay
COMMIT TRANSACTION
--or
--ROLLBACK TRANSACTION if something went wrong

could help you?

Meningitis

thanks for the input. I could use a transaction if it would help, but I don't really want to until I understand why I would need to. How would a transaction help in this instance? Would it make the DB force the commit before handling any other queries on this table? – Camelback

yes this forces the commit, one other thing you might check (maybe not :)) is wether that second call really comes in after your first (since you said it's from a different thread) – Meningitis

This is the correct way to force a Commit. However, it won't necessarily cause other queries to block because that is controlled by other things (Isolation Level, Query-type, transaction state, data involved, etc.) as well. – Hadrian

Answer 2

I’m not familiar with the data access tools you mention, but from your description I would guess that either the process does not wait for the stored procedure to complete execution before proceeding to the next steps, or ye olde “something else” is messing with the data in between your write and read calls.

One way to tell what’s going on is to use SQL Profiler. Fire it up, monitor all possible query execution events on the database (including stored procedure and stored procedures line start/stop events), watch the Text and Started/Ended columns, correlate this with the times you are seeing while tracing the application, and that should help you figure out what’s going on there. (SQL Profiler can be complex to use, but there are many sources on the web that explain it, and it is well worth learning how to use it.)

Stagy

Answer 3

I'll leave my answer below as there are comments on it...

Ok, I feel shame I had simplified my question too much. What was actually happening is two things:

1) the inserting procedure is actually running on a separate machine( distributed system).
2) the inserting procedure actually inserts data into two tables without a transaction.

This means the query can run at the same time and find the tables in a state where one has been written to and the second table hasn't' yet had its write committed.

A simple transaction fixes this as the reading query can handle either case of no write or full write but couldn't handle the case of one table written to and the other having a pending commit.

Well it turns out that when I created the stored procedure the MSSQLadmin tool added a line to it by default:

SET NOCOUNT ON;

If I turn that to:

SET NOCOUNT OFF;

then my procedure actually commits to the database properly. Strange that this default would actually end up causing problems.

Camelback

How certain are you that the NOCOUNT setting was the source of the problem? That seems extremely unlikely. – Hamlen

@Nick I'm not 100% certain yet, All I can say is if I toggle this setting on then I can reproduce the problem after about 1,000 trials, with it off I can't reproduce this problem after 100,000 trials. Like all of science I can't disprove a negative very well, I can only say I can't yet reproduce the problem. – Camelback

It sounds like you've got a race condition. Just read and write from the same thread. – Hamlen

@Nick I believe you are right about a race condition but I can't read and write from the same thread. Let's leave that as a constraint. – Camelback

Answer 4

Easy way using try-catch, like it if useful

             BEGIN TRAN
         BEGIN try
        
            INSERT INTO meals
            (
                ...
            ) 
            Values(...)
        
            COMMIT TRAN
        END try
        BEGIN catch
          ROLLBACK TRAN
          SET @resp = (convert(varchar,ERROR_LINE()), ERROR_MESSAGE() )
        END catch 

Galitea