BDE、Delphi、ODBC、SQL Native Client 和死锁
我们有一些 Delphi 代码，它使用 BDE 通过 SQL Server Native Client ODBC 驱动程序（2005 版本）访问 SQL Server 2008。我们的问题是，我们在向多个表进行插入的循环中遇到了一些死锁问题。
整个循环是在 [TDatabase].StartTransaction 中完成的。查看 SQL Server Profiler，我们清楚地看到，在循环过程中的某个时刻，SPID（会话 ID？）发生了变化，然后我们自然会陷入死锁。 （两个SPID都对同一个表进行插入）
似乎BDE在某些时候会与数据库进行第二次连接...
（虽然我很想跳过BDE，但目前不可能。）
任何人都有经验可以分享吗？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
在 ODBC 安装中验证 SQL Server 驱动程序是否配置为执行连接池。
似乎本机客户端安装默认激活它...（至少，我的安装激活了连接池，但我没有激活它）。
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).
对于提问者来说，这可能来得太晚了，但也许对其他人有帮助。
每当有一个游标未关闭时，BDE/ODBC 组合将为后续查询建立一个新连接。 “spid 更改”可能是未关闭游标的结果。
要解决此问题，您必须找到导致此仍然打开的游标的 BDE 组件。然后，您调用一个最终关闭光标的方法（
TTable.Close、TTable.Last...）。之后，“spid Change”应该消失，因此僵局也消失了。
查找该组件的一些提示：
执行 sp_who2。BlkBy列。被阻止的连接中有一个号码。spid（服务器进程ID）。DBCC INPUTBUFFER(spid)。EventInfo列中，您将找到您的程序发出的 sql 语句。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:
EXEC sp_who2.BlkBy. The blocked connection has a number in it.spid(Server Process ID) of the blocking connection.DBCC INPUTBUFFER(spid).EventInfoyou will find the sql-statement that has been issued by your programm.如果您的应用程序是多线程的：BDE 不是线程安全的。您必须为每个线程使用单独的 BDE 会话（显式创建的
TSession实例）；为主线程自动创建的全局Session是不够的。此外，所有数据库访问组件（TDatabase、TQuery等）只能在其对应的TSession已创建。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 globalSessioncreated 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 ofTSessionhas been created.