How to use new table that create within a transaction

Question

When I create a table within a transaction this table is not accessible in transaction but all other table that created before transaction are accessible. How can I solve this problem?

procedure TForm1.Button1Click(Sender: TObject);
var MyAdo:TADOQuery;
    a,b:integer;
begin
  AdoConnection1.ConnectionString := 'Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master;Data Source=MyPCNAME'
  MyAdo:=TAdoQuery.Create(Application);
  MyAdo.Connection := ADOConnection1;
  MyAdo.SQL.Add('Create table tempdb..test1 (TBID int) '
              +' Insert Into tempdb..test1 Values (1) '
              +' Insert Into tempdb..test1 Values (2) '
               );
  MyAdo.ExecSQL;

  ADOConnection1.BeginTrans;

  MyAdo.SQL.Clear;
  MyAdo.SQL.Add('Create table tempdb..test2 (TBID int) ');
  MyAdo.ExecSQL;


  MyAdo.SQL.Clear;
  MyAdo.SQL.Add('Select max(TBID) Mx1 From TempDb..Test1 ');
  MyAdo.Open;
  A:=MyAdo.FieldByName('Mx1').AsInteger;        //Result:2

  MyAdo.SQL.Clear;
  MyAdo.SQL.Add('Select max(TBID) Mx2 From TempDb..Test2 ');
  MyAdo.Open;
  B:=MyAdo.FieldByName('Mx2').AsInteger;        //Deadlock!!!!!!!!! It need to force the application to close

  ADOConnection1.CommitTrans;
end;

Try by creating a stored procedure in your dbms and put all sql code on that. Afterwards, invoke the stored procedure in your delfi code. — mohabbati, Oct 20 '18 at 11:49
On a side note, why are you using the `master` database? You're supposed to create your own... — Jerry Dodge, Oct 20 '18 at 13:07
@Jerry Dodge: Above code is sample. In the original code I read/write to different databases, So I first connect to master and read/write to other databases with (DatabaseName..TableName). I haven't problem for read/write to these databases but when I create temp table in tempdb I can't read from it — mina, Oct 20 '18 at 13:29

score 1 · Accepted Answer · answered Oct 20 '18 at 15:34

Try changing your code as follows

  [...]
  MyAdo.Close;
  MyAdo.SQL.Clear;
  MyAdo.SQL.Add('Select max(TBID) Mx2 From TempDb..Test2 with (nolock)');
  MyAdo.Open;
  B:=MyAdo.FieldByName('Mx2').AsInteger;   //  No Deadlock

This works for me without blocking on SS2014. But I think you might do better to read around the subject, starting with f.i. SQL Server SELECT statements causing blocking

Btw, contrary to what is said in that q, setting the AdoConnection's IsolationLevel to ilReadUncommitted did not avoid the blocking with your original code.

How to use new table that create within a transaction

1 Answers1