Hi,

We have designed an application to pull some profit and loss reports which help business analysts in analysing company's business across various countries on different industries (like Shop industry, restaurant industry etc.)

We have provided seperate login ID to each and every user.
When user pulls report from .NET front end, it accesses DB2 version 9.1 databases.
Now the situation is, when 1 user tries inserting some data into the database it is not possible for other user to delete data from database.
I am not able to understand why it is throwing SQLCODE -913 (i.e. deadlock) in this case.
In database, we have maintained a USER_ID column to distinguish data for one user from another. So, one user's data is completely isolated from other user's data and each user has access to only their data not to other user's data.
In fact the purpose of maintaining USER_ID was to avoid deadlocks in the application.
As per our requirement:
1) We do not need to lock rows
2) It should be possible to perform simultaneous operations on data base for many users, i.e. user1 should be able to insert/update/delete/select when user2 is performing insert/delete/update/select operation.

We use DB2 version 9.1

Regards,
Upendra

Hi,

do you include the USERID in the WHERE-Clause?
Do you have an Index on the USERID?

Cheers
nvk

Hi nvk@vhv,

We have index on USERID column and USERID column is also present in the WHERE clause. On further investigation, we found that the PAGE level locks are defined on the table. Have asked DBA to change it to ROW level lock. I will check if the issue gets resolved after changing the level of lock from PAGE to ROW and keep you posted with the updates.

Thanks,
Upendra

Hi,
I don't know if you have already checked this, but what is the LOCKSIZE of the table?
For your task, changing the LOCKSIZE to ROW will probably help. keep in mind that you need to verify that the values of locklist and maxlocks are enough

Hope this helps
Alex

Since it was mentioned above that they were using Page Locks, we may presume that this is DB2 for z/OS and not DB2 LUW.