0

Let's consider a scenario:

Two or more people are working on a table that stores configuration data for an app. There are multiple configurations in that table, so each person can work on it's own data set.

Edit 200 rows functionality is faster than scripting it, because there are a lot of special characters which needs escaping in script, in Edit it's just copy & paste in most cases.

Each person works on rows specific to a configuration, so query for edit contains where clause. Of course it's not happening constantly, but when testing, there are periods of unusual such activity sometimes.

Now here's the question: when changes are saved in Edit 200 rows does SSMS commits whole table or just the rows in the pane? Also: what if two people accidentally work on same data set - will they overwrite each other's work?

My gut feeling says "no, just specific rows" to the first and "yes" to second, but I can't find any description on how that SSMS UI function works in the background (which may be because I'm unusually slow and dense today... not a corona, but not feeling well).

Improve this question

1 Answer 1

Reset to default
4

There's no such thing as "commit whole table". A modification is performed that affects x number of rows based on the WHERE clause. At end of transaction (end of statement unless you specified BEGIN tran and you now say COMMIT or ROLLBACK), the rows that were modified are committed or rolled back.

As for the possibility of a conflict. I can see two ways to seek answer:

  1. Run a trace and see what SQL is submitted.

  2. Try it.

I ran a trace and I didn't see anything at the T-SQL that protect you from the conflict.

When I tried it, however, when doing the modification as the "second person", I got a message in SSMS:

enter image description here

What SSMS did was to submit an UPDATE with a WHERE clause for each column (compare to the value when you read the row). If it got 0 rows back, it assumes that somebody else modified the row while we were lookin at it, and ask us that to do (I don't have the source code for SSMS, so this sentence is my assumption of how it does it). If we say "Yes", then it submits one more UPDATE with a WHERE clause just for the primary key column. Here are those two UPDATEs from my trace, the second one submitted after I answered "Yes" in above dialog:

exec sp_executesql N'UPDATE TOP (200) Sales.Customer SET TerritoryID = @TerritoryID WHERE (CustomerID = @Param1) AND (PersonID IS NULL) AND (StoreID = @Param2) AND (TerritoryID = @Param3) AND (AccountNumber = @Param4) AND (rowguid = @Param5) AND (ModifiedDate = @Param6)',N'@TerritoryID int,@Param1 int,@Param2 int,@Param3 int,@Param4 nvarchar(10),@Param5 uniqueidentifier,@Param6 datetime',@TerritoryID=5,@Param1=2,@Param2=1028,@Param3=1,@Param4=N'AW00000002',@Param5='E552F657-A9AF-4A7D-A645-C429D6E02491',@Param6='2014-09-12 11:15:07.263'

exec sp_executesql N'UPDATE TOP (200) Sales.Customer SET TerritoryID = @TerritoryID WHERE (CustomerID = @Param7)',N'@TerritoryID int,@Param7 int',@TerritoryID=5,@Param7=2
Improve this answer
2
  • 1
    "What SSMS did was to submit an UPDATE with a WHERE clause for each column". Adding that this technique is called optimistic concurrency (as opposed to pessimistic locking). If the table has a rowversion column, that could be used instead of comparing every column but SSMS doesn't seem to leverage that.
    – Dan Guzman
    Commented Jan 10, 2022 at 11:41
  • Thank you. This seems to confirm empirically what I suspected. Yes, the fun part is that SSMS's UI is just nice interface, somewhat cumbersome, with vanilla SQL behind it. And it confirms that two people can overwrite each other's work if not careful with WHERE.
    – AcePL
    Commented Jan 11, 2022 at 7:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.