CODEWITHSUNDEEP
sqlsql-servert-sql
gordieb
gordieb

Reputation: 251

Is update/select of a single column in a single row atomic and safe in SQL Server?

I'd like to use the following statement to update a column of a single row:

UPDATE Test SET Column1 = Column1 & ~2 WHERE Id = 1

The above seems to work. Is this safe in SQL Server? I remember reading about possible deadlocks when using similar statments in a non-SQL Server DBMS (I think it was related to PostgreSQL).

Example of a table and corresponding stored procs:

CREATE TABLE Test (Id int IDENTITY(1,1) NOT NULL, Column1 int NOT NULL, CONSTRAINT PK_Test PRIMARY KEY (Id ASC))
GO
INSERT INTO Test (Column1) Values(255) 
GO
-- this will always affect a single row only
UPDATE Test SET Column1 = Column1 & ~2 WHERE Id = 1

Upvotes: 1

Views: 809

Answers (2)

Martin Smith
Martin Smith

Reputation: 452957

For the table structure you have shown both the UPDATE and the SELECT are standalone transactions and can use clustered index seeks to do their work without needing to read unnecessary rows and take unnecessary locks so I would not be particularly concerned about deadlocks with this procedure.

I would be more concerned about the fact that you don't have the UPDATE and SELECT inside the same transaction. So the X lock on the row will be released as soon as the update statement finishes and it will be possible for another transaction to change the column value (or even delete the whole row) before the SELECT is executed.

If you execute both statements inside the same transaction then I still wouldn't be concerned about deadlock potential as the exclusive lock is taken first (it would be a different matter if the SELECT happened before the UPDATE)

You can also address the concurrency issue by getting rid of the SELECT entirely and using the OUTPUT clause to return the post-update value to the client.

UPDATE Test SET Column1 = Column1 & ~2 
OUTPUT INSERTED.Column1
WHERE Id = 1

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

What do you mean "is it safe"?

Your id is a unique identifier for each row. I would strongly encourage you to declare it as a primary key. But you should have an index on the column.

Without an index, you do have a potential issue with performance (and deadlocks) because SQL Server has to scan the entire table. But with the appropriate primary key declaration (or another index), then you are only updating a single row in a single table. If you have no triggers on the table, then there is not much going on that can interfere with other transactions.

Upvotes: 0

Related Questions