I'd like to know if there is a way to lock a record without making any update on it. This sometimes very useful to have this behavior in order to be sure that the data shown to the user can not be changed during his visualization. I'm using usually Oracle which has row locking mechanism as default behavior.

With Oracle, you can make a SQL statement likes this : SELECT col1, col2 FROM tableWithPK WHERE colPK = 32 FOR UPDATE. This statement will start a new transaction if necessary, and the row is only available for reading.

I know that SQL Server mechanisms are a bit different: but maybe someone can tell me how it possible to achieve this using LLBLGen.

Thanks in advance.

kenpanda wrote:

I'd like to know if there is a way to lock a record without making any update on it. This sometimes very useful to have this behavior in order to be sure that the data shown to the user can not be changed during his visualization. I'm using usually Oracle which has row locking mechanism as default behavior.

With Oracle, you can make a SQL statement likes this : SELECT col1, col2 FROM tableWithPK WHERE colPK = 32 FOR UPDATE. This statement will start a new transaction if necessary, and the row is only available for reading.

I know that SQL Server mechanisms are a bit different: but maybe someone can tell me how it possible to achieve this using LLBLGen.

Thanks in advance.

I think you would have to begin a transaction using READ COMMITED or higher isolcation before doing the SELECT. That will accomplish what you want.

This is probably what Oracle is doing for you in the back ground. I assume in Oracle you have to unlock the record somehow if you don't do the update? In SQL you would just rollback the transaction you had started.

BOb

I tried the two IsolationLevel allowed by Oracle (ReadCommited and Serializable), none lock the record. Unless, I make another error. When you try this :

Transaction transManager = new Transaction(IsolationLevel.ReadCommitted, "myFristTrans");
            MigrationStepsEntity mse = new MigrationStepsEntity();
            transManager.Add(mse);
            mse.FetchUsingPK(1200);
            mse.ErrorCode = 1;
            mse.Save();

and that you activate the trace through the app.config file :

<configuration>
  [...]
 <system.diagnostics>
    <switches>
      <add name="SqlServerDQE" value="3" />
      <add name="AccessDQE" value="4" />
      <add name="OracleDQE" value="4" />
      <add name="FirebirdDQE" value="4" />
      <add name="MySqlDQE" value="4" />
      <add name="DB2DQE" value="4" />
      <add name="PostgeSqlDQE" value="4" />
      <add name="ORMGeneral" value="4" />
      <add name="ORMStateManagement" value="4" />
      <add name="ORMPersistenceExecution" value="4" />
    </switches>
  </system.diagnostics>
</configuration>

You will get this output :

Method Enter: TransactionBase.CTor
:   Transaction name: myFristTrans. Isolation level: ReadCommitted.
Method Exit: TransactionBase.CTor
'EnsystaImporter.vshost.exe' (Managed): Loaded 'C:\MIS_DEV\DOTNET.1XX\EnsystaIntegrator\VisualStudio\EnsystaImporter\bin\Debug\SD.LLBLGen.Pro.DQE.Oracle10g.NET20.dll', No symbols loaded.
Method Enter: DaoBase.PerformFetchEntityAction
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT "ENSYSTA_IMPORT"."MIGRATION_STEPS"."MIGRATION_STEP_ID" AS "MigrationStepId", "ENSYSTA_IMPORT"."MIGRATION_STEPS"."DESCRIPTION" AS "Description", "ENSYSTA_IMPORT"."MIGRATION_STEPS"."STEP_CLASS" AS "StepClass", "ENSYSTA_IMPORT"."MIGRATION_STEPS"."STATUS" AS "Status", "ENSYSTA_IMPORT"."MIGRATION_STEPS"."ERROR_CODE" AS "ErrorCode", "ENSYSTA_IMPORT"."MIGRATION_STEPS"."ERROR_MESSAGE" AS "ErrorMessage" FROM "ENSYSTA_IMPORT"."MIGRATION_STEPS" WHERE ( "ENSYSTA_IMPORT"."MIGRATION_STEPS"."MIGRATION_STEP_ID" = :MigrationStepId1)
    Parameter: :MigrationStepId1 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1200.

Method Exit: CreateSelectDQ
Method Enter: DaoBase.ExecuteSingleRowRetrievalQuery
Executed Sql Query: 
    Query: SELECT "ENSYSTA_IMPORT"."MIGRATION_STEPS"."MIGRATION_STEP_ID" AS "MigrationStepId", "ENSYSTA_IMPORT"."MIGRATION_STEPS"."DESCRIPTION" AS "Description", "ENSYSTA_IMPORT"."MIGRATION_STEPS"."STEP_CLASS" AS "StepClass", "ENSYSTA_IMPORT"."MIGRATION_STEPS"."STATUS" AS "Status", "ENSYSTA_IMPORT"."MIGRATION_STEPS"."ERROR_CODE" AS "ErrorCode", "ENSYSTA_IMPORT"."MIGRATION_STEPS"."ERROR_MESSAGE" AS "ErrorMessage" FROM "ENSYSTA_IMPORT"."MIGRATION_STEPS" WHERE ( "ENSYSTA_IMPORT"."MIGRATION_STEPS"."MIGRATION_STEP_ID" = :MigrationStepId1)
    Parameter: :MigrationStepId1 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1200.

If you take a look to the generated queries, you will see that, even a transaction is started, the record is NOT locked.

kenpanda wrote:

I'd like to know if there is a way to lock a record without making any update on it. This sometimes very useful to have this behavior in order to be sure that the data shown to the user can not be changed during his visualization.

This isn't supported, because it can lead to severe problems: locks which are held for a long time (long as in > a couple of ms) hurt performance severily and also can lead to problems when the user walks away to go to lunch and forgets to close the screen.

I'm using usually Oracle which has row locking mechanism as default behavior.

With Oracle, you can make a SQL statement likes this : SELECT col1, col2 FROM tableWithPK WHERE colPK = 32 FOR UPDATE. This statement will start a new transaction if necessary, and the row is only available for reading.

I know that SQL Server mechanisms are a bit different: but maybe someone can tell me how it possible to achieve this using LLBLGen. Thanks in advance.

You should look into using concurrency control using a concurrencypredicatefactory. If you don't want anything to get changed, make your application use 'functionality' locking, i.e. lock a piece of the application based on flags you implement yourself so only one user can for example edit a user.

I'm not fully agree with your response but thanks for your assitance

kenpanda wrote:

If you take a look to the generated queries, you will see that, even a transaction is started, the record is NOT locked.

I'm not sure how you can tell that be looking at the generated query? Ado.Net should have sent a BEGIN TRANSACTION to your server, you won't see that in the LLBLGEN Pro trace. Can you trace the commands sent to Oracle?

Try this test... Run your code upto the fetch line and suspend it. Then go into your query tool, open a connection and try to update that record. I assume you will get an error because the record is locked. It has to be if your select was started within a "ReadCommited" transaction.

BOb

Hi,

I agree with Frans, locking a record for long time can hurt performace; but if that what you want then you can try the following:

• add a dummy column to the table that you want this feature.
• start a Read-commited transaction.
• do a simple update to the dummy column using the primary key of the record to locate the right one.
• read the entity (record) and show it to the user. while the user if working with the entity the record is locked in the database.
• save any update done by the user.
• commit the transaction and close the connection.

In your example, you need to use serializable and not readcommitted (you could use repeatableread on sql server) to achieve the desired effect.

However I would follow Frans' advice - indeed the phrase "hurt performance" may be an understatement - on sql server (and Oracle IIRC) depending on isolation level and timeout settings, this may well block other queries against that table making the system look like it has hung until it fails with a timeout. This is almost certainly not how to implement the feature you are looking for on most database systems. Other strategies, such as adding a "RowIsLocked" field to the table and managing that explicitly are preferable (although these are not without problems mentioned above). In general, optimistic concurrency is the best strategy even though users tend to be a bit upset if they make changes that they cannot save.

Locking has been a sticky issue for my heavily-used applications too. We've found that handling it as a concurrency issue (we do optimistic checking by using a timestamp field) has greatly simplified the problem and increased performance at the same time.

For the most part I have read-only screens explicitly request the "nolock" option to avoid deadlocks with threads that seek to update the data. My theory is this: If you're locking a record just to read it, you force a user seeking to update the data to wait. You've accomplished nothing by doing so, since the user seeking to update will likely wait it out and update the data as soon as the reader releases the lock.

So even though in that case the reader has technically avoided a "dirty read", practically speaking that have not - after they released the lock the data was updated anyway and their read of the data was made "dirty".

It's new years eve - hope I've made myself clear! Happy New Year everybody!

Thanks a lot for your responses, and best wishes for 2007 ! Let assume that I don't want to make a database vendor independent application. I'm not an expert with MS SQL Server, but I think that Oracle has a lock mechanism almost different than SQLSrvr. Oracle never blocks readers, and you can use a statement like 'SELECT FOR UPDATE NOWAIT' to avoid timeout issue. This is the reason why I think that there is no performance issue. I don't understand why to reinvent the wheel for lock strategy when this feature is embedded by the database itself.

kenpanda wrote:

Thanks a lot for your responses, and best wishes for 2007 ! Let assume that I don't want to make a database vendor independent application. I'm not an expert with MS SQL Server, but I think that Oracle has a lock mechanism almost different than SQLSrvr. Oracle never blocks readers, and you can use a statement like 'SELECT FOR UPDATE NOWAIT' to avoid timeout issue. This is the reason why I think that there is no performance issue. I don't understand why to reinvent the wheel for lock strategy when this feature is embedded by the database itself.

Then why do you need a lock? If the lock doesn't block anything, there's no need for setting an explicit lock as you will get that automatically anyway. A lock which is set explicitly is there to prevent others from reading, otherwise why set the lock in the first place?