Data access layer generating software approach

Hi Everybody,

I am evaluating on various optimistic locking mechanisms for implementation in a Data access layer code generating software.

First Approach: All column based locking.I see following demerits:

1.Can't compare CLOB and BLOB columns

2. Every column of the database entity(table) should have a value set.Means we should have values of all column before update or delete regardless of we need to update one column or more.

3.We have to preserve the old values so extra memory overhead.

Second Approach: Timestamp column based locking :

I see following demerits:

1.If we go for timestamp based locking ,then we have to create an extra column or an extra table for timestamp of all tables..I think by introduction of rowversion in sql server, the problem of extra column is solved now atleast for oracle and sqlserver.Not sure about access and mysql.

2.We have to join the extra table for whole database in the update or delete command.If we go for timestamp column in every table, that approach is very intrusive and is not applicable for existing databases. It also need to have a select and update of timestamp column at the start and end. All these will lead to slow down in performance though i don't know how much.

This has no disadvantage which is present in all column based locking like BLOB ,CLOB problem

Third approach:Coarse grained locking .It can be implemented for tables which are related by business logic. I think, it is very difficult to implement in automatic code generation scenario.

Can you people suggest the best approach in my case(DAL code generating product ) and why .I need a clear cut approach in my case..

Pour in with your comments....

Regards

Microsoft ISV Community Center Forums3

There are two things in your question:

- Locking

- Knowing if data has changed

The first question involves how you do transactions. It has an impact on how rows are locked. I would suggest that you have a look at this article.
http://msdn2.microsoft.com/en-us/library/ms173763.aspx

The second is introduced by a TimeStamp colomun. But you do not have to update the timeStamp, it is done automatically by the database engine. you just have to read it and compare with the one in the database when saving back your data.