How can I include the table hint WITH(UPDLOCK) in certain SELECT statements generated by LLBL? We are using the adapter scenario and I didn't find how to do this.

Background: Every record should be accessed by only one client at the same time. The usage is SELECTing a record, changing its data in some way and writing it back with UPDATE. From the beginning of the SELECT statement up to the end of the UPDATE command no other client should have access to this record.

We already use short living transactions and the isolation level REPEATABLE READ, but we still encounter deadlock situations. So we need to place an UPDATE LOCK on the record while executing the first SELECT statement. This would cause subsequent SELECT statements to wait until the first transaction is completed.

You shouldn't be experiencing the deadlocks if you are using the same adapter. Could you post some of the code that is causing the problems and maybe we could provide some specific examples.

Sorry, I don't have code compact enough to post it here. But consider this situation:

Two clients are accessing our service app at the same time. Inside the service two threads are started (managed by IIS). Client 1 (thread 1) starts a transaction and executes a SELECT statement for record 123. Client 2 (thread 2) starts a transaction and also executes a SELECT statement for record 123. This is working fine even for isolation level REPEATABLE READ, because the SELECT statements places only shared locks (READ LOCKs) on the record.

If now thread 1 trys to execute an UPDATE statement, the UPDATE waits until transaction 2 is completed either with commit or rollback. But if thread 2 now starts also an UPDATE statement, we have a deadlock situation. This because the UPDATE commands try to place exclusive locks (UPDATE LOCKs) on the record and have to wait until all other LOCKS (shared and execlusive) are released.

Here is a short code snipped which demonstrates this situation:

public void WebServiceMethod()
{
  DataAccessAdapter adapter = new DataAccessAdapter(true);
  adapter.StartTransaction(IsolationLevel.RepeatableRead, "Test");

  try
  {
    CustomerEntity customer = new CustomerEntity(123);
    adapter.FetchEntity(customer);

    /* some time consuming processing comes here */
    customer.Name = "Test";

    adapter.SaveEntity(customer, false, null, false);
    adapter.Commit();
  }
  catch
  {
    adapter.Rollback();
    throw;
  }
  finally
  {
    adapter.Dispose();
    adapter = null;
  }
}

As you can see, the chance to create a deadlock is very high, if you call this method twice or more times at the same time.

One solution would be to reduce the isolation level. But this is forbidden by our business rules. Only one client should have access to a single record at the same time!

The onliest solution I found is to place exclusive locks (UPDATE LOCKs) together with the SELECT statements, so shared locks (READ LOCKs) will never be used. In SQL Server this is usually done with the table hint WITH(UPDLOCK).

Move the select outside the transaction: so first select, then start the transaction. Selects in transactions are better of being placed outside the transaction (if possible of course).

To have all selects hinted to NOLOCK, you should do: SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.UseNoLockHintOnSelects = true;

this places NOLOCK hints on selects. It's not documented because it should be avoided if possible and I'd start by moving selects outside the transaction start (or moving the transaction start to a point after the select).

SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.UseNoLockHintOnSelects = true;

How would that work in an ASP.NET environment, where I'm creating a new DataAccessAdapter for each page load? Would I need to call the above each time as well?

(Sweet feature BTW, and I can understand why it's not documented. But for ASP.NET stuff, where transactions are writing all the time typically to data that's not shared across users, this is pure gold. Prevents the 'Readers blocking Writers' issue.)

swallace wrote:

SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.UseNoLockHintOnSelects = true;

How would that work in an ASP.NET environment, where I'm creating a new DataAccessAdapter for each page load? Would I need to call the above each time as well?

It's a static var, so every query will use it.

(Sweet feature BTW, and I can understand why it's not documented. But for ASP.NET stuff, where transactions are writing all the time typically to data that's not shared across users, this is pure gold. Prevents the 'Readers blocking Writers' issue.)

It's not documented because it's a global feature, which means that if you don't want the hint to be added, you still get it added to the query.

Hello together,

in relation to this topic i've a question:

Exists in the meantime a way to specify also other hints for the sql server like UPDLOCK, READPAST or ROWLOCK?

Because the problem is, that 'NOLOCK'-selects will also returns uncommitted changes from db and you can'nt be sure if your data are valid and consistent!!!

Thx for your help at this point...

best regards

Stefan

No there's no way to specify these hints. There are also no plans to implement support for these hints at the moment for v2, perhaps in a later version (2.1)

Frank wrote:

public void WebServiceMethod()
{
  DataAccessAdapter adapter = new DataAccessAdapter(true);
  adapter.StartTransaction(IsolationLevel.RepeatableRead, "Test");

  try
  {
    CustomerEntity customer = new CustomerEntity(123);
    adapter.FetchEntity(customer);

    /* some time consuming processing comes here */
    customer.Name = "Test";

    adapter.SaveEntity(customer, false, null, false);
    adapter.Commit();
  }
  catch
  {
    adapter.Rollback();
    throw;
  }
  finally
  {
    adapter.Dispose();
    adapter = null;
  }
}

One way-around is to have a field that act as flag and update it directly (using the adapter.UpdateEntitiesDirectly and a filter by the Customer primary key):

try
  {
    tmpCustomer = New CustomerEntity();
    tmpCustomer.Flag = 1
    filter = a new filter condition by the primary key = 123
    adapter.UpdateEntitiesDirectly(tmpCustomer, filter);

    CustomerEntity customer = new CustomerEntity(123);
    adapter.FetchEntity(customer);
...........

This is not elegant; but would work, because the first user that summit the update of the flag will block the others users until the transaction is commited or rolledback.