Managing Optimistic Concurrency Conflicts

Managing Optimistic Concurrency Conflicts

Databases that enable multiple users to update the same tables must decide on a strategy to resolve

conflicts when two or more users update the same row with different column values. One option, called

pessimistic concurrency , requires an updating user to lock the row to update, read its current column

values, complete the edit, and unlock the row. One of the primary problems with pessimistic

concurrency is that the lock prevents other users from reading the row, which causes transactions that

involve the row to stall or roll back.

The alternative, optimistic concurrency , doesn ’ t require a read operation and lock prior to attempting

an update or deletion. In this case, it ’ s assumed that the editing user has a local copy of the data to be

edited — for EF, this an EntityObject instance that contains original values . The UPDATE Table SET

Column = Value or DELETE FROM Table SQL statement includes a WHERE clause with a primary key

constraint to specify the row to be updated and original value constraints for columns that are significant to concurrency management. If another user has edited any of these columns after the editing user

obtained the original values, the database returns 0 as the number of records updated or deleted.

Depending on the client ’ s data provider, you can test for an unexpected 0 value or the provider throws

an exception — an OptimisticConcurrencyException for ADO.NET data providers.

An alternative to the use of original property values is to add a column with the timestamp data type

to the table and entity, then perform a similar comparison between the value saved by the entity and that

in the table currently. The timestamp field leads to simpler WHERE clause constraints but adds a

persistence artifact to the entity, which violates the principle of “ persistence ignorance ” and would

exclude the use of database engines that don ’ t have a timestamp or similar data type. The “ Updating

an Entity Instance and Managing Concurrency with a Timestamp Property ” section describes use of

timestamps by stored procedures.

To complete the update or deletion after detection of a concurrency conflict, it ’ s necessary to invoke the

DataContext .Refresh(RefreshMode.ClientWins, EntityObject ) method to overwrite the local

original values with values from the database and call the DataContext .SaveChanges() method again.

Enabling Optimistic Concurrency Management

for Entity Properties

EF ’ s optimistic concurrency management feature is disabled by default; the EntityName

.PropertyName .ConcurrencyMode value for all entity properties defaults to None . Enabling

participation in concurrency conflict tests requires setting the property ’ s ConcurrencyMode value to

Fixed in the EDM Designer. Figure 13 - 2 shows the Properties sheet for the NorthwindModel.Customer

EntityType with the ConcurrencyMode property value set to Fixed .

Each property whose ConcurrencyMode value you set to Fixed adds a criterion to the WHERE clause.

For example, if you ’ ve set the Customer entity ’ s CompanyName , ContactName , and ContactTitle to

participate in concurrency tests, and change the value of CompanyName from Bogus Software, Inc. to

Bogus Software Corp., the update SQL statement will appear similar to this example captured with SQL

Profiler:

T - SQL

exec sp_executesql N’update [dbo].[Customers]

set [CompanyName] = @0

where (((([CustomerID] = @1) and ([CompanyName] = @2)) and

([ContactName] = @3)) and ([ContactTitle] = @4))

‘,N’@0 nvarchar(40),@1 nchar(5),@2 nvarchar(40),@3 nvarchar(30),

@4 nvarchar(30)’,@0=N’Bogus Software Corp.’,@1=N’BOGUS’,

@2=N’Bogus Software, Inc.’,@3=N’Joe Bogus’,@4=N’President’

Implementing Optimistic Concurrency

Management with Code

C# 3.0

private void btnSaveChanges_Click(object sender, EventArgs e)

{

Customer bogus = (from b in ctxNwind.Customers

where b.CustomerID == “BOGUS”

select b).FirstOrDefault();

if (bogus != null)

{

try

{

if (isNameChanged)

{

bogus.CompanyName = txtCompanyName.Text;

txtLog.Text += “CompanyName changed to ‘” +

bogus.CompanyName + “’\r\n”;

}

if (isContactChanged)

{

bogus.ContactName = txtContactName.Text;

txtLog.Text += “ContactName changed to ‘” +

bogus.ContactName + “’\r\n”;

}

if (isTitleChanged)

{

bogus.ContactTitle = txtContactTitle.Text;

txtLog.Text += “ContactTitle changed to ‘” +

bogus.ContactTitle + “’\r\n”;

}

int changes = ctxNwind.SaveChanges(true);

}

catch (OptimisticConcurrencyException ocEx)

{

// Concurency conflict occurred

string exc = ocEx.Message;

string msg = “A concurrency conflict occurred.\r\n\r\n” +

“Click Yes to overwrite the other user’s changes.\r\n\r\n” +

“Click No to overwrite your changes with the other user’s “ +

“changes.\r\n\r\n” +

“Click Cancel to abandon your attempted edits.”;

DialogResult result =

MessageBox.Show(msg, “Northwind Concurrency Test Harness”,

MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question);

if (result == DialogResult.Yes)

{

// Keep client updated values

ctxNwind.Refresh(RefreshMode.ClientWins, bogus);

ctxNwind.SaveChanges(true);

txtLog.Text += “Your changes were persisted.”;

}

else if (result == DialogResult.No)

{

// Reject client updated values

ctxNwind.Refresh(RefreshMode.StoreWins, bogus);

txtLog.Text += “Your changes were overwritten.”;

LoadBogusTextBoxes(bogus);

}

}

btnVerify.Enabled = true;

}

}

The catch block captures OptimisticConcurrencyException s and the MessageBox enables the user

to select between overwriting the other user ’ s entries by refreshing the entity with the ClientWins

option (Yes), or accepting the other user ’ s entries with the StoreWins option (No). Figure 13 - 3 shows

the message that occurs when a simulated other user (left instance) changes the CompanyName value to

Bogus Software Corp. and you (right instance) attempt to change ContactTitle to President.