Deadlocks in Oracle with logically disjoint transactions usually involve unindexed foreign keys:
There are two issues associated with unindexed foreign keys. The first is the fact that a table lock will result if you update the parent records primary key (very very unusual) or if you delete the parent record and the child's foreign key is not indexed.
Locks in Oracle are managed at the row level. Concurrent disjoint transactions should not interfere with one another. Unindexed foreign keys are an exception, since it can result in a complete TABLE LOCK.
You should get the SQL in the trace file of the deadlock and that should help you
narrow down which table / foreign key is responsible for the lock. Once you know which table is affected by the deadlock, make sure that all foreign key references to this table are properly indexed. E.G in your example T2.t1_id
should be indexed if it points to T1.t1_id
.
Alternatively you could use Tom Kyte's script from the above link to determine if you have any unindexed foreign key.
If you're locked on inserts, it usually means that you're trying to insert rows that have the same values for a set of columns that are UNIQUE, for example:
CREATE TABLE T1 (col1 NUMBER NOT NULL, col2 NUMBER);
ALTER TABLE T1 ADD CONSTRAINT T1_UNIQUE UNIQUE (col1, col2);
session1> INSERT INTO T1 VALUES (1, NULL);
session2> INSERT INTO T1 VALUES (2, NULL);
session2> INSERT INTO T1 VALUES (1, NULL); -- <= this will wait on session1
session1> INSERT INTO T1 VALUES (2, NULL); -- <= Deadlock !
Best Answer
There is no such thing as a read only privilege. If someone grants update,delete,insert on x to public, everyone who can create a session can modify the contents of that table.
Also, if you create a user x and using a dba account create a table x.y, the user x - who owns the table y - can modify the contents of his/her table, because he/she is the owner of that table.