Many problems can occur in applications when multiple users are allowed to edit the same record at the same time. Some simple applications take a “last edit wins” approach, where the last person to save their changes gets their changes applied in the end. This has the obvious problem that any user who changed the record between the time the last user read the record and saved his change will get their changes lost. This issue is usually unacceptable in business applications.
To solve this issue, many applications add the ability to detect if another user has changed the same record between the time you read it and attempted to save changes to it. If a change by another user is detected, the save is aborted and a message is presented to the user saying someone else has edited the same record. In a lot of applications, this is where the concurrency features end. The user is expected to discard their changes, read the most up-to-date copy of the record, and reapply the changes.
Visual Studio LightSwitch takes concurrency one step further and allows the end user the option of “merging” his changes with the conflicting change. That way, no changes will be overwritten and the user doesn’t have to reapply all his changes. This saves a lot of time for the business user.
Concurrency in Visual Studio LightSwitch 2011
In order to detect changes, LightSwitch 2011 uses the original values of all the properties of an entity. So if you have two string properties, FirstName and LastName, a SQL update statement for the entity will look like
UPDATE Customers SET FirstName = 'new FirstName', LastName = 'new LastName' WHERE ID = 2 AND FirstName = 'original FirstName' AND LastName = 'original LastName'
Notice the WHERE clause has expressions for FirstName = ‘original FirstName’ and LastName = ‘original LastName’. If someone else has changed FirstName’s value or LastName’s value, these expressions will evaluate to FALSE, which will then cause the update to fail. If the update fails, a concurrency error is raised to notify the user the record has already been changed.
Issues with Concurrency in Visual Studio LightSwitch 2011
There are three primary issues with how concurrency is implemented in LightSwitch 2011.
First, some columns cannot be compared in SQL Server. Examples of these column types are text, image, xml, etc. Since LightSwitch relies on comparing original values in the WHERE clause, it is not possible to detect concurrency conflicts on these column types. If only these non-comparable columns have been changed, LightSwitch will suffer from the “last edit wins” problem illustrated above.
Second, this approach assumes that the client is trusted to send in the correct original values. This puts extra responsibilities on the client and makes the server trust the original values coming from the client.
Third, if every update request includes all the new values plus all the original values, the size of every update request is essentially doubled. Similarly, delete requests would need all of the original values, bloating the request. For small applications this isn’t a terrible problem, but it doesn’t scale well to large applications.
Concurrency in Visual Studio LightSwitch 2012
Visual Studio LightSwitch 2012 uses the OData protocol for communication between the client and server. The OData protocol uses the HTTP ETag part of the HTTP protocol to enable concurrency conflict detection. Basically, every property that should be used for concurrency conflict detection has its original value serialized into the ETag value when the item is read. This ETag value is sent to the client along with all the values of the entity being read. A client making an update will submit this ETag value along with the updated property values to the server. The server will check to make sure the ETag is valid (i.e. no one else has made an update to the record in the meantime). If the ETag isn’t valid, the update is rejected and the user is informed that someone else has edited the same record. See Pablo Castro’s blog post “Optimistic Concurrency & Data Services” for more information on how WCF Data Services implements concurrency for the OData protocol.
Now, an astute reader will notice a fourth problem when trying to combine LightSwitch 2011’s concurrency behavior with the OData protocol. It is the same as the third problem above: doubling the size of the payload, only now the problem occurs on all read operations and not just during an update operation. Since LightSwitch 2011 wants to use all properties for concurrency conflict detection, and the ETag contains the serialized original values of all properties used for concurrency, all property values will be serialized twice in a read payload.
An even worse problem is that if your entity is too big (many properties or really long strings), it won’t fit in the ETag since ETags are commonly put in HTTP headers, which most clients and servers impose a size limit for security reasons.
Because of all these issues, LightSwitch 2012 has changed its default concurrency conflict detection.
When you create a table in Visual Studio LightSwitch 2012 (or upgrade an existing application from a previous version), a generated column is added to your tables named RowVersion. The RowVersion column is a SQL Server rowversion or timestamp column. A rowversion column in SQL Server gets updated to a new value every time the record is updated. This makes it perfect to use for concurrency conflict detection since it is relatively small (8 bytes) and is guaranteed to be changed whenever any column is changed.
This solves all of our problems listed above. The overhead of detecting concurrency conflicts is now minimal – an extra 8 bytes is attached to each record that is strictly used for concurrency conflict detection. This falls well inside any HTTP header size limits and doesn’t bloat read or update requests. Whenever any column is changed, the rowversion column is updated so now all concurrency conflicts can be detected. And the server doesn’t require the client to send all of the original values with an update request. The client is only required to send the 8 byte ETag value.
The RowVersion property is not shown in the Entity Designer, but is shown in other places in the LightSwitch IDE. It was removed from the Entity Designer because it cluttered up the designer and developers can’t make any changes to it anyway. Rest assured that this property doesn’t show up in the screens you create by default. So your end users won’t know the difference.
An interesting feature you can add to your application is to detect whether a record has been changed since the last time it was read. To do this, you can create a query with @Id and @RowVersion parameters. Use these parameters to filter the records where Id == @Id and RowVersion == @RowVersion. You can pass in a record’s Id and current RowVersion values into the parameters of this query. If no record is returned, then the record has been modified (or deleted). If a record is returned, then the record must not have changed in the database.
When you attach to an existing database in LightSwitch 2012, there is no way for LightSwitch to generate a new rowversion column on your table. When attaching to an existing database, LightSwitch will never make modifications to that database’s structure. However, you can take advantage of these concurrency enhancements yourself by adding a rowversion column to your tables using SQL Management Studio or Visual Studio’s SQL Server Object Explorer window.
When LightSwitch attaches to or updates an external database and notices a rowversion column exists on the table, LightSwitch will use just that rowversion column for concurrency conflict detection – just like the ApplicationData source.
However in LightSwitch 2012, if you attach to an external database that doesn’t contain a rowversion column, LightSwitch will fall back to using all available columns for concurrency conflict detection. You will then have the same problems as listed above. In order to work around these issues, it is recommended to add a rowversion column to your database tables.
WCF RIA Service
LightSwitch 2012 now respects the three attributes used in WCF RIA Services to signify that a property should be used in concurrency conflict detection: TimestampAttribute, ConcurrencyCheckAttribute, and RoundTripOriginalAttribute. Any property marked with one of these attributes on your WCF RIA entity will be used for concurrency conflict detection. If your entity doesn’t have any of these attributes on its properties, then all properties will be used for concurrency conflict detection, just like in LightSwitch 2011.
If you are attaching to an OData service, LightSwitch 2012 will use the ETag value provided by the back-end OData service for concurrency checking. LightSwitch will attach the back-end ETag value to the entity and flow it through to the back-end when making update and delete requests.
Concurrency conflict detection is a low-level technical detail that normally you don’t need to worry about. LightSwitch will do what’s right to make it work as best as possible. However, sometimes you will run into cases where you need to understand how this technical detail works in order to make your application the best application it can be.
Hopefully you now understand the concurrency enhancements that were made in LightSwitch 2012 and why they were made.