Locking is still active after exception in Deletion

[cis-wurzen]

If an exception is raised during deletion of one record with TSmartQuery the locking of the record is still active. See sample.

Code: Select all

program OdacLockingTestSample;

{$APPTYPE CONSOLE}

uses
  DB, Ora, OraScript, OraSmart, sysutils;

procedure CreateOracleTables(ASession: TOraSession);
var
  OraScript: TOraScript;
begin
  //script for creation of Oracle tables
  OraScript := TOraScript.Create(nil);
  try
    OraScript.Session := ASession;
    OraScript.SQL.BeginUpdate;

    OraScript.SQL.Add('DROP TABLE B;');
    OraScript.SQL.Add('DROP TABLE A;');

    OraScript.SQL.Add('CREATE TABLE A (');
    OraScript.SQL.Add('  ID  NUMBER(9) NOT NULL);');
    OraScript.SQL.Add('ALTER TABLE A ADD (CONSTRAINT PK_A_ID PRIMARY KEY (ID));');

    OraScript.SQL.Add('CREATE TABLE B (');
    OraScript.SQL.Add('  ID   NUMBER(9) NOT NULL,');
    OraScript.SQL.Add('  AID  NUMBER(9));');
    OraScript.SQL.Add('ALTER TABLE B ADD (CONSTRAINT PK_B_ID PRIMARY KEY (ID), CONSTRAINT FK_B_AID FOREIGN KEY (AID) REFERENCES A (ID));');

    OraScript.SQL.Add('INSERT INTO A (ID) VALUES (1);');
    OraScript.SQL.Add('INSERT INTO B (ID,AID) VALUES (1,1);');
    OraScript.SQL.Add('COMMIT;');

    OraScript.SQL.EndUpdate;
    OraScript.Execute;    
  finally
    OraScript.Free;
  end;
end;

procedure RunDemonstration(Session1, Session2: TOraSession);
var
  qr1,qr2: TSmartQuery;
begin
  qr1 := TSmartQuery.Create(nil);
  qr2 := TSmartQuery.Create(nil);
  try
    qr1.Session   := Session1;
    qr1.SQL.Text  := 'SELECT * FROM A';
    qr1.KeyFields := 'ID';
    qr1.LockMode  := lmLockImmediate;
    qr1.Open;

    qr2.Session   := Session2;
    qr2.SQL.Text  := 'SELECT * FROM A';
    qr2.KeyFields := 'ID';
    qr2.LockMode  := lmLockImmediate;
    qr2.Open;

    //Step 1: We want to delete a record in Table A
    try
      qr1.Delete;
      //Before deletion the record is locked by ODAC. Then Odac try to delete the record.
      //It can't do that because of the given ORACLE constraint and an exception is raised.
      //But the locking on the record is still active.
      //But why the locking which is set befor deletion isn't removed after raising of the exception?
    except
    end;

    //Step 2: To show that the locking of the record in Session1 is still active we try to lock the record with an other Session
    try
      qr2.Lock;
      //The record can't be locked because the locking set by session1 is still active.
    except
      on E:Exception do
      begin
        Writeln(E.Message);
        Readln;
      end;
    end;

  finally
    qr1.Free;
    qr2.Free;
  end;
end;


var
  UserName,Password,Server: String;
  Session1,Session2: TOraSession;
begin
  //Logon information - Please fill out !!!!
  UserName := '';
  Password := '';
  Server   := '';


  Session1 := TOraSession.Create(nil);
  Session2 := TOraSession.Create(nil);
  try
    //logon to Database
    Session1.LoginPrompt := False;
    Session1.UserName    := UserName;
    Session1.Password    := Password;
    Session1.Server      := Server;
    Session1.Open;
    
    Session2.LoginPrompt := False;
    Session2.UserName    := UserName;
    Session2.Password    := Password;
    Session2.Server      := Server;
    Session2.Open;
  
    //creation of needed Oracle tables
    CreateOracleTables(Session1);

    //run test
    RunDemonstration(Session1, Session2);
  
  finally
    Session1.Free;
    Session2.Free;
  end;
end.

[AlexP]

hello,

Thank you for the information, we have reproduced the problem and will try to fix it in the nearest version.