mORMot Open Source

Bascy

Member

From: The Netherlands

Registered: 2012-06-22

Posts: 108

Deadlock in transaction using MS SQL over OleDB?

I've created the following unittest to test if using a transaction when applying an update is working. This tests works on oracle (OCI), our own nexus driver, but now i've tried it on MS SQL using OleDB and the method stalls at line indicated in the code ...

procedure TMormotDBBaseTransactionTest.TestCommitTransaction;
var
  Rec: TSQLUnitTestSampleRecord;
  lStat: TSQLDBStatement;
const
  cNewQuestion = 'New Question!';
  cID = 2;
begin
  CreateSmallModelData(Client); // Add 10 records to the UnitTestSampleRecord table

  //Create another connection to test table contents outside of transaction
  Conn.Connect;

  Client.TransactionBegin(TSQLUnitTestSampleRecord);
  Rec := TSQLUnitTestSampleRecord.CreateAndFillPrepare(Client,'ID=?', [], [cID]);
  try
    Rec.FillOne;
    Rec.Question := cNewQuestion;
    Client.Update(Rec);

    // Use other connection to check that the update has not been committed yet
    lStat := Conn.NewStatementPrepared('Select Question from UnittestSampleRecord where id='+ IntToString(cID), true);
    try
      lStat.ExecutePrepared;  <<<- Here execution halts!!! No exceptions, just freezes
      lStat.Step(True);
      CheckNotEqualsString(Rec.Question, lStat.ColumnString(0));
    finally
      lStat.Free;
    end;

    Client.Commit;

    lStat := Conn.NewStatementPrepared('Select Question from UnittestSampleRecord where id='+ IntToString(cID), true);
    try
      lStat.ExecutePrepared;
      lStat.Step(True);
      CheckEqualsString(Rec.Question, lStat.ColumnString(0));
    finally
      lStat.Free;
    end;

  finally
    Rec.Free;
  end;
end;

When pausign and looking at the the callstack:

:7749f8b1 ntdll.NtWaitForSingleObject + 0x15
:76151194 kernel32.WaitForSingleObjectEx + 0x43
:6fb02985 ; C:\Windows\SysWOW64\DBnmpNTw.dll
:6faaacb6 ; C:\Windows\SysWOW64\DBNETLIB.DLL
:6a4aa077 ; C:\Program Files (x86)\Common Files\System\Ole DB\sqloledb.dll
:6a47faa2 ; C:\Program Files (x86)\Common Files\System\Ole DB\sqloledb.dll
:6a47f864 ; C:\Program Files (x86)\Common Files\System\Ole DB\sqloledb.dll
:6a47f652 ; C:\Program Files (x86)\Common Files\System\Ole DB\sqloledb.dll
:6a48182f ; C:\Program Files (x86)\Common Files\System\Ole DB\sqloledb.dll
:6a4788cb ; C:\Program Files (x86)\Common Files\System\Ole DB\sqloledb.dll
:6a47f5c6 ; C:\Program Files (x86)\Common Files\System\Ole DB\sqloledb.dll
:6a6a12ff ; C:\Program Files (x86)\Common Files\System\Ole DB\oledb32.dll
SynOleDB.TOleDBStatement.ExecutePrepared
uTestMormotDBBaseCommon.TMormotDBBaseTransactionTest.TestCommitTransaction

it looks to me as if its waiting for the transaction .. while the statement shuold be excecuted in another connection (and does so with i.e. oracle OCI)

Last edited by Bascy (2012-09-13 09:58:59)

mpv

Member

From: Ukraine

Registered: 2012-03-24

Posts: 1,571

Website

Re: Deadlock in transaction using MS SQL over OleDB?

Everything is OK in this example. MS SQL default transaction model is read commited, so second transaction must wait until commit or rollback of first. About transaction isolation level good article is here http://www.postgresql.org/docs/9.1/stat … n-iso.html