Concurrency Handling of SQL Transactrion

Why concurrent execution of transactions is desirable?

why concurrent execution of transactions is desirable

Without concurrent execution of transactions, your application throughput is limited to inverse of your transaction duration.

Eg if each transaction takes 50ms, your throughput limited to 20 transactions per second. Which means you can't perform a lot of transactions or support a large number of users.

Best way of managing database concurrency?

This is not a database concurrency issue. The ACID properties of databases are about transactions completing, while maintaining database integrity. In the situation you describe, the transactions are correct, and the database is correctly processing them.

What you want is a locking mechanism, essentially a semaphore that guarantees that only one user can have write access to the data at any one time. You might be able to rely on database locking mechanisms, capturing when locks fail to occur.

But, I would suggest one of two other approaches. If you are comfortable with the changes being only in the application logic, then put the locking mechanism there. Have a place where a user can "lock" the table or record; then don't let anyone else touch it.

You can go a step further. You can require that users obtain "ownership" of the table for changes. Then you can implement a trigger that fails unless the user is the one making the changes.

And, you might think of other solutions. What I really want to point out is that your use-case is outside what RDBMSs do by default (because they would let both transactions complete successfully). So, you will need additional logic for any database (that I'm familiar with).

SQL handling ACID and concurrency

A transaction is atomic by definition. But when a transaction's changes become visible to other users / connections / transactions depends on the isolation level. The default isolation in SQL Server is READ COMMITTED - see this question's answer for more info and links on how to change it.

For this type of scenario, you probably want SERIALIZABLE. The good news is that you can change the isolation level for a transaction with a SET TRANSACTION ISOLATION LEVEL statement in your stored proc. The bad news is that you have to be 100% sure that this is the only place in your code that ever updates the SEAT table.

Fundamentally, the issue you have is that there is a race condition. Just because you are in a transaction does not mean that two transactions can't both call the stored proc at the same time, then run the SELECT. Now both tx think it's ok to to do the UPDATE. Setting the isolation level to SERIALIZABLE locks the table for the tx that hits the SELECT first.

Manage concurrency in SQL Server

The best practice in this case would be optimistic concurrency. Add a rowversion column to the table and check to see if the value has changed from the original value when updating:

CREATE TABLE dbo.Book(
      Id int NOT NULL CONSTRAINT PK_book PRIMARY KEY
    , Title varchar(30) NOT NULL
    , Price decimal(9,2) NOT NULL
    , Status bit NOT NULL
    , row_version rowversion NOT NULL
    );

INSERT INTO dbo.Book (Id, Title, Price, [Status])
    VALUES(1, 'C# 6', 40.0, 0);
GO

CREATE PROC dbo.UpdateBookAvailability
      @Id int
    , @Rowversion rowversion
    , @Status bit
AS
UPDATE dbo.Book
SET Status = @Status
WHERE
    Id = @Id
    AND row_version = @RowVersion;
IF @@ROWCOUNT < 1
BEGIN
    RAISERROR('book not avaiable', 16, 1);
END;
GO

Handling SQL Server concurrency issues

You could increment and fetch the ID in the update statement using output.

update UniqueIdTable
set ID = ID + 1
output deleted.ID
where label = @inputLabel

How to deal with concurrent updates in databases?

Use transactions:

BEGIN WORK;
SELECT creds FROM credits WHERE userid = 1;
-- do your work
UPDATE credits SET creds = 150 WHERE userid = 1;
COMMIT;

Some important notes:

• Not all database types support transactions. In particular, mysql's old default database engine (default before version 5.5.5), MyISAM, doesn't. Use InnoDB (the new default) if you're on mysql.
• Transactions can abort due to reasons beyond your control. If this happens, your application must be prepared to start all over again, from the BEGIN WORK.
• You'll need to set the isolation level to SERIALIZABLE, otherwise the first select can read data that other transactions have not committed yet(transactions arn't like mutexes in programming languages). Some databases will throw an error if there's concurrent ongoing SERIALIZABLE transactions, and you'll have to restart the transaction.
• Some DBMS provide SELECT .. FOR UPDATE , which will lock the rows retreived by select until the transaction ends.

Combining transactions with SQL stored procedures can make the latter part easier to deal with; the application would just call a single stored procedure in a transaction, and re-call it if the transaction aborts.

Transaction Required with Optimistic Concurrency?

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.

---

Related Topics