December 2, 2016 at 1:37 pm
Hi,
Assume i have 4 tables belongs to users data and i have stored procedure which has logic to update only one user data on all the tables at a time(userid will be passed to update). I have used transaction because if anything fails i don't want to commit it.
Also i have web page which reads the users data from the 4 tables. So my question is if one people try to update the user record and at the same time if some one try to read those tables to display the data on website. Since the those 4 tables mentioned in the proc has transaction, will it throw any error if the Wed is trying to read the data from those table because of locking?
December 2, 2016 at 1:52 pm
In this simple example, this would not error, but could cause blocking, meaning the Web page is waiting until the UPDATEs are finished and their transaction committed before it performs the reads. Isolation Levels of the connections control how different scenarios like this play out. For a detailed description of the transaction isolation levels in SQL Server, try here:
https://msdn.microsoft.com/en-us/library/ms173763.aspx
As a note, in general using READ UNCOMMITTED level or the NOLOCK hint in a query to avoid the read being blocked by the UPDATE is considered a bad practice and can lead to your users seeing invalid or inconsistent data.
December 2, 2016 at 2:00 pm
KGJ-Dev (12/2/2016)
I have used transaction because if anything fails i don't want to commit it.
Do you have proper error handling that will rollback in case of an error?
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
December 2, 2016 at 2:03 pm
Hi Chris, thanks for the explanation.
Hi Gail,
Yes i have proper error handling in the catch block. Any more suggestions how to handle this situation.
December 2, 2016 at 2:05 pm
Read committed snapshot or snapshot isolation?
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
December 2, 2016 at 2:11 pm
Hi Gail,
Will either one satisfy my need? which one is the best for my situation and if possible could you please post me a sample code to understand.
December 2, 2016 at 11:46 pm
KGJ-Dev (12/2/2016)
Will either one satisfy my need?
Test carefully and see, and make sure you read up on them
which one is the best for my situation
Test carefully and see, and make sure you read up on them
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 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply