Transactions and Isolation levels

At work, we have two applications which connect to the same database. For all kinds of business reasons, we need to make sure that only one of the applications accesses certain data at the same time. To do this, we use a row in a table as a semaphore.

While working on the locking mechanism, we had a closer look at the Transaction Management and the Isolation Levels we were using. There is a lot of good documentation on Transaction and Isolation, but it tends to be over complete, elaborate and therefore hard to read. I’ll try to share our insights with you in a slightly more digestable form (I hope).

How to add drivers to SQuirreLSQL.app

If you’re a SQuirreL SQL fan like me, and you use a Mac, you probably also want to know how to add a driver to the OSX version of the application. Most people just point to an external jar file, but you can also add drivers to the application bundle.  There’s not much to it, but it took me some time to discover that I actually had to edit the plist file in the bundle after adding the driver.

As you can see on the left, my SQuireLSQL.app now comes with the Oracle OCI and Thin driver built in, and because they are part of the application bundle, I can’t wreck my installation by misplacing jarfiles or reconfiguring classpaths. Nice.

If you want to drivers to your SQuireLSQL.app yourself, here’s what you need to do:

Transporting Oracle CHARs over a dblink

At work, we’ve got two Oracle databases and a Java web application. One of the tasks of the web application is to copy tables over from one database to the other. Last week we ran into a funny problem which turns out to be an Oracle bug.

I’ve posted a question about this last week on stackoverflow.com but it seems that not many people are affected by this particular problem, or at least not many people know the answer to this particular little problem. We set out to tackle this and we did.

Killing oracle sessions, the easy (JDBC) way.

Does Oracle complain about not being able to drop a table for a currently connected user, but you are sure you disconnected? Do the sessions “hang” in “inactive” state? Just log on as system, and execute the following query: 

SELECT
'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || '''; --',
       s.sid,
       s.serial#,
       s.osuser,
       s.username,
       s.program,
	status
FROM   v$session s
WHERE status = 'INACTIVE';

You will get a list of statements you need to execute (just copy-pase) to kill the inactive sessions. Don’t listen to the guys telling you to do intricate System Administrator stuff on a command prompt, just use any JDBC tool.

EDIT: Single query which also lists the kill command if the “alter system kill session” trick did not work:

SELECT
'ALTER SYSTEM KILL SESSION ''' || s.sid || ',' || s.serial# ||
       '''; -- kill -9 ' || p.spid,
       s.sid,
       s.serial#,
       p.spid,
       s.username,
       s.program,
       s.status
FROM   v$session s, v$process p
WHERE s.paddr = p.addr
  and (s.state='INACTIVE' or s.state='KILLED');

Thanks Bas en Jeroen!