LINQ To SQL Samples - Simultaneous Changes |
This and the following sample demonstrate optimistic concurrency. In this sample, the other user makes and commits his update to Product 1 before you read the data so no conflict occurs.
Public Sub LinqToSqlSimultaneous01()
Console.WriteLine("OTHER USER: ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~")
' Open a second connection to the database to simulate another user
' who is going to make changes to the Products table
Dim otherUser_db As NorthwindDataContext = New NorthwindDataContext()
otherUser_db.Log = db.Log
Dim otherUser_product = (From p In otherUser_db.Products _
Where p.ProductID = 1 _
Select p).First()
otherUser_product.UnitPrice = 999.99
otherUser_db.SubmitChanges()
Console.WriteLine("~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~")
Console.WriteLine()
Console.WriteLine("YOU: ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~")
Dim product = (From p In db.Products _
Where p.ProductID = 1 _
Select p).First()
product.UnitPrice = 777.77
Dim conflict As Boolean = False
Try
db.SubmitChanges()
Catch x As DBConcurrencyException
conflict = True
End Try
Console.WriteLine()
If (conflict) Then
Console.WriteLine("* * * OPTIMISTIC CONCURRENCY EXCEPTION * * *")
Console.WriteLine("Another user has changed Product 1 since it was first requested.")
Console.WriteLine("Backing out changes.")
Else
Console.WriteLine("* * * COMMIT SUCCESSFUL * * *")
Console.WriteLine("Changes to Product 1 saved.")
End If
Console.WriteLine("~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~")
Cleanup63() ' Restore previous database state
End Sub
Result:
OTHER USER: ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
YOU: ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
* * * COMMIT SUCCESSFUL * * *
Changes to Product 1 saved.
~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
This sample demonstrates the implicit transaction created by SubmitChanges. The update to prod2's UnitsInStock field makes its value negative, which violates a check constraint on the server. This causes the transaction that is updating both Products to fail, which rolls back all changes.
Public Sub LinqToSqlSimultaenous03()
Console.WriteLine("*** BEFORE ***")
ObjectDumper.Write(From p In db.Products Where p.ProductID = 4 Select p)
ObjectDumper.Write(From p In db.Products Where p.ProductID = 5)
Console.WriteLine()
Console.WriteLine("*** UPDATE WITH IMPLICIT TRANSACTION ***")
Try
Dim prod1 As Product = (From p In db.Products _
Where p.ProductID = 4 _
Select p).First()
Dim prod2 As Product = (From p In db.Products _
Where p.ProductID = 5 _
Select p).First()
prod1.UnitsInStock = New Short?(prod1.UnitsInStock - 3)
prod2.UnitsInStock = New Short?(prod2.UnitsInStock - 5) ' ERROR: this will make the units in stock negative
' db.SubmitChanges implicitly uses a transaction so that
' either both updates are accepted or both are rejected
db.SubmitChanges()
Catch e As Exception
Console.WriteLine(e.Message)
End Try
Console.WriteLine()
Console.WriteLine("*** AFTER ***")
clearDBCache()
ObjectDumper.Write(From p In db.Products _
Where p.ProductID = 4 _
Select p)
ObjectDumper.Write(From p In db.Products _
Where p.ProductID = 5 _
Select p)
Cleanup95() ' Restore previous database state
End Sub
Result:
*** BEFORE ***
ProductID=4 ProductName=Chef Anton's Cajun Seasoning SupplierID=2 CategoryID=2 QuantityPerUnit=48 - 6 oz jars UnitPrice=22.0000 UnitsInStock=53 UnitsOnOrder=0 ReorderLevel=0 Discontinued=False Order_Details=... Category={ } Supplier={ }
ProductID=5 ProductName=Chef Anton's Gumbo Mix SupplierID=2 CategoryID=2 QuantityPerUnit=36 boxes UnitPrice=22.3500 UnitsInStock=0 UnitsOnOrder=0 ReorderLevel=0 Discontinued=True Order_Details=... Category={ } Supplier={ }
*** UPDATE WITH IMPLICIT TRANSACTION ***
The UPDATE statement conflicted with the CHECK constraint "CK_UnitsInStock". The conflict occurred in database "CE559A7E617443375E2DBA1456355C1C_L STUDIO 2008\PROJECTS\SAMPLEQUERIESTOCONTENT\SAMPLE QUERIES\BIN\DEBUG\SAMPLEDATA\NORTHWIND.MDF", table "dbo.Products", column 'UnitsInStock'.
The statement has been terminated.
*** AFTER ***
ProductID=4 ProductName=Chef Anton's Cajun Seasoning SupplierID=2 CategoryID=2 QuantityPerUnit=48 - 6 oz jars UnitPrice=22.0000 UnitsInStock=53 UnitsOnOrder=0 ReorderLevel=0 Discontinued=False Order_Details=... Category={ } Supplier={ }
ProductID=5 ProductName=Chef Anton's Gumbo Mix SupplierID=2 CategoryID=2 QuantityPerUnit=36 boxes UnitPrice=22.3500 UnitsInStock=0 UnitsOnOrder=0 ReorderLevel=0 Discontinued=True Order_Details=... Category={ } Supplier={ }
This sample demonstrates using an explicit transaction. This provides more protection by including the reading of the data in the transaction to help prevent optimistic concurrency exceptions. As in the previous query, the update to prod2's UnitsInStock field makes the value negative, which violates a check constraint within the database. This causes the transaction that is updating both Products to fail, which rolls back all changes.
Public Sub LinqToSqlSimultaneous04()
Console.WriteLine("*** BEFORE ***")
ObjectDumper.Write(From p In db.Products _
Where p.ProductID = 4 _
Select p)
ObjectDumper.Write(From p In db.Products _
Where p.ProductID = 5 _
Select p)
Console.WriteLine()
Console.WriteLine("*** UPDATE WITH EXPLICIT TRANSACTION ***")
' Explicit use of TransactionScope ensures that
' the data will not change in the database between
' read and write
Using ts As TransactionScope = New TransactionScope()
Try
Dim prod1 As Product = (From p In db.Products _
Where p.ProductID = 4 _
Select p).First()
Dim prod2 As Product = (From p In db.Products _
Where p.ProductID = 5 _
Select p).First()
prod1.UnitsInStock = New Short?(prod1.UnitsInStock - 3)
prod2.UnitsInStock = New Short?(prod2.UnitsInStock - 5) ' ERROR: this will make the units in stock negative
db.SubmitChanges()
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Using
Console.WriteLine()
Console.WriteLine("*** AFTER ***")
clearDBCache()
ObjectDumper.Write(From p In db.Products _
Where p.ProductID = 4 _
Select p)
ObjectDumper.Write(From p In db.Products _
Where p.ProductID = 5 _
Select p)
Cleanup66() ' Restore previous database state
End Sub
Result:
*** BEFORE ***
ProductID=4 ProductName=Chef Anton's Cajun Seasoning SupplierID=2 CategoryID=2 QuantityPerUnit=48 - 6 oz jars UnitPrice=22.0000 UnitsInStock=53 UnitsOnOrder=0 ReorderLevel=0 Discontinued=False Order_Details=... Category={ } Supplier={ }
ProductID=5 ProductName=Chef Anton's Gumbo Mix SupplierID=2 CategoryID=2 QuantityPerUnit=36 boxes UnitPrice=22.3500 UnitsInStock=0 UnitsOnOrder=0 ReorderLevel=0 Discontinued=True Order_Details=... Category={ } Supplier={ }
*** UPDATE WITH EXPLICIT TRANSACTION ***
The UPDATE statement conflicted with the CHECK constraint "CK_UnitsInStock". The conflict occurred in database "CE559A7E617443375E2DBA1456355C1C_L STUDIO 2008\PROJECTS\SAMPLEQUERIESTOCONTENT\SAMPLE QUERIES\BIN\DEBUG\SAMPLEDATA\NORTHWIND.MDF", table "dbo.Products", column 'UnitsInStock'.
The statement has been terminated.
*** AFTER ***
ProductID=4 ProductName=Chef Anton's Cajun Seasoning SupplierID=2 CategoryID=2 QuantityPerUnit=48 - 6 oz jars UnitPrice=22.0000 UnitsInStock=53 UnitsOnOrder=0 ReorderLevel=0 Discontinued=False Order_Details=... Category={ } Supplier={ }
ProductID=5 ProductName=Chef Anton's Gumbo Mix SupplierID=2 CategoryID=2 QuantityPerUnit=36 boxes UnitPrice=22.3500 UnitsInStock=0 UnitsOnOrder=0 ReorderLevel=0 Discontinued=True Order_Details=... Category={ } Supplier={ }
