LINQ to SQL Tutorial

Part 17 Concurrency in Linq to SQL

In this video we will discuss how linq to sql handles concurrent updates i.e when 2 or more users update the same data at the same time.

Text version of the video
http://csharp-video-tutorials.blogspot.com/2014/10/part-17-concurrency-in-linq-to-sql.html

Slides
http://csharp-video-tutorials.blogspot.com/2014/10/part-17-concurrency-in-linq-to-sql_4.html

LINQ to SQL Tutorial – All Text Articles & Slides
http://csharp-video-tutorials.blogspot.com/2014/09/linq-to-sql-tutorial.html

LINQ to SQL Tutorial Playlist

Dot Net, SQL, Angular, JavaScript, jQuery and Bootstrap complete courses
https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd

By default, linq to sql uses optimistic concurrency to handle concurrent updates. Optimistic concurrency does not involve locking rows when reading. Instead, this model checks if two users tried to update the same record at the same time. If that happens one user’s changes are committed and the other user’s changes are discarded and an exception will be thrown to notify the user.

Let us look at this in action with an example.

At this point open SQL Profiler and run a new trace. Run the web application. Click “Deposit $500” button. Notice that the balance is updated to 1500 as expected.

Now inspect the Update query that is generated by linq to sql
exec sp_executesql N’UPDATE [dbo].[Accounts]
SET [AccountBalance] = @p3
WHERE ([AccountNumber] = @p0) AND ([AccountName] = @p1) AND ([AccountBalance] = @p2)’,
N’@p0 int,@p1 nvarchar(4000),@p2 int,@p3 int’,@p0=1,@p1=N’John Mary’,@p2=1000,@p3=1500

To update the balance of the account all we need is the New Balance and the AccountNumber that needs to be updated. In the WHERE clause of the query notice that along with AccountNumber and the New Balance, we also have AccountName and the original AccountBalance. The reason we have these is to make sure that no column values in the row have changed since we have loaded the data from the database. If any of the values have changed, then the update fails and an exception will be thrown.

Let’s now simulate the scenario of 2 users updating the same record at the same time. To do this
Step 1 : Throw a break point on the line where we call db.SubmitChanges(); in btnDeposit_Click() method.

Step 2 : Run the application in Debug mode, and click “Deposit $500” button. The processing should stop just before we call SubmitChanges() method.

Step 3 : At this point open SQL Server Management Studio, and execute the following query
Update Accounts set AccountBalance = AccountBalance – 500 Where AccountNumber = 1

Step 4 : Now come back to Visual Studio and press F5 to continue the execution. Notice that “ChangeConflictException” is thrown and the exception message states – Row not found or changed.