Lock errors with MSSQL query (sometimes)

[kneighbour]

I have a program that runs pretty much all day unattended. Sometime I get a lock error from the MS SQL Server
"Lock request time out period exceeded."

All I am doing is running a readonly SELECT where I fetch around 1000 records for processing. I basically loop around and do this 'Select 1000 records', 'Process those records', get the next 1000 records, etc.

On the Unidac Query, I have
Lockmode=lmNone
ReadOnly=true
Options.QueryRecCount=true

On the Connection component, the LockTimeout = 9000

I am not using any specific Transaction stuff - just the defaults. I am using this basic code below to attempt a retry of the SELECT after an error message with the word 'Lock' in it. So far I get the exception error ok, but the loop does not repeat. Still working on that but it is an error I cannot duplicate, so it is difficult to work with.

Code: Select all

  blCanRetry := true;
   repeat
      inc(ii);
      try
          query.open;
          ii := 99; //quit
      except
         on e: exception do
            begin
               blCanRetry := (pos('LOCK', uppercase(e.message)) > 0);
               sleep(500);
            end;
      end;
   until (ii >= 20) and blCanRetry;

I read that I should perhaps try setting the isolation level to REPEATABLE READ. I don't see where I can do that? Is there some other locking option that I can use with the UniDac components?

Using UniDac 7.4.11 in Windows 7 and Delphi XE7

[Stellar]

Unfortunately, we could not reproduce the issue. Detecting the exception type by name is not reliable, as it depends on the server.
You can handle the EUniError exception and identify the exception by code, in this case the error code for "Lock request time out period exceeded" is 1222. For example:

Code: Select all

  i := 0;
  repeat
    Inc(i);
    try
      UniQuery.Open;
      Break; //quit
    except
      on E: EUniError do
      begin
        if E.ErrorCode = 1222 then
          Sleep(500)
        else
          Break; //quit
      end;
      on E: Exception do
      begin
        Break; //quit
      end;  	
    end;
  until (i >= 20);

You can also try using a dirty read to retrieve table data. READ UNCOMMITTED transactions are not blocked by exclusive locks that would prevent the current transaction from reading rows that have been modified but not committed by other transactions. When this option is set, it is possible to read uncommitted modifications, which are called dirty reads. For example:

Code: Select all

TUniTransaction(UniConnection1.DefaultTransaction).IsolationLevel := ilReadUnCommitted;
UniConnection1.Connect;
UniQuery.Open;

[kneighbour]

Thanks for those tips. I like the "Dirty Read" solution. Will give that a try.

[Stellar]

Glad to see that the issue was resolved.
Feel free to contact us if you have any further questions about our products.