Transaction lockup on LoggedEvent...

[JNL]

Hi Eitan,

We are currently getting regular lockups on table LoggedEvent, where
one exclusive lock (IX or X lock in SQL Server) becomes "orphaned" and
causes a log-jam of other inserts and selects, thereby preventing
users from logging in or out of the application. The orphaned sessions
are most likely caused by Citrix connections being killed or timing
out, due to poor network performance, but we still need a way of
preventing the log-jam effect.

I'm currently investigating setting a lock timeout interval for all
transactions, or maybe a simple command timeout, where would be the
best place to do this? Is there anything else you can think of that
would help?

Cheers,

Justin

[Eitan Suez]

hi justin,

  sorry for letting this msg slip through.
  that's an interesting problem.  a lock timeout sounds
  like a good idea.  i can't think of a better alternative
  at the moment.  i believe it can be set generally on
  the entire database.

  i'm trying to think through the steps of what's happening
  precisely.  the client initiates a transaction to write a record
  to the log, which causes the database to put a lock on the table
  until the write is done.  then we see a citrix terminal crash which
  causes the lock never to get released.

  here's a thought:  it may be safe to assume that the log
  table is not critical.  i.e. if a write to the log table fails once in a while, we
  can just ignore that.  in that case, perhaps the solution is
  configuring writes to that table to be non locking, through
  a different transaction isolation level perhaps.

  another idea is perhaps to lock only the row in question, and not
  the whole table (if it is the case that the whole table is getting
  locked).

  i'm guessing there are a number of ways and places to configure
  locking:  in the database, through hibernate.  so you'll need to
  review the database administration documentation, and relevant
  portions of the hibernate manual.  hibernate settings can be
  done programmatically or through the hibernate.properties file.

/ eitan

[JNL]

Thanks Eitan, I'll read up on the database options. We haven't been
able to reproduce the exact steps that cause the lockup, but that's no
reason we can't put in a fix of some sort. As you said, the log
writing isn't crucial, can be asynchronous and row-level locking, etc.

Cheers,

Justin