CODEWITHSUNDEEP
sqlsql-server
techmad
techmad

Reputation: 933

Concurrency, Locking in SQL Server Database

I have a requirement to Save/Update data in 15 tables. I have to ensure at the time of updation the concurrency and locking should be maintained, so that no dirty read happens. I am updating data using storeed procedure. What I can do in oredr to implement locking and concurrency?

Upvotes: 0

Views: 5219

Answers (4)

Jaime
Jaime

Reputation: 1120

Other answers are assuming that you are already using a transaction. I won't omit this, since you might be missing it.

You should use a transaction to ensure that records in all 15 tables or none are inserted/updated. The transaction ensures you atomicity in your operation. If something fails during the stored procedure and you don't use a transaction, some of the save/update operations will be made, and some not (the ones from the query that has produced the error).

If you use BEGIN TRAN, and COMMIT for successful operations or ROLLBACK in case of failure, you will get all done or nothing. You should check for errors after each query execution, and call ROLLBACK TRANSACTION if there is one, or call COMMIT at the end of the stored procedure.

There is a good sample in the accepted answer of this Stackoverflow question on how to handle transaction inside a stored procedure.

Once you have the transaction, the second part is how to avoid dirty reads. You can set the isolation level of your database to READ COMMITED, this will prevent, by default, dirty reads on your data. But the user can still chose to do dirty reads by specifying WITH (NOLOCK) or READUNCOMMITED in his query. You cannot prevent that.

Besides, there are the snapshot isolation levels (Snapshot and Read Commited Snapshot) that could prevent locking (which is not always good), avoiding dirty reads at the same time.

There is a lot of literature on this topic over the internet. If you are interested in snapshot isolation levels, I suggest you to read this great article from Kendra Little at Brent Ozar.

Upvotes: 4

Charles Bretana
Charles Bretana

Reputation: 146409

All you need to do to ensure that the sql server isolation level is set appropriately. To eliminate dirty reads, it needs to be at Read Committed or higher, (Not at Read Uncommitted). Read Committed is the default setting out of the box.

It might be worth while, however, to review the link above and see what benefits, (and consequences) the higher settings provide.

Upvotes: 1

Remus Rusanu
Remus Rusanu

Reputation: 294177

You can't prevent that dirty read does not happen. Is the reader that does the dirty reads, not you (the writer). All you can do is ensure that your write is atomic, and that is accomplished by wrapping all writes in a single transaction. This way readers that do not issue dirty reads will see either all your updates, either none (atomic).

If a reader chooses to issue dirty reads there's nothing you can do about it.

Note that changing your isolation level has no effect whatsoever on the reader's isolation level.

Upvotes: 2

M.Ali
M.Ali

Reputation: 69494

You can set the transaction isolation level to SERIALIZABLE. by using the following statement 

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Before you BEGIN TRANSACTION but Warning it can slow down other user who will be try to see on update or insert data into your tables, Your can also make use of the SNAP_SHOT Isolation Level which shows you only the last Commited/Saved data but it makes extensive use of Temp DB which can also effect the performance.

Upvotes: 0

Related Questions