SQL Transacation questions
-
I have few questions about SQL/C# transaction. 1/ How can you add transaction functionality to your stored procedure without locking anything ( I know there is different isolation level, but I'm not sure which one to use ) 2/ I need to use transaction just for the sake of rolling back the changes if there was an exception in the middle of the procedure. so I really don't care about locking tables. which isolation level should I use.
-
I have few questions about SQL/C# transaction. 1/ How can you add transaction functionality to your stored procedure without locking anything ( I know there is different isolation level, but I'm not sure which one to use ) 2/ I need to use transaction just for the sake of rolling back the changes if there was an exception in the middle of the procedure. so I really don't care about locking tables. which isolation level should I use.
Tunisiano32 wrote:
How can you add transaction functionality to your stored procedure without locking anything ( I know there is different isolation level, but I'm not sure which one to use )
Locking is done when writing. The isolation levels are for specifying what you want to read.
Tunisiano32 wrote:
I need to use transaction just for the sake of rolling back the changes if there was an exception in the middle of the procedure. so I really don't care about locking tables. which isolation level should I use.
Sounds like the procedure consists of multiple steps that can fail individually, and those updates might lock multiple tables while processing.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^][](X-Clacks-Overhead: GNU Terry Pratchett)
-
Tunisiano32 wrote:
How can you add transaction functionality to your stored procedure without locking anything ( I know there is different isolation level, but I'm not sure which one to use )
Locking is done when writing. The isolation levels are for specifying what you want to read.
Tunisiano32 wrote:
I need to use transaction just for the sake of rolling back the changes if there was an exception in the middle of the procedure. so I really don't care about locking tables. which isolation level should I use.
Sounds like the procedure consists of multiple steps that can fail individually, and those updates might lock multiple tables while processing.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^][](X-Clacks-Overhead: GNU Terry Pratchett)
READ UNCOMMITTED is the isolation level you are looking for I think. This will allow dirty reads, but almost eliminate all locking. Writing will lock the affected tables, but if others READ UNCOMMITTED they should be fine. If you have to lock the table in order the rollback the process then you really have no choice but to lock it.