Bug? LOCK_TIMEOUT is set to 2000 instead of -1

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
skirsche
Posts: 8
Joined: Fri 03 Apr 2009 06:59

Bug? LOCK_TIMEOUT is set to 2000 instead of -1

Post by skirsche » Mon 23 Aug 2010 14:56

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
Devart Team
Posts: 2888
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Wed 25 Aug 2010 10:00

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

brace
Posts: 213
Joined: Wed 14 Feb 2007 08:26

Post by brace » Mon 04 Oct 2010 08:32

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
Posts: 296
Joined: Thu 12 Oct 2006 09:34

Post by Ludek » Mon 04 Oct 2010 14:38

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
Posts: 213
Joined: Wed 14 Feb 2007 08:26

Post by brace » Mon 04 Oct 2010 15:46

Ludek wrote: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
Devart Team
Posts: 2888
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Tue 05 Oct 2010 09:28

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
Posts: 213
Joined: Wed 14 Feb 2007 08:26

Post by brace » Tue 05 Oct 2010 11:41

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
Devart Team
Posts: 2888
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Wed 06 Oct 2010 10:25

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

Post Reply