Hello,

We are getting continuosly the deadlock in our client system, but in our local environment the process is getting timeout.

The difference between the two environment is , client machine is P5 Series and our local env is P6 Series machines.

If anyone had the same kind of issues,please share how to solve this issue?

The reason for getting timeout is, the statistics were not updated by runstats.These tables are not defined as volatile.So the query goes for tablescan.

I read from IBM faq that

"Index scans may acquire next key locking on the indexes which is less likely to lead to deadlocks than row or table level locking (on the root table) that would be acquired by a tablescan. So using the VOLATILE keyword can help minimize the chances of deadlock occurrences."

Note:
------
The query goes for tablescan has the primary key in the where clause.But Statistics is not updated.Cardinality in system table is 0.but the actual number of records in the table is 0.1 million.

Is this what happening in my case?


Regards,
Siva

That is probably what is happening. If you set the table to volatile, DB2 will ignore the statistics and encourage index usage.

The difference between the two systems could also be related to the value of LOCKTIMEOUT in the database configuraton setting.

You could be getting lock escalation from row to table level if the LOCKLIST memory is too small. For OLTP systems I would make the LOCKLIST 4096. The db2daig.log will show lock escalations if the message level is set to 3 (default).

If you upgraded your system from Version 7 to version 8, make sure that you reorg your table and indexes to convert them to type II. All indexes created in version are type II. Type II indexes are less likely to cause locking problems.

If you are using version 7, issue this command to reduce locking problems:
db2set RR_TO_RS=YES. This is not relevent to version 8.

Thanks for your reply.


Database is db2 v8.2.We didn't upgrade from V7 to 8.2.Installed 8.2 and then started the application.

There are no escalation in the database when this error occured.(From db2diag.log and database snapshot).

The LOCKTIMEOUT value is 120 seconds in both the databases.

If cardinality of the table is shown as 0 in the catalog, that means that DB2 thinks there are zero rows and will do table scan. If cardinality is -1, that means stats have not been run, and DB2 uses some default numbers. Either do a runstats on the table with distribution on key columns and indexes all, or alter the table to volatile.