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

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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).