July 31, 2014 at 3:02 am
User Database isolation level is read committed, but isolation level changed at SP side.
I have doubut, does data have been committed or not as below two SPs setting in SERIALIZABLE isolation level
Create usp_Proc1
AS
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN
...............
...............
end
Create usp_Proc2
AS
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
...............
...............
COMMIT TRAN
END
July 31, 2014 at 4:32 am
Each of those procedures will commit, individually. Even though one of them doesn't have the COMMIT statement. SQL Server runs in autocommit transaction mode by default.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 31, 2014 at 5:15 am
Thank you replies.. tested and data was commited in SERIALIZABLE isolation level .
There is one SP and written code like SELECT/INSERT/UPDATE all operation at one single stored procedure, it was INSERT/UPDATE command blocked each other and none of them getting completed.
Blocked by - 67 (SPID), also HeaderBlocker - 76 (SPID).. and wait type - LCK_M_IX (intent shared lock each others).
So i planned to test first to implement SERIALIZABLE isolation level at SPs level for resolving this LCK_M_IX wait type, but not hope and same blocking problem happend again. also
why INSERT/UPDATE get blocked and identified some Non-clustered index key was appered in table which is performing INSERT/UPDATE statement. also index key was created at same INSERT cloumn only.
After removing those index blocking issues was resolved, (but not removed SERIALIZABLE isolation level at SPs level. But application took long time more than 15 sec for insert records).
Finally removed SERIALIZABLE isolation level at side after that acceptable seconds take for insert/update.
Thanks
ananda
July 31, 2014 at 6:58 am
Serializable has to take exclusive locks of everything it touches, including reads. So, any other process that is reading anything that the process running in Serializable needs, is going to force that process to wait. That's expected behavior. If you need serializable, you'll have to deal with a certain amount of process blocking.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply