CODEWITHSUNDEEP
sql-serveroptimistic-concurrency
AaronLS
AaronLS

Reputation: 38367

Transaction Required with Optimistic Concurrency?

Reading through this question there's several mentions of optimistic concurrency being more costly during resolution because of aborting transactions:

Optimistic vs. Pessimistic locking

If you are executing a single update statement, usually you would craft the where clause to ensure no update is made if the is a conflict. I.e. you would include the original row state in the where clause. If someone else edited the row and there is a conflict, the update statement will simply update zero records, due to the where clause. You would not need to abort any transaction, because no change was made by your update. You would simply check the number of rows modified by the query(usually one or zero), and if it modified zero, you present the user with a resolution decision.

Unless you have multiple operations that must be atomic along with the update, I would not think you'd need a transaction for a single update with a where clause to support optimistic concurrency. But perhaps there is a nuance that I am missing.

Do you need a transaction for a single update statement with a well formed where clause selecting based on original row state(for optimistic concurrency)?

Upvotes: 2

Views: 1074

Answers (1)

Jeff S
Jeff S

Reputation: 7484

In general, you never need a transaction for any single DML statement (Insert, Update, Delete). Each individual DML statement is atomic. That is, it succeeds or fails as a whole. A transaction allows you to group multiple DML queries into a single, atomic unit of work (so that they succeed or fail as a group).

Having said that, I would typically always use transactions updating. That way, the update pattern is common across all update/saves and I don't have to worry about how many updates I'm doing (or remembering to add a transaction because I added a second update statement).

Having said all of that, I don't think that really was the answer to your question.

Most modern web applications have a flow similar to:

  1. Get the data from the database and display it
  2. Let the user do their thing.
  3. Update the database

Step 2 will take much longer than the other two steps. And the concurrency model don't help with this flow because you cannot open a transaction in step 1 and close it in step 3. So how do you make sure the data updated is the same data that was displayed. While a well-formed update could prevent the update from occurring, that's only part of the problem because you also have to let the user know their update failed.

The concurrency options only really help with processing in step 3. If that process looks like:

  1. Read the data from the database
  2. check if the data has changed
  3. If it hasn't changed, update it.
  4. Save the changes to the database

Pessimistic concurrency guarantees the data will not have changed between step 1 and step 4; optimistic concurrency doesn't guarantee anything.

I guess I'm saying that whether or not you have a transaction around a single update statement does not impact optimistic concurrency or pessimistic concurrency. The model picked should be used but both concurrency models need to deal with data changes.

Upvotes: 2

Related Questions