Sql transaction question

  • 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?

  • 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.

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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.

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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.

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply