Por: Quinn Wildman
Resumen: MSSQL and Sybase servers generate locks
What can I do to help avoid server side locking (including DEADLOCK)
problems when working with Microsoft SQL Server (DBLIB) and Sybase
SQL Server (DBLIB)?
The most important thing to realize when dealing with this issue is the BDE never requests locks. All locks that are applied are performed by the server over which the BDE has little to no control.
The following suggestions may help you tune your application and
server. The server side suggestions may not apply to all server
and database installations.
From the client application you may want to take greater control
over the size of results sets (this may mean using TQueries),
minimize the length of transactions (usually not an issue if the
SQLPASSTHRU MODE is set to ...AUTOCOMMIT), and only open dbaware
controls when necessary to help minimize resource drain on the
server and, possibly in this case, deadlock potential.
MSSQL 7 has a new feature of row level locking which will automatically occur with some queries. You can also configure a MSSQL database as to when to select row locks over page locks. If you have MSSQL 6.5, updating to MSSQL 7 may resolve page lock issues you have because MSSQL 7 may generate row locks instead.
The means by which the BDE selects data from each of the supported
servers does not make assumptions about how each server chooses
to ensure data integrity.
This may result in significant additional statements being sent to the server, but will result in no page locks because the cursor will be closed each time the table is scrolled.
BDE/SQL Links will not only detect and raise a deadlock
error but it will "reset" its database transaction state when
it detects an MSSQL error 1205. It is not necessary to rollback
the explicit transaction (Database1.rollback) after the
deadlock has been detected.
The error 1205 signals to the client that the server has
"resolved" a deadlock and chosen one of the users to end the
deadlock. This user's transaction is automatically rolled back.
Please refer to the MS SQL Server documentation for more
information on deadlock detection and server error 1205.
MS SQL and Sybase Server topics: (the following is by no means a
comprehensive list. Please check your Sybase and MS SQL Server
docs for tips on optimizing your server and databases)
Please also see:
MS SQL Server documentation (printed or Books Online)
If using TQueries:
For more information on the options above:
Analyzing locks Topic
(also see Database Developer's Companion Errata)
Try Delphi XE4 free for 30 days
New Instant Trial!
Webinars on demand!
More social media choices:
Delphi on Google+
@RADTools on Twitter
Respuesta del Servidor desde: ETNASC02