Bug? LOCK_TIMEOUT is set to 2000 instead of -1

[skirsche]

Microsoft's documentation indicates that the LOCK_TIMEOUT variable is set to -1 by default.

http://technet.microsoft.com/en-us/libr ... 89470.aspx
http://msdn.microsoft.com/en-us/library/ms182729.aspx
http://msdn.microsoft.com/en-us/library/ms177413.aspx

I got "Lock request time-out period exceeded" errors and was pretty sure there is no way they could occour, since I didn't touch this setting. By testing I found out that SDAC 4 sets it to 2000.

For now and in case it's not a bug I will just set LOCK_TIMEOUT to -1 after every connect.

The following code will show these messages (Delphi 5, SQL Server 2008):

Compiled against version 4.80.0.60 (not expected): '2000'
Compiled against version 3.80.0.36 (expected): '-1'

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
var
  Con: TMSConnection;
  Query: TMSQuery;
begin
  Query := nil;
  Con := TMsConnection.Create(nil);

  try
    Con.Server := 'LOCALHOST\SQLEXPRESS';
    Con.Username := 'sa';
    Con.Password := '';
    Con.Connect;

    Query := TMsQuery.Create(nil);
    Query.Connection := Con;
    Query.SQL.Text := 'SELECT @@LOCK_TIMEOUT';
    Query.Active := True;

    if not Query.Eof then
      ShowMessage(Query.Fields[0].Value);

  finally
    Query.Free;
    Con.Free;
  end;
end;

[Dimon]

Use the TMSConnectionOptions.DefaultLockTimeout property to specify how much time a transaction will wait for a lock.

[brace]

I reply to this thread since I have a question on LockTimeout.

On some SQL Server Express DB where too many users work sometimes I have the lock timeout error.

Can it be a good temporary workaround to set the TMSConnectionOptions.DefaultLockTimeout to a high value like 15000?

Of course the ideal solution is to perform an optimization of the queries or to upgrade the server, but at least in this way the user doesn't get disconnected and doesn't lose the functionality.

The default is 2000, the error comes only from time to time, so I guess that even setting 3000 would be enough, but do you see potential problems in setting it to 15000?

[Ludek]

I think, that only you und your customers can decide, what's the longest acceptable time with frozen application (or, with threads, for finishing the task)

[brace]

I think, that only you und your customers can decide, what's the longest acceptable time with frozen application (or, with threads, for finishing the task)

Yes sure, but if I have to choose between "application crash" and "wait 3 seconds" I choose for the second.

I can imagine there are scenarios when it makes sense to succesfully use the Lock Timeout, but honestly, in a Delphi application I would use only main connection timeout, then if I am using some low priority query that I can easily "kill" without compromising the correct functionlaity of the application I'd rather write a thread and handle "my timeout" there.

[Dimon]

For different cases LockTimeout should be set to different values, but 2000 is an optimal value to wait and don't think that the application is frozen.

[brace]

Yes ok, but imagine on a SearchButtonClickEvent I write

Code: Select all

msqueryCUstomersList.Open

The server is suffering and the UI is frozen for 2 seconds, after that the user has a

LockTimeout exceeded.

One can intercept the exception and in this case telle the user

"The server seems busy, please try again".

BUT what if I need to run 5 queries in a row like

Code: Select all

msquery1.open;
do something;
msquer2.open;
do something;
msquery3.open;
do something;
msquery4.open;
do something;
msquery5.open;
do something;

if msquery4 fails because of locktimeout it is painful.

A solution could be that only some TMSQueries use LockTimeout, but as far as I can understand being a TMSConnection property this is not possible (unless I want to use more connections).

[Dimon]

You are right, lock timeout is set for a whole connection and can not be used only for one query.