September 14, 2016 at 9:45 am
Dear all,
I have some stored procedures that I was calling one by one and passing a variable with the market code. All using the same connection.
Like 1 - Open connection. 2- Execute sp1. 3 - Execute sp2 ....
(those are 7 stored procedures)
Now a new team started to trigger this but instead of triggering 1 by 1 till the seven finish, they are doing the opposite. They are executing 5 times the same stored procedure in parallel , each one with a sifferent parameter.
This is taking a lot of time. it seems we have several locks (not deadlocks).
I know that I can decrease the isolation level inside stored procedures so that they don't cause locking.
What is the lowest level ?
Also, when the stored proc ends, the isolation level gets to its normal state?
thank you
September 14, 2016 at 10:28 am
Another question related with the same topic is this:
I have added the following code to the my sp_procedure:
create sP_name ()
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
--Transaction is handled by the caller SSIS package if it is necessary.
end
1) This isolation level (TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ) will only affect the transactions that I have inside the stored procedure, correct? I mean, the other transactions (executed from other stored procedures) will run using the normal SQL Server isolation , correct?
2) After the stored procedure completes completes. this TRANSACTION ISOLATION LEVEL READ UNCOMMITTED is discarded it does not affect anything more. correct? Or do I have to unset the read uncommited at the end of the stored proc?
September 14, 2016 at 11:43 am
river1 (9/14/2016)
I know that I can decrease the isolation level inside stored procedures so that they don't cause locking.
Wrong question.
You should be fixing (tuning) the procedure so that it doesn't deadlock, rather than telling SQL that it's fine if the data is slightly wrong (which is part of what read uncommitted does)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply