In 1997, as part of the Sisbaja team, I first used Oracle Database. Now, 8 -- eight! -- years later, in addition to being the last single -- well, technically, RB won't be married until this Saturday, but still -- member standing, I suspect I'm also the only one who's still dealing with it.

BTW, just for the sake of it, allow me to introduce the five Sisbaja members:
1) Bapak Yudho -- it speaks volumes when you type your first name on Google and hit I'm Felling Lucky, the website displayed is yours
2) Ibu Yova, now Bapak Yudho's wife and a mother of three
3) You know Siwis, now a father of one (and a husband of three -- kidding!)
4) You also know RB, soon a husband (of one)
5) I'll let you guess

Anyway. Oracle. The source of wisdom. And this week I learned its latest piece of wisdom. That the root of a problem might not readily be plain to see from its log entries. Much like one's true self might not readily be plain to read from one's blog entries -- a topic for another day if I feel like it.

The problem? Deadlock. Until I this week I always thought deadlock was just a concept to make life difficult for students taking Operating Systems and Database subjects. Check that. Deadlock exists in real life and I'm none the wiser.

Say we had two tables:
1) TABLE_A: A_ID (primary key) and A_DESC
2) TABLE_B: B_ID (primary key) and B_DESC

Oracle's log read like this: "deadlock detected while waiting for resource" with the current statement being "update TABLE_A set A_ID = '005', A_DESC = '005' where A_ID = '005'"; it also gave info on the other waiting process: "update TABLE_B set B_ID = '123', B_DESC = '123' where B_ID = '123'"

Thing is, TABLE_A and TABLE_B were not related. And sure 'nuff, when we tried opening two sessions, session 1 executing the first statement and session 2 the second, no errors occurred. No deadlock, no nothing. The two tables were simply updated.

BTW, if you think it's dumb to update a table's primary key, especially when it won't change anyway, I totally agree with you. Well, legacy stuff bla bla bla...

As it so often does, Google came to our rescue. We found this. Thanx to Toad's great range of UI tools, we quickly found out that, guess what, we didn't have indexes on several child table foreign key columns.

Say we had two more tables:
3) TABLE_A_CHILD: A_ID (foreign key to TABLE_A.A_ID) and A_CHILD_DESC
4) TABLE_B_CHILD: B_ID (foreign key to TABLE_B.B_ID) and B_CHILD_DESC
Both foreign keys are not indexed.

And yes, we did have two methods in our code; one updating TABLE_B_CHILD and then updating TABLE_A with the statement above; the other updating TABLE_A_CHILD and then TABLE_B. In either method, commit and rollback was not called until the very last. If these two methods happened to be called at the same time (very likely, given the great number of users accessing the application simultaneously) deadlock would likely occur, regardless of whether they accessed the same records.

To simulate, we did these steps:
1) Open two sessions.
2) In session 1 execute this: update TABLE_B_CHILD set B_ID = '456', B_CHILD_DESC = '456 Child' where B_ID = '456'; this effectively locks TABLE_B_CHILD.
3) In session 2 execute this: update TABLE_A_CHILD set A_ID = '006', A_CHILD_DESC = '006 Child' where A_ID = '006'; this effectively locks TABLE_A_CHILD.
Note: step 2 and 3 are interchangable, step 3 can come before 2.
4) In session 1 execute: update TABLE_A set A_ID = '005', A_DESC = '005' where A_ID = '005'; this session locks TABLE_A and at the same time tries to lock TABLE_A_CHILD, only it can't as TABLE_A_CHILD has already been locked by session 2. So it waits. No deadlock yet.
5) In session 2 execute: update TABLE_B set B_ID = '123', B_DESC = '123' where B_ID = '123'; this session locks TABLE_B and at the same time tries to lock TABLE_B_CHILD. You guessed it, it can't. Because TABLE_B_CHILD has been locked by session 1.
At this point Oracle correctly detects that deadlock will occur. To prevent it, one of the currently running statements has to be cancelled. So Oracle terminates the statement in session 1, as it's the one executed first.

Err, you guys follow that? No? That's fine. It took us one whole day to get three managers to finally follow. I guess some people are not destined to be managers.

So. We managed to simulate the deadlock. Sadly, our client didn't understand our excitement. Huh, you managed to simulate what now? Whatever, dude. Just get it fixed before tomorrow morning. Next we had to come up with a fix.

Since we are so smart, we came up with two. Well, either one would solve the problem, but we recommended our client do both. One: create indexes on all child table foreign key columns. This should result in a "told you so" from our fav DBA. Two: remove the needlessly updated primary keys. This should result in a "you update primary keys? WTH, man" from, well, very much everyone. What can I say, legacy stuff bla bla bla...

Need another proof on how smart we are? We let the same managers deal with the client.

Here's something useful: a way to locate unindexed foreign keys.

Current music: Toto - Don't Chain My Heart
Current mood: happy