From time to time an Oracle based application will encounter a deadlock. This happens when two (or more) sessions are holding onto a resource and waiting for another one before it can relinquish the one(s) it holds. There is quite a lot of misunderstanding about deadlocks and what happens within Oracle to relieve the situation. Hopefully this blog entry will help to sort it all out. First of all, a brief demonstration.

The following is a pretty nice expression evaluator for Oracle’s PL/SQL language. You pass it a string containing an expression that would return a numeric value when evaluated and it will evaluate the entire expression and return the number. The passed expression must be in valid Oracle syntax or you will get a NULL instead of a number. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 CREATE FUNCTION expression (iExpression IN varchar2) RETURN number AS vResult number; BEGIN ----------------------------------------------------------- -- Expression evaluator for PL/SQL.

Tanel Poder has started what looks to be an excellent series of posts on his blog on the subject of Oracle diagnostic events (eg 10046 traces) at this location.

For many years, various big guns - and a lot of smaller ones - in the Oracle world have been advocating, nay demanding, that we [almost] always use bind variables in our SQL code. The reason is simple, it’s shareable, efficient, reduces parsing and allows the application to scale up to more and more users. Over the years I have spent fixing and tuning Oracle databases, I have noticed a trend of developers moving away from hard coding everything to using binds more and more in their code.

In a previous posting here on the subject of Lazy Developer Syndrome, I showed a small fragment of code where I SELECTed the ROWID in addition to all the other data I wanted, then UPDATEd the same row using the ROWID I had stored rather then using the Primary Key index that I used to SELECT the row in the first place. Why did I do this? Selecting the ROWID is always a good idea if you intend to UPDATE or indeed, DELETE, it afterwards.

To those who don’t know me, I’m an Oracle DBA and I also can develop as well. I detest having to work on applications which have the following construct in the code: 1 2 3 4 SELECT <stuff> FROM TABLE WHERE <something> FOR UPDATE; In most cases, the above is a sign of developer laziness. They cannot be bothered to write correct code to handle a situation where a row that the user has been working on (unlocked) has been amended by another user in the meantime.