argh ... deadlock

Published 08-03-2007 11:36 AM | barakoboy

just when we were starting to feel really good about our chances of delivering a working and performant system, i get an email from the stress test guy of our client saying our transactions are causing table locks left and right.

what the .....

image

now i don't remember any process that involved locking tables or anything close to that.  no one in the team has any knowledge of that either. 

whipped out jmeter and started running some long running tests to try to pop the sucker out. the script was simple:

  1. login
  2. create a record
  3. save the record
  4. delete the record
  5. logout

after 3 minutes of continuously running the script, we were able to replicate the bugger.

%#$%, something was locking the tables.

after a lot of frantic google and hibernate forum searching, we come across this:

http://www.archaebacteria.net/?p=21

and got this from my boss:

http://www.mail-archive.com/hibernate-devel@lists.sourceforge.net/msg05495.html

in a nutshell, from lukas barton's blog:

If an index exists on the foreign key column of the child table, no DML locks, other than a lock over the rows being modified, are required.

If the index is not created, a share lock is taken out on the child table for the duration of the transaction.

woopeedoo.  created the indices, deployed update @ client, resumed testing ...

... so far so good.

Comments

# lamia said on August 2, 2007 9:37 PM:

Even your boss do research on stuff like that? That's very cool!

# barakoboy said on August 2, 2007 9:45 PM:

oh yeah!  he's a die-hard techie (and a big .Net fan i think ... what say you jokiz?)

and i think his favorite tool is awk  =)

# cruizer said on August 2, 2007 10:55 PM:

mabangis yun boss namin dyan kina barakoboy at jokiz...walang binatbat yun mga puti dito sa SG he he

# barakoboy said on August 2, 2007 11:36 PM:

@cruizer hehehehe honga!

# lamia said on August 2, 2007 11:58 PM:

Ramdam ko nga ang pwersa galing dito sa kinauupuan eh! Lolz!

# keithrull said on August 3, 2007 1:09 AM:

hehe, this reminds me of my old colleague.. he used to do math on every thing that we program.. tinkering on every bits and bytes that are passing to any communication lines and analyzing the performance of even the tiniest piece of code...

then he got fired. i think they were saying that he was trying to build a warship when all that was being asked is floatation device.

such is the life :)

# barakoboy said on August 3, 2007 2:56 AM:

@keith whoa! reminds me of knuth's famous quote  =)

# jokiz said on August 7, 2007 11:59 PM:

agree on the boss being a real techie guy. he's the one who found out about the perf bug on using varchar fields here (devpinoy.org/.../nhibernate-unicodes-and-index.aspx)

doubts on the share lock thingie on the whole child table? share meaning the lowest level of lock (shared lock?) used by selects?

# paolo said on August 14, 2007 8:20 PM:

yes it locks the whole child table.  ayos no? hehehehe

# tockcaojhl said on November 23, 2007 4:53 PM:

Hello! Good Site! Thanks you! qtxascoprus