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

Question

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?

Answer 1

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

Answer 2

Serializable isolation level protects against all three known concurrency problems,because serializable level applies range locks so you can't modify rows with the range of transaction. It prevents against(but deadlocks are possible):

Dirty Reads occur when one transaction reads data written by another, uncommitted, transaction. The danger with dirty reads is that the other transaction might never commit, leaving the original transaction with "dirty" data.

Non-repeatable Reads occur when one transaction attempts to access the same data twice and a second transaction modifies the data between the first transaction's read attempts. This may cause the first transaction to read two different values for the same data, causing the original read to be non-repeatable

Phantom Reads occur when one transaction accesses a range of data more than once and a second transaction inserts or deletes rows that fall within that range between the first transaction's read attempts. This can cause "phantom" rows to appear or disappear from the first transaction's perspective.