LINQ to SQL - Optimistic locking

Venturing into the world of LINQ I'm determined to give it a go because writing 3 -10 sps per table is just a pain in the bottom.

If you come from a database background one of the things that will concern you is the SQL that LINQ generates. The good news is that you can capture all the SQL that LINQ generates by using the Log property of you data context. The following sets the PubsDataContext to log to the file c:\log.txt

PubsDataContext dc = new PubsDataContext();

TextWriter tw = new StreamWriter("c:\\log.txt");

dc.Log = tw;

What I noticed straight away was updates, by default when an update is performed, irrespective of what columns are changed, all the columns are specified in the WHERE clause. This is used to enforce optimistice locking. This is to  ensure that when you update data it hasn't already been updated by someone else. The downside however is that every column has to be checked.

You can change this behaviour on each column in the LINQ to SQL model that is generated by adding the UpdateCheck attribute i.e.

[Column(Storage="_col2", DbType="Int", UpdateCheck=UpdateCheck.Never)]

public System.Nullable<int> col2



However its a bit of a pain to do that on each column in each table. However there is light at the end of the tunnel. If you have a timestamp on the table only this column will be check to verify the record has't changed.

You might be still asking why I want this at all. Imagine your bank account (£100 balance), you've cash a cheque £50 and you use the cash machine to withdraw £100, what if it happens that the bank pays the cheque into your account at the same time you use the cash point, both  read the balance at £100, the chequed is cashed updating it to £150, but then the cash point withdrawl updates the £100 balance with the £100 withdrawl leaving you with a balance of £0. What happened to the cheque?

If the each transaction checked that the record had not been updated then it would have realised that the data had changed and the update would have failed.

The other option is pessimistic locking, which puts a lock on a record for the duration that the record is being read until it is updated. This is not very good for concurrency as locks cause blocking and so the number of users you application will be able to support will not be very high.

Published Monday, January 21, 2008 8:55 PM by simonsabin
Filed under:


Tuesday, January 22, 2008 8:25 AM by Barry

# re: LINQ to SQL - Optimistic locking

Putting a Timestamp column on each table also stops the update sql including every column in where clause.

Tuesday, January 22, 2008 3:13 PM by jonsayce

# re: LINQ to SQL - Optimistic locking

Have you looked at the performance of the generated SQL yet Si? I'd be interested to know what you make of it.

I did a brief profiler session just to see what it looked like but I'm hoping to get the chance to test it against some other data access layer candidates and see how they compare - I don't see how LINQ can really compete with static SPs or well-designed dynamic ones though.

Tuesday, January 22, 2008 3:58 PM by simonsabin

# re: LINQ to SQL - Optimistic locking

Not yet. There are a number of points to consider.

Its very easy for users to access all columns in a table by selecting the table and not the specific columns. This is just a question of good table design and making sure you don't have wide tables.

If you need 4 queries to select data 4 different ways. In which case you will end up with as any plans as you would SPs.

However the issue is two people writing the same functional query and getting two different queries resulting in redundant plans. Hopefully the use of LINQ as the middle layer should smooth out this issue, but I think it will be the biggest problem.

The final point is caching. LINQ has built in caching which means that in theory applications could avoid round trips to the database.

Tuesday, January 22, 2008 5:09 PM by AdamMachanic

# re: LINQ to SQL - Optimistic locking

There are lots of ways to implement pessimistic concurrency in the database -- row locks are not the only way to solve the problem, and the solution does not have to be a scalability bottleneck.  Please see the concurrency chapter of my book for more info :-)

Tuesday, January 22, 2008 5:20 PM by simonsabin

# re: LINQ to SQL - Optimistic locking

Very true, unfortunately I fell into the trap of mentioning something in a blog post when I needed a whole other blog post to explain :(