BDE, Delphi, ODBC, SQL Native Client & Dead lock

3
votes

We have some Delphi code that uses the BDE to Access SQL Server 2008 through the SQL Server Native Client ODBC driver (2005 version). Our issue is that we're experiencing some deadlock issues in a loop doing inserts to multiple tables.

The whole loop is done within a [TDatabase].StartTransaction. Looking at the SQL Server Profiler we clearly see that at one point during the loop the SPID (Session ID?) change, and then we naturally end up with a deadlock. (Both SPID doing inserts to the same table)

It seems like the BDE at some point does a second connection to the DB...

(Although I would love to skip the BDE, it's currently not possible. )

Anyone with experiences to share?

3
delphiodbcdeadlockbdesql-server-native-client
Because the codebase is about 13 years old, and is heavily dependent on BDE. Besides we need to support both Interbase and MSSQL. – EspenS
i'm a little confused when you say the "spid change". that is probably the cause of the deadlock .. you can't start a transaction in one spid and try to do inserts in a different one or end the transaction in a different one. do you know how you're ending up with two spids? you're not multi-threading, so are you creating a second database connection? – Don Dickinson
The spid change is more than likely the reason for the deadlock. I have no idea how the spid suddenly changes. Using SQL Server Profiler I clearly see that there's a new audit login and a new spid, but we do not attempt to connect to the server again from our code. The same code works like a charm when conneting to Interbase. – EspenS
can you provide some example code? – Jk.

3 Answers

1
votes

In case your app is multithreaded: BDE is not threadsafe. You have to use a separate BDE session (explicitly created instance of TSession) for each thread; the global Session created automatically for the main thread is not sufficient. Also, all database access components (TDatabase, TQuery, etc.) can only be used in the context of the thread where their corresponding instance of TSession has been created.

1
votes

Verify in the ODBC installation if SQL Server driver is configured to do connection pooling. Appear that Native Client installation activates it for default... (At least, mine installation had connection pooling active and I don't activated it).

0
votes

This probably comes too late for the asker, but maybe it helps others.

Everytime there is a cursor that doesn't get closed, the BDE/ODBC combo will establish a new connection for successive querys. The "spid change" is probably the result of a non-closed cursor.

To solve this problem you have to find the BDE-component that caused this stil-opened cursor. Then you call a method that will eventually close the cursor (TTable.Close, TTable.Last ...).

After that the "spid change" should be gone and therefore the deadlock.

Some tips to find that component:

  • During the lock, execute the following statement (for example using Management Studio): EXEC sp_who2.
  • Look in column BlkBy. The blocked connection has a number in it.
  • This number is the spid (Server Process ID) of the blocking connection.
  • Then you execute DBCC INPUTBUFFER(spid).
  • In column EventInfo you will find the sql-statement that has been issued by your programm.
  • With that information you should be able to find the BDE-component that causes your trouble.