Oracle deadlock without explicit locking and read committed isolation level, why?

I get this error Message: ORA-00060: deadlock detected while waiting for reso开发者_JAVA百科urce even though I am not using any explicit table locking and my isolation level is set to READ COMMITTED.

I use multiple threads over the Spring TransactionTemplate with default propagation. In my business logic the data is separated so that two transaction will never have the same set of data. Therefor I don't need SERIALIZABLE

Why can Oracle detect a deadlock? Deadlocks are impossible in this constellation, or am I missing something? If I'm not missing anything then my separation algorithm must be wrong, right? Or could there be some other explaination?

---

Oracle by default does row level locking. You mention using multiple threads. I suspect one thread is locking one row then attempting to lock another which has been locked by another thread. That other thread is then attempting to lock the row the first thread locked. At this point, Oracle will automatically detect a deadlock and break it. The two rows mentioned above could be in the same table or in different tables.

A careful review of what each thread is doing is the starting point. It may be necessary to decide to not run things in parallel, or it may be necessary to use an explicit locking mechanism (select for update for example).

LMK of what you find and of any additional questions….

K

---

Encountering deadlocks has nothing to do per se with the serialization level. When a row is inserted/updated/deleted oracle locks the row. If you have two transactions running concurrently and trying to change the same row, you can encounter a deadlock. The emphasis in on "CAN". This generally happens if different type of transactions take locks in a different order, which is a sign of bad transaction design.

As was previously mentioned a trace file is generated on encountering a deadlock. If you look at the trace file, you can determine which two sessions are involved in the deadlock. In addition it also shows the respective SQL statements.