CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2008
programming idiot
programming idiot

Reputation: 145

Does wrapping all sql calls in ISOLATION LEVEL SERIALIZABLE remove all concurrency issues?

In order to prevent concurrency errors, I've decided to wrap all my sql calls' (which are all in stored procedures) sql statements (all crud operations, such as update / insert/ upserts and even just table reading) with this

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
begin tran

--sqlstatements here

OPTION (MAXDOP 1)

COMMIT TRAN

let's just say that I am not concerned about performance. I only want to prevent constraint violations, and deadlocks caused by 2 or more simultaneous threads accessing the same database.

Does this effectively remove all deadlocks and also constraint issues arising from race conditions?

Do I still need to explicitly use with (hold lock, update lock) for CUD functions if I already wrap the calls in a serializable transaction?

Upvotes: 1

Views: 399

Answers (1)

podiluska
podiluska

Reputation: 51504

It depends what you mean by a "concurrency issue". If you include deadlocks in this, then you may still need to include locking hints (eg: updlock) in your query

Upvotes: 2

Related Questions