Have Geek Brain

Oracle Deadlock Detection Tool

I'm looking for a static analyzer for Oracle queries and PL / SQL procedures (triggers, constraints, ...) - a tool that will walk through our DB schema and point out possible deadlocks. Just like FindBugs for Java.

If no such tool exists, would you like to use it?

0


a source to share


4 answers


Starting from 10g, the database now has a built-in static PL / SQL analyzer:

ALTER SESSION SET PLSQL_WARNINGS = 'ENABLE:ALL';


You have google for PLSQL_WARNINGS and you will find useful links

I agree with Matthew, although you can hardly find an analyzer that can effectively detect deadlocks ... there are too many variables in the game.

+2


a source


TOAD has some static analysis tools (or at least some code quality). I doubt they can find dead castles.



+1


a source


Deadlocks will depend on transactions, not static code. Oracle has no concept of "BEGIN TRANSACTION", so the static analyzer does not know what the starting point of a transaction is. Hypothetically, the parser could be written so that if given the original SQL or PL / SQL statement, it could track all potential execution paths and determine which tables were updated / delete / insert / merge in which order. You can then "compare" two (or more) results of this and determine if there are any where tables are processed in a different order (eg TAB_A, then TAB_B in one and TAB_B, then TAB_A in the other). I suspect this will cause a lot of false positives.

In Oracle, selection is not blocked (except for SELECT ... FOR UPDATE). Thus, deadlocks only occur when updating data, and only when two concurrent transactions try to update the same rows.

+1


a source


There is an infinite potential for deadlocks in any database. Therefore, the tool will need usage statistics to know which deadlocks will statistically occur more than once a year. Such a tool would be too complex to justify the development work.

The most common practice would be to set up the database in a Q&A or production environment. And then monitor for any deadlocks that actually occur. You can run automated unit tests against the Q&A environment to simulate load.

-2


a source






More articles:


All Articles