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

投稿者:: Borland Staff

概要: 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.

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.

次からのサーバー応答:: ETNASC03