Dealing with 'Deadlocks'
Hint Ref: 021005110011
Hint Date: 11/05/2010
Hint Details:
*What is a 'Deadlock'?
The term 'deadlock' refers to what is known as a 'cyclical deadlock': a situation wherein two or more competing transactions are blocking each other on the same resource (usually table or row lock) and thus since neither are able to proceed, one of them must be terminated by the server.
Depending on the situation, there might be a couple different approaches to resolving deadlocks in the database. Each approach is based on the information available, individual's experience or the preference. Some administrators might be more comfortable modifying database schema; others would rather change SQL code or server/connection options.
The difficulty with deadlocks is that they rarely occur in test or QA environments, therefore they are harder to discover early in the development cycle. They typically occur under heavy production load, where it's harder to debug and not always possible to make any code changes.
*So what to do when a deadlock happens on your database. Here are 3 steps:
1. Log deadlock information
2. Determine what tables and SQL commands are involved
3. Decide what 'technique' to use to resolve it
Deadlocks could be sporadic: happen once and then never again, or repeatable: happen at a certain time of the day, during one specific report/procedure call. One needs to develop a judgment to know which ones to ignore and which ones to pay attention to. It is important to mention that the sole reason for their occurrence lies in the application and database design.
If your application occasionally behaves abnormally, e.g. most of the time everything works perfectly but sometimes, even though nothing has changed, transactions are rolled back, scripts are failing etc., or your application is simply returning the following error:
SQLCODE=-306, ODBC 3 State="40001"
then, it's time to perform some quick analysis and verify what is causing deadlocks in your database.
*Logging Deadlocks Information - Sybase Version 10,11
Deadlock logging is not turned on by default. An administrator needs to enable this either through 'Sybase Central' or dbisql. In addition to deadlock logging the user should also turn the database option 'RememberLastStatement' on in order to track down which SQL statement is being executed during deadlocks.
In order to turn required options on, from dbisql run the following:
// make sure default blocking_timeout has not been modified
SET OPTION PUBLIC.blocking_timeout = 0;
// set server option to remember LastStatement
SET OPTION PUBLIC.rememberlaststatement = 'On';
// remember information about deadlocks
SET OPTION PUBLIC.log_deadlocks='On';
Once the required options are set the server will temporarily keep all of the information in memory until a restart or it is manually cleared.
To manually turn on Deadlock detection and 'RememberLastStatement', connect to the database in Sybase Central, and right-click on the database name (with the grey cylinder icon). Select 'Properties'. The Deadlock settings are at the bottom of the 'Settings' tab.
*Analyzing deadlock information
To view deadlock information, SQL Anywhere provides the 'sa_report_deadlocks()' system stored procedure.
A sample output from executing the following SQL in dbisql :
SELECT * FROM sa_report_deadlocks();
Is
snapshotId,snapshotAt,waiter,who,what,object_id,record_id,owner,is_victim,rollback_operation_count
1,'2010-04-15 10:48:21.171',4,'DBA','call proc2()',3420,47906818,3,false,1
1,'2010-04-15 10:48:21.171',3,'DBA','call proc1()',3420,47906819,4,true,1
The 'waiter' is the connection number of the user waiting where 'is_victim' = 1. The 'object_id' is the ID of the blocked table as returned by the following query:
select table_name from systable where object_id=<object_id>;
As per the deadlock definition there needs to be at least 2 different transactions involved for a deadlock to happen. The likelihood of a deadlock to appear increases with the duration of a transaction and the number of tables (locks) involved. In other words, in order to eliminate them it is important to keep transactions short, and the number of tables /locks accessed during the transaction small.
Very often, long running transactions are caused by poorly written queries or bad indexes. In many cases, speeding them up would not only fix the performance bottleneck but also resolve deadlocks.
The main idea to resolve deadlocks is to change the locking behaviour of one of the transactions involved, so it still produces the same results, but behaves different as far as locking is concerned.
Changing locking behaviour could be done in a couple of different ways for example:
1). modifying existing SQL code (e.g. changing isolation level or reordering statements)
2). modifying existing databases schema (modifying, adding, removing indexes)
Depending on the environment different solutions could be applied. In situations where SQL code is embedded within a compiled application, the only option might be to change the schema (e.g. adding a new index to speedup the transaction). On other systems, where changing SQL is not a problem, a simple rewrite of a stored procedure may be a way to go. Either will make the deadlock less likely to appear or remove it completely.
**Space Manager Considerations
Please note that for Space Manager, the Database Connection Isolation Level should be 0 and this can be checked for an individual connection by connecting to the database with Sybase Central, click on the database name in the folders list and select the 'Connected Users' tab. Right-click on the connection to check and select 'Properties'. Select the 'Extended Information' tab and scroll down to 'isolation_level'. Please note that the list is not sorted alphabetically and so it may be necessary to scroll down further than immediately apparent.
**Space Manager Stored Procedures
With Space Manager, the most common cause of Deadlocks is when third party systems are connecting to the Space Manager database and are running poorly written functions or procedures that use the UNIQUEKEY() function to return new database record IDs. This function returns a unique ID for a new record being inserted into a table and it uses the UNIQUEKEY table, locking its single record in case more than one ID is requested at exactly the same moment in time.
The process of obtaining the new ID should only need to lock this table for the shortest moment in time, but where the process of obtaining the ID is part of a larger transaction, the whole process covered by the transaction must be completed before the lock is released.
It is therefore good practise to obtain the new IDs in advance of the main process the procedure performs and storing them in variables until required. Separate variables should be set up for the maximum number of unique IDs required as if they are not used, it is of no concern.
Therefore, set up all possible IDs at the start of the procedure, then release any lock by issuing a 'commit work;' and THEN complete the subsequent processes.
By way of an example:
A procedure might contain the following lines -
insert into phone(phoneid,phonetypeid,customerid,number,primaryphone) select uniquekey(string(trim(icnumber1),custid)),ictypeid1,custid,icnumber1,(if isnull(icprimary1,0) = 2 then 0 else icprimary1 endif)
/* now some complex SQL commands*/
insert into phone(phoneid,phonetypeid,customerid,number,primaryphone) select uniquekey(string(trim(icnumber2),custid)),ictypeid2,custid,icnumber2,(if isnull(icprimary2,0) = 2 then 0 else icprimary2 endif)
commit work;
This would lock the UniqueKey table for an unacceptable amount of time;
Change this code to the following (remembering to declare the two new variables):
begin
set newid1=uniquekey('AA');
set newid2=uniquekey('BB');
end;
commit work;
insert into phone(phoneid,phonetypeid,customerid,number,primaryphone) select newid1,ictypeid1,custid,icnumber1,(if isnull(icprimary1,0) = 2 then 0 else icprimary1 endif)
/* now some complex SQL comands*/
insert into phone(phoneid,phonetypeid,customerid,number,primaryphone) select newid2,ictypeid2,custid,icnumber2,(if isnull(icprimary2,0) = 2 then 0 else icprimary2 endif)
commit work;
*Summary
There are a number of things that need to be done in order to avoid deadlocks on the database:
a. Logging deadlock information
b. identifying SQL involved
c. Making sure queries are optimized for performance
d. Keeping transactions short
e. Revising your general performance
It is important to remember that deadlocks are just symptoms of poor database design, poor SQL coding or other problems that are hiding in the system. Addressing deadlock issues will not only make an application more stable, but it may also improve overall database performance.
(Please Note: This Procedure can be destructive and should only be used by Advanced Users. RADical Systems (UK) Limited or its Partners cannot be held responsible, in anyway, for any consequence of using this or any other Database Function, Procedure or SQL command. Responsibility resides solely with the user.
IT IS HIGHLY RECOMMENDED THAT A FULL AND VALID SPACE MANAGER DATABASE BACKUP IS TAKEN AND VERIFIED AS VALID BEFORE MAKING ANY CHANGES TO THE DATABASE.)