Stumblings

Since installation, the Oracle 10g XE on my desktop has mostly been used for quickly testing how database stuff works. Last week I finally got around to digging a bit deeper into the HTMLDB/Application Express part.

Lets say you have batch jobs on your system. Every morning management requires a report of the previous day’s run. Fortunately, your batch jobs log their status in a table. Now you could always run a query against this table, spool the data and email it to management. Or you could create a report using Application Express. Consider:

• No software to deploy on management desktops (need a browser though)
• You aren’t the messenger that delivers bad news about batch job failures
• A browser based report looks a lot more impressive than spooled CSV (especially when your boss is trying to impress his boss)
• Not wastefully repetetive or mind numbingly boring

So here is how you go about impressing management:

1. Login to your desktop Oracle XE instance as a user with DBA privileges
2. Create a new user – say rss
3. Create a database link to your application database(say remdb) that has the batch log table(app_batch_log)
4. Create a public synonym (app_batch_log_remdb) for this remote table (app_batch_log@remdb)
5. Grant select on app_batch_log_remdb to user rss
6. Logout and login to Oracle XE as user rss
7. Choose Application Builder >> Create Application >> Create Application from the (rather picturesque) XE menu
8. Enter your application name and click Next
9. Select Report as Page Type, SQL Query as Page Source and enter your query (SELECT * FROM app_batch_log_remdb). Click Add Page.
10. Click Next thrice
11. Change Authentication Scheme to No Authentication and click Next twice
12. Click Create
13. Click Run Application

Here is a screen capture of the application creation process (Steps 7 thru 13).

To deploy:

1. Enable HTTP access to your XE instance as described here
2. Copy the URL from the last step and replace the 127.0.0.1 with your desktop IP address/hostname
3. Send off to those interested.

Mark Rittman's post on Oracle pet peeves linked to Jeff's original list for SQL Server. Hardened by constant exposure to brilliant ideas (such as the comma separated list in a column), I was only mildly amused. I am still not sure what is scarier: the mildness of my amusement or these comments from a guy who claims to be an OCA/OCP?

DML Locks

These are the locks that Oracle places on your behalf when you execute DML. For the purposes of this post (and my sanity), let’s divide DML into 2 categories: Regular DML (the garden variety of INSERT, UPDATE, DELETE, SELECT FOR UPDATE) and Exotic DML (LOCK TABLE …)

This post describes the most common types of locks i.e. locks that are automatically placed on your behalf when you execute Regular DML.

1. Row Locks

These are the simplest part of the Oracle locking scheme to understand. Every row is either locked or it is unlocked. That’s it (no lock types to consider). When you execute regular DML, row locks are obtained on the affected rows.

A salient feature of Oracle’s locking mechanism is the the way row locks are recorded. The fact that a row is locked by a transaction is NOT stored in a centralized list of locks. Rather, the transaction id is stored as part of the row data itself. When your transaction needs to lock a row, Oracle inspects if the transaction specified on the row is still active. If yes, the row is locked by that transaction and your transaction is blocked. If not, the row is locked by your transaction and your transaction id is now stored on the row.

2. Table Locks

These are the ones that come in various modes. When a DML Table lock is in place, another session cannot execute DDL on this table, thus preventing alteration of the table structure while you still hold a table lock. DML table locks are also referred to as TM locks.

The fine Oracle manual tells you that Table locks can be taken in 5 different modes:

RS: row share
RX: row exclusive
S: share
SRX: share row exclusive
X: exclusive

However if you look closely, you will notice that you have to explicitly lock a table (using LOCK TABLE …) to get a lock in any of the last 3 modes (S, SRX, X). The other two modes (RS and RX) are the only ones automatically taken when you execute regular DML. So for now, let’s just consider these two.

The RS (row share) table lock is taken when you execute a SELECT … FOR UPDATE. This is in addition to the row locks on whatever rows are selected as a result of this statement. When this lock is in place other transactions may obtain RS, RX, S and SRX locks on this table. No X locks can be obtained though.

The RX (row exclusive) table lock is taken when you execute an INSERT, UPDATE or DELETE. Again, this table lock is in addition to the row locks on affected rows. When this lock is in place other transactions my place RS and RX locks but not S, SRX and X.

As far as regular DML is concerned there is no difference between the RS and RX locks. The difference only comes into play if you execute LOCK TABLE. This should be pretty rare anyway.

Anything else? Err, yes. In addition to the RS and RX locks on regular DML, S (share) table locks are taken on your table if all of the following conditions are satisfied:

1. Your table has FKs to other tables (i.e. it has parent tables).
2. You are updating the PK/UK columns or deleting on a Parent table of this table
3. The foreign key from your table to this Parent table in unindexed

Usually you do NOT update the PK columns of a table. You usually index your FKs. So you shouldn’t hit the issue of this kind of lock when you execute regular DML, right? Well, almost. There is a little issue with the 9i Merge statement that might unexpectedly cause share table locks to appear.

Update: Applies only to 9.2.0.4 or earlier. See this discussion for details.