How do I prevent SQL from running transactions simultaneously

Question

I've noticed that MS SQL may begin another transaction just before a previous transaction is complete (or committed). Is there a way how we can ensure a transaction must complete first before the next transaction begins?

My problem is that I want to perform an SQL SELECT almost immediately after an SQL INSERT. What I'm seeing right now is; when the SELECT statement is run; it does not return the (very) recently inserted data.

As I traced this scenario using SQL profiler, I've noticed that the SQL INSERT and SELECT performs simultaneously, as in the SELECT occurs before the INSERT is completed.

Is there a way to fix this problem of mine? thanks!

transactions don't prevent simultaneous runs. You need to use locks for that. — Marc B, Aug 14 '12 at 21:45
@user1034912:`it does not return the (very) recently inserted data.`.It does not return half-updated data either (old-new).You get the data prior to insert, and that is valid from consistency point of view.If you want to be sure you get the data after the update you need to use locks as Marc B said — Cratylus, Aug 14 '12 at 21:52
Thanks All. Marc B, what command am I looking for to get the locks? — user1034912, Aug 14 '12 at 22:00
Are you allowed to insert rows while the _select_ is happening? If not, I think you may have to lock the entire table... which is **really bad** from a concurrency performance issue. What are you attempting to accomplish, that you need this kind of locking? — Clockwork-Muse, Aug 14 '12 at 22:39
http://blog.sqlauthority.com/2007/04/27/sql-server-2005-locking-hints-and-examples/ — Sergey Benner, Aug 14 '12 at 22:39

score 1 · Answer 1 · answered Aug 16 '12 at 08:18

From the sounds of it, you're looking for the OUTPUT clause

From the examples in the documentation http://msdn.microsoft.com/en-us/library/ms177564.aspx

DECLARE @MyTableVar table( NewScrapReasonID smallint,
                           Name varchar(50),
                           ModifiedDate datetime);
INSERT Production.ScrapReason
    OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate
        INTO @MyTableVar
VALUES (N'Operator error', GETDATE());

score 0 · Answer 2 · answered Aug 16 '12 at 08:13

You can run your transactions in SERIALIZABLE isolation level. In this way you will ensure that the select will be performed after the insert. In lower isolation levels, the select is performed in paralell and returns the snapshot of the data - the way it is seen with all transactions completed before the one that issues select has been started.

score -1 · Answer 3 · answered Aug 14 '12 at 23:19

I'm guessing you want to get an auto-generated identifier back after the insert? I'm not sure the MSSQL way to do this, but in PostgreSQL, there is INSERT ... RETURNING extension to solve exactly this problem.

http://www.postgresql.org/docs/9.1/static/sql-insert.html

Are you locked into MSSQL?

How do I prevent SQL from running transactions simultaneously

3 Answers3