![]() ![]() The application is running on weblogic and sql server 2005, and I've reproduced the deadlocks on websphere and sql server 2008. I've tried (somewhat blindly) playing about with the indexes, but seemingly to no avail. (PID, ACTIVITYID, ITEMID, TRANID) form the primary key. We have 3 non-clustered indexes on the table (PID, ACTIVITYID, ITEMID, TRANID), (ITEMID), and (PID, ACTIVITYID). In my simplistic mind these two statements should be locking different rows, and even if they were updating the same row, one should just wait for the other.Ĭan anyone explain why they are causing deadlocks, or give any suggestions as to how to prevent them? I realise some amount of deadlocks are inevitable and the application should handle them (which it does), but I don't understand why they should be happening in this case. UPDATE WF SET QUEUETIME =, STATE =, USER =, TIME = WHERE PID = AND ACTIVITYID = AND ITEMID = AND TRANID = AND STATE = AND USER = nvarchar(4000))UPDATE WF SET QUEUETIME =, STATE =, USER =, TIME = WHERE PID = AND ACTIVITYID = AND ITEMID = AND TRANID = AND STATE = AND USER = WHERE PID = 'standardOutbound' AND ACTIVITYID = 'Node1' AND ITEMID = '121' AND TRANID = 'toNode1' AND STATE = '' AND USER = '' UPDATE WF SET QUEUETIME=' 13:06:53.625', STATE = 'ready', USER = '', TIME = null WHERE PID = 'MessageProcessing' AND ACTIVITYID = 'Delete' AND ITEMID = '120' AND TRANID = 'Created' AND STATE = 'ready' AND USER = '' UPDATE WF SET QUEUETIME=' 13:06:53.578', STATE = 'outbound', USER = '', TIME = null For example the two update statements below: You'll just have to take my word on SELECT ) but it will depend on your DB and settings.I have an application which is running multiple sql statements simultaneously in different threads, causing various deadlocks which all seem to come from one table. So basically I think you have a few incorrect assumptions. Oracle & I think Postgres will never lock on SELECT (unless you have FOR UPDATE which is clearly reserving for an update anyway). SQL Server will not lock on a SELECT if you use SNAPSHOT ISOLATION. That's just crappy database engine design though. SELECT should never deadlock but on some databases it will because the locks it uses interfere with consistent reads. So that proved inserts & deletes can deadlock. The best way to create a deadlock is to do the same thing in a different order. ![]() You can somewhat ignore select depending on your database and settings but the others will give you deadlocks.Generally all modifications can cause a deadlock and selects will not (get to that later). Is there a way to fix this example for REPEATABLE_READ or SERIALIZABLE isolation? In the above example, the INSERT locking order is whereas the DELETE locking order is. DELETE FROM permissions WHERE id = 100.SELECT permission_id FROM companies WHERE id = 200 - returns permission_id = 100.INSERT INTO companies (name, permission_id) VALUES ('Nintendo', 100) - Inserts companies.id = 200.INSERT INTO permissions - Inserts permissions.id = 100.companies [id BIGINT PRIMARY KEY, name VARCHAR(30), permission_id BIGINT NOT NULL, FOREIGN KEY (permission_id) REFERENCES permissions(id)).For super bonus points: how can I avoid a deadlock in the following scenario? For bonus points: answer the same question for all other operations (e.g.Is it possible for an INSERT operation to cause a deadlock? If so, please provide a detailed scenario demonstrating how a deadlock may occur (e.g.We are talking about multiple threads accessing multiple tables simultaneously. ![]() I am using REPEATABLE_READ or SERIALIZABLE transaction isolation (locks get retained every time I access a row). ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |