Share via

Azure SQL - same table read/ update by multiple users simulteniously

Partha Das 286 Reputation points
2022-08-30T15:25:06.667+00:00

Hi,

Let's say we have a table
236138-table.jpg

3 analysts open the table using SSMS command "Edit top 200 rows" from their systems
236204-edit-top-200.jpg

They are trying to update the highlighted cell at the same time. What will happen?
Questions:

  1. Is it possible to restrict others when one is updating a cell? Which Isolation Level can be applied to achieve it?
  2. What if we want to retain all the changes by those users? Can row versioning will help?

No code is involved here which will modify the table. It is going to be simultaneous manual update.

Early reply is highly appreciated.

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,363 questions
0 comments
Accepted answer
  1. Erland Sommarskog 107.2K Reputation points
    2022-08-30T21:30:48.327+00:00

    They are trying to update the highlighted cell at the same time. What will happen?

    The first update will win, and the other two will get an error message. This is because the UPDATE statement that SSMS generates includes a WHERE clause with all values on the row, included the old value for being updated. For this reason #2 and #3 will not update any rows: SSMS will detect this and produce an error.

    1) Is it possible to restrict others when one is updating a cell? Which Isolation Level can be applied to achieve it?

    As seen above, SSMS takes care of this for you with optimistic concurrency,.

    2) What if we want to retain all the changes by those users? Can row versioning will help?

    Yes, you would make it a system-versioned table in that case.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. YufeiShao-msft 7,091 Reputation points
    2022-08-31T06:38:03.127+00:00

    Hi @Partha Das ,

    SQL Server supports a range of concurrency control, when many people attempt to modify data in a database at the same time, a system of controls will be implemented, like:
    Pessimistic concurrency control:
    after a user performs an action that causes a lock to be applied, other users cannot perform actions that would conflict with the lock until the owner releases

    Optimistic concurrency control:
    When a user updates the data after it was read, if another user updated the data, an error is raised, the user receiving the error rolls back the transaction and starts over.

    Row versioning prevent users from reading uncommitted data and prevent multiple users from attempting to change the same data at the same time

    Please referring to this doc:
    Transaction locking and row versioning guide

    -------------

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.