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

Question

Hi.

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

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