Search code examples
javajdbcsybasereindextable-locking

Sybase/JDBC: how to detect reorgs or exclusive locks?

We use Sybase ASE (15.5) server as our DB and are having strange, intermittent SPID blocking issues that I am trying to detect and mitigate programmatically at the application-layer.

Sybase allows you to schedule so-called "reorgs" which from what I can tell are periodic re-indexes/table compactions, cleanups, etc. Scheduled DB maintenance, basically.

Every once in a while, we get all the planets coming into alignment with each other, where:

  1. A query is executed (creating a SPID in Sybase) and hangs for some reason. This places a (blocking) shared lock on, say, the widgets table; then
  2. The scheduled reorg kicks off, and wants to cleanup the widgets table. The reorg places an exclusive lock request on widgets, but can't obtain the lock because widgets is already locked and blocked by the hanging SPID/query; then
  3. Subsequent queries are executed, each requesting shared locks on widgets; such that
  4. The whole system is now tied up: the reorg can't start until it obtains an exclusive lock on widgets, but widgets is tied up in a blocking shared lock by a hung SPID. And because the reorg has placed an exclusive lock on widgets, all other queries wanting shared locks on widgets have to wait until the reorg is complete (because a newly requested exclusive lock trumps a newly requested shared lock).

I think my ideal strategy here would be to:

  • Timeout DB queries after say, 2 minutes, which will prevent SPIDs from hanging and thus preventing the reorgs from running; and then
  • If a query attempts to hit a table that has an exclusive lock on it, detect this and hadle it specially (like schedule the query to run again 1hr later, when hopefully the reorg is complete, etc.)

My questions:

  1. How do I timeout a query to release a shared lock after, say, 2mins?
  2. Is there a way to programmatically (most likely through the Sybase JDBC driver, but perhaps via Sybase command-line, HTTP calls, etc.) determine if a reorg is running? Or, that an exclusive lock exists on a table? That way I could detect the exclusive lock and handle it in a special way.

Thanks in advance!

Solution

  • You can get the commands that are running in the database with the following query:

    select cmd from sysprocesses

    To find locking information you can join master..syslocks and your_db..sysobjects to find out what locks exist on the object you are trying to access. syslocks.type indicates the kind of lock that is in place, and those possible values can be found here:

    select object_name(id), db_name(dbid), type from master..syslocks
where dbid = db_id("your_db")

    http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc36274.1550/html/tables/X16427.htm

    Hopefully that helps.

    To find if the lock is tied to a reorg, I think you should be able to join syslocks.spid to sysprocesses.spid where cmd = "REORG" or something like:

    select p.cmd, p.spid, l.type from master..sysprocesses p, master..syslocks l  where CMD = "REORG"