[LOCK]Select-Timeout at ilReadUnCommitted

[ReeBo]

I try to update some rows in a ReadUnCommitted-Transaction.
If a row was updated, a another connection or program could not select this row until the transaction is committed or rollback.

Why? With ReadUncommitted, it have to show the updated row.

I use a MS SQL SERVER 2008 (Windows XP Prof.) and Delphi XE2 (Windows 7 Prof.) with SDAC 6.1.4 Components.

I have create a little example project:

Code: Select all

program Project1;
{$APPTYPE CONSOLE}
{$R *.res}
uses
  System.SysUtils, MSAccess, DBAccess, ActiveX;
var
  con, con2 : TMSConnection;
  que, que2 : TMSQuery;

begin
    CoInitialize(nil);
    con := TMSConnection.Create(nil);
    con2 := TMSConnection.Create(nil);
    que := TMSQuery.Create(nil);
    que2 := TMSQuery.Create(nil);
    try
      //Set Connection-Settings for SQL-Server
      con.LoginPrompt := false;
      con.Server := '';
      con.Username := '';
      con.Password := '';
      con.Database := '';
      con.IsolationLevel := ilReadUnCommitted;
      con.Open;
      con2.LoginPrompt := false;
      con2.ConnectString := con.ConnectString;
      con2.Options.DefaultLockTimeout := 1;
      con2.Open;

      //Set Query-Settings
      que.Connection := con;
      que.LockMode := lmOptimistic;
      que2.Connection := con2;

      //Create and Fill Test-Table
      que.SQL.Text := 'CREATE TABLE [dbo].[Test]([ID] [int] IDENTITY(1,1) NOT NULL, [Txt] [varchar](10) NULL)';
      que.Execute;
      que.SQL.Text := 'INSERT INTO [dbo].[Test] ([Txt] ) VALUES (''Text1'')';
      que.Execute;
      que.SQL.Text := 'INSERT INTO [dbo].[Test] ([Txt] ) VALUES (''Text2'')';
      que.Execute;

      //Update-Query
      que.SQL.Text := 'UPDATE [dbo].[Test] SET [Txt] = ''HELLO'' WHERE ID = 2';

      //Start Transaction and execute Update-Query
      con.StartTransaction;
      que.Execute;

      try
        que2.SQL.Text := 'SELECT * FROM [dbo].[Test]';
        que2.Execute; //<<-- alert TIMEOUT
      except
        on e : Exception do
          WriteLn(e.Message);
      end;
      WriteLn('Press Enter for Commit!');
      Readln;  //here the script wait! You could call selection from other programs

      //kill Transaction
      con.Commit;

      //delete Test-Table
      que.SQL.Text := 'DROP Table [dbo].[Test]';
      que.Execute;

      con.Close;
    finally
      FreeAndNil(que2);
      FreeAndNil(con2);
      FreeAndNil(que);
      FreeAndNil(con);
      CoUninitialize;
    end;
end.

I try also LockMode lmPessimistic and lmNone, but it doesn't work.

Thanks for help.

Greets
ReeBo

[AndreyZ]

To solve the problem, you should use the ReadUnCommitted transaction isolation level for your second connection (when it is needed to read uncommitted changes), not for the first connection. You should remove the

Code: Select all

con.IsolationLevel := ilReadUnCommitted;

code and add the

Code: Select all

con2.IsolationLevel := ilReadUnCommitted;

code to your project.