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
– Jarek, Commented Apr 15, 2013 at 13:39
Wouldn't a transaction lock the tables in use in order to prevent change in the used data? — MAV
– MAV, Commented Apr 15, 2013 at 13:42
@Pako To insert a row and get its identity. (It seems not to be straightforward as I would expect.) — ispiro
– ispiro, Commented Apr 15, 2013 at 13:42

Jeff Rosenberg · Accepted Answer · 2013-04-15 13:44:56Z

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;

7 Comments

Trevor Pilley Over a year ago

I missed the bit about getting the ID for the inserted value, this is the way to go for that specific scenario.

ispiro Over a year ago

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: support.microsoft.com/kb/2019779). Or is that an old but that's been fixed?

Jeff Rosenberg Over a year ago

According to that link, "The fix for this issue was first released in Cumulative Update 5 for SQL Server 2008 R2 Service Pack 1."

ispiro Over a year ago

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?

Jeff Rosenberg Over a year ago

That's actually a complicated question that depends on Isolation Levels. 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.

|

Trevor Pilley · Accepted Answer · 2013-04-15 13:42:53Z

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, 2013 at 13:42

Trevor Pilley

16.5k6 gold badges47 silver badges61 bronze badges

4 Comments

ispiro Over a year ago

Thanks. I want to lock the whole table. Is that possible?

Trevor Pilley Over a year ago

@ispiro why on earth would you want to lock the entire table?

ispiro Over a year ago

So that no one will write new data to it without first seeing this data.

Trevor Pilley Over a year ago

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.

Thorsten Dittmar · Accepted Answer · 2013-04-15 13:45:05Z

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, 2013 at 13:45

Thorsten Dittmar

56.9k10 gold badges95 silver badges145 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

7 Comments

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related