Correct way to execute 2 SQL commands without other command executing in between

Question

The suggested duplicate doesn't answer the question in the title.

I want to execute two MSSQL commands without any other "user" (I'm not sure what's the correct term) executing a command between them.

Searching, I have found two ways that seem like they would achieve that but am unsure about them:

Use TABLOCK. - But I've seen it being considered bad practice.
Use a transaction - But all I could find was that it will be atomic, and not necessarily locking out other actions.

What way is the correct way?

More info: Only my program will be accessing the database, but it might be from several instances of it, and I don't mind a short wait - if one instance will have to wait in line for a second or two - that's fine.

EDIT: I'm trying to insert a row and get its identity. (It seems not to be straightforward as I would expect.)

And could you explain why is it so important that no other query will be executed in between? — Jarek, Apr 15 '13 at 13:39
Wouldn't a transaction lock the tables in use in order to prevent change in the used data? — MAV, Apr 15 '13 at 13:42
@Pako To insert a row and get its identity. (It seems not to be straightforward as I would expect.) — ispiro, Apr 15 '13 at 13:42
possible duplicate of [Return the id of the just added row](http://stackoverflow.com/questions/829567/return-the-id-of-the-just-added-row) — mbeckish, Apr 15 '13 at 13:52
@ispiro - In that case, it would probably be better to add a better answer, or at least a comment, to the original post, rather than creating a duplicate post and hoping for better answers. — mbeckish, Apr 15 '13 at 15:26
The proposed duplicate bears essentially no relation to this question. — Jonathan Leffler, Apr 16 '13 at 23:27

score 11 · Answer 1 · answered Apr 15 '13 at 13:44

11

To insert a row and get its identity, you don't need to block all other commands. Just use a transaction in combination with SCOPE_IDENTITY:

BEGIN TRAN;

INSERT INTO MyTable (MyColumn)
VALUES ('MyValue');

SELECT SCOPE_IDENTITY();

COMMIT TRAN;

More on SCOPE_IDENTITY at MSDN.

answered Apr 15 '13 at 13:44

Jeff Rosenberg

3,522
1
18
38

I missed the bit about getting the ID for the inserted value, this is the way to go for that specific scenario. – Trevor Pilley Apr 15 '13 at 13:48
Thanks. As a result of reading your answer, I searched, and have found that there might be a bug in MSSQL that makes that unreliable. (See: http://support.microsoft.com/kb/2019779). Or is that an old but that's been fixed? – ispiro Apr 15 '13 at 13:54
According to that link, "The fix for this issue was first released in Cumulative Update 5 for SQL Server 2008 R2 Service Pack 1." – Jeff Rosenberg Apr 15 '13 at 13:56
Thanks again. So to sum up - a transaction won't allow any other commands to come in between the transaction's commands. Is that true? – ispiro Apr 15 '13 at 14:02
That's actually a complicated question that depends on [Isolation Levels](http://msdn.microsoft.com/en-us/library/ms189122(v=sql.105).aspx). But in general, the transaction will keep a lock on the table that you're inserting into, and will not allow any other commands _on that table_. It will still allow commands that don't impact that particular table, however -- which is a good thing. – Jeff Rosenberg Apr 15 '13 at 14:08
I believe that in every case -- and I hope someone will correct me if I'm wrong -- the transaction will prevent other commands from _updating_ the table. In some isolation levels, it will not prevents _reads_, but that shouldn't be a problem. – Jeff Rosenberg Apr 15 '13 at 14:10
A transaction isn't required. – ErikE Jun 12 '13 at 22:30

score 3 · Answer 2 · answered Apr 15 '13 at 13:42

3

You need to use a Transaction with an IsolationLevel of Serializable. That will prevent other users reading or modifying the rows used by the query for the duration of the transaction without completely locking out the table.

answered Apr 15 '13 at 13:42

Trevor Pilley

16,156
5
44
60

Thanks. I _want_ to lock the whole table. Is that possible? – ispiro Apr 17 '13 at 14:37
@ispiro why on earth would you want to lock the entire table? – Trevor Pilley Apr 17 '13 at 14:47
So that no one will write new data to it without first seeing this data. – ispiro Apr 17 '13 at 20:04
That approach is going to cause you a world of pain down the line... What you should be doing is the _least_ amount of locking possible and rely on constraints if you need to ensure that multiple users cannot and a record which is effectively the same as another. For example, if you have a table with user details, adding a unique constraint on the email address column is far easier than trying to lock the table to prevent others reading or writing to it while another user is inserting a record. – Trevor Pilley Apr 18 '13 at 07:52

score 1 · Answer 3 · answered Apr 15 '13 at 13:45

1

You can not prevent commands from being executed in between two other commands. All you can do is prevent data from being modified until you're finished with modifying it yourself. This is done using transactions using the respective isolation level.

answered Apr 15 '13 at 13:45

Thorsten Dittmar

55,956
8
91
139

Correct way to execute 2 SQL commands without other command executing in between

3 Answers3

Linked