Site Logo

IzzySoft


Das inoffizielle Android-HandbuchDas inoffizielle Android-Handbuch
Für 16,99 € bei Amazon kaufen
Das inoffizielle Android-SystemhandbuchDas inoffizielle Android-Systemhandbuch
Für 6,99 € bei Amazon kaufen
Die besten Android-Apps: Android-Systemtools. Fotografie & Freizeit. Büro-Tools, Schule und StudiumDie besten Android-Apps: Android-Systemtools. Fotografie & Freizeit. Büro-Tools, Schule und Studium
Für 2,94 € bei Amazon kaufen
Stand: 2024-03-28 04:07
Preis & Verfügbarkeit können sich geändert haben.

Locks, Deadlocks and the like

Deadlock handling

OK, Oracle deals with them and resolves them itself – but they are nasty stuff. And I do not talk about the ORA-00060 in the alert log file, but rather about performance, handling, and the "rejected" transaction. Are deadlocks avoidable? I'd say: At least partially. If Deadlocks are an issue for you, you should investigate your StatsPack / AWR reports:

TM locks without affected rows

These mostly point to Foreign Key constraints without indexes. To solve/prevent these locks, check the affected table for Foreign Keys. If those are not indexed, create indexes for the affected columns.

What's behind it? Easy: Having no index, the locks required for updates need to be managed at block level; so of the same database block contains multiple rows, they are all locked. An index brings a smoother way for locking here.

TX locks with affected rows

Here usually the application is responsible ("application row level conflict"). The task in this case is to ensure rows are always locked in a particular order. If two threads run at the same time, and one locks rows from 1 to n while the other locks from n to one … Well, it's quite obvious they meet somewhere in the middle and create a deadlock.

Further readings

Metalink note 62365.1

TM Locks: How to identify the locked object?

Force DDL to repect Locks

Before Oracle 11g, DDL always respected locks – failing with an ORA-00054. This is no longer the case from Oracle 11g onwards, where most DDL operations can be completed parallel. To indicate DDL statements should respect locks as they did before, one can:

ALTER SESSION SET ddl_lock_timeout=30;

The value is specified in seconds and indicates how long DDL statements will continually retry to get a lock and execute. If they did not succeed until then, they should fail as before.

It is also possible to make this the DB default:

ALTER SYSTEM SET ddl_lock_timeout=30;
2020-11-18