Handling Deadlocks When Using InterBase Tables in Delphi and C++ Builder

Abstract: Handling Deadlocks When Using InterBase Tables in Delphi and C++ Builder

Problem: When using a Delphi and C++ Builder client applications that are performing SELECT statements encounter a deadlock when another client application locks a record by using an UPDATE, DELETE, or INSERT statement. Solution: The BDE defaults to a mode that automatically assigns a transaction level of Read Commited and commits the transaction by doing a soft commit. In InterBase the transaction level is set up as NO WAIT, READ COMMITTED, NO RECORD VERSIONS, because record versioning is not used. The deadlock occurs when a SELECT tries to read a record that is not yet committed. In InterBase a soft commit is done by doing a commit retaining (i.e. isc_commit_retaining()). In the IDAPI.CFG file the DRIVER FLAG parameter has five settings that are described below: The BDE Driver Flags Parameters: Driver Flag: Isolation BDE/Application Problem: Level/Commits: NULL Honor eXIL parameter in dbiBeginTran()/ Honor eEND parameter in dbiEndTran() 0 Read Committed/ Deadlock (performance hit would Hard Commit (i.e Commit) occur on large tables if deadlock did not occur). 512 Repeatable Read/ Performance hit on large tables as Hard commit BDE will fetch cursor to its previous location. 4096 Read Commited/ Deadlock Soft Commit (i.e. Commit Retaining) 512 + 4096 Repeatable Read/ = 4608 Soft Commit See (1) below. (1) Changes in other transactions are not seen, the soft commit keeps the transactions "view" of the data set going. Closing and re-opening the data set (i.e Query1.Close and Query1. Open) will not show changes made by other transactions. Refreshing the view will then require disconnecting and reconnecting to the database (i.e Database1.Close and Database1.Open), this may also require changing the Database1. KeepConnected property to False (default is True). Another approach is doing a literal Database1. StartTransaction immediately followed by a Database1.Commit. By starting a transaction the BDE will close out any transaction it has automatically going for the Database component (i.e. the SHARED_AUTOCOMMIT setting). The two recommended modes are the 512, and the 4608 modes depending on which is the most desireable behavior to handle in the application. In the inline version of the BDE v4.0 the transaction level will automatically default to NO WAIT, READ COMMITTED, RECORD VERIONS, and for the driver flag modes that use Read Committed record versions will also be used. The inline version of BDE 4.0 start shiping with the inline version of Delphi 3.0.