New post
Question
· Dec 21, 2023

Determining all contending processes on exclusive lock issues

#Database Transaction Processing #ObjectScript #SQL #Ensemble

Hi,

The system I am working on processes large numbers of records. Inserts, updates and so forth. There are multiple processes that can potentially work on the same table at the same time. 

It is an almost impossible task to try and time these processes to not run at the same time, due to volumes, SLAs, etc.

Every now and then there are a few locking issues, Not many considering the volumes. It is just a bit of a nuisance as we need to reprocess these records later.

All the selects on the processes run at IsolationMode 0 and the transactions are kept as short as possible.

Is there a way to see which other process had the record locked at that time when the locking issue occurred and on which global node, including the index global the lock occurred on?

Sometimes it occurs on inserts, which hints towards an index being locked.

Thank you.
 

Product version: Ensemble 2018.1
Discussion (6)2
Log in or sign up to continue
Stefan Cronje · Dec 21, 2023

I haven't considered it.

It is not a deadlock. An update fails in a process, I log the exception, and the process moves on to the next record to process. This sometimes happens when batch runs occur between 00:00 and 05:00 AM.

When that exception occurs, I need it to programmatically find the contending process' information and the node locked, to log that information so that I can investigate it at a later time.

0 0
Julius Kavay · Dec 21, 2023

If you know which record is locked (i.e. ^My.Global(123) ) then you can identify the locking process (and therefore the user) in a simple method

Class DC.Lock Extends %RegisteredObject
{
/// For a given (global) reference
/// return the (exclusive) locking processID and username
/// 
/// ref: a global reference, for example: $name(^My.Global(1,2,3))
/// 
/// For other lock types (shared, remote)
/// use the infos obtained by info_types OWNER, MODE, FLAGS and COUNTS, see
/// https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls?KEY=RCOS_slock
/// 
ClassMethod Who(ref)
{
    if ^$LOCK(ref,"MODE")="X" {
        set pid=^$LOCK(ref,"OWNER")
        if pid {
            set job=##class(%SYS.ProcessQuery).%OpenId(pid)
            quit {"pid":(pid), "usr":($s(job:job.UserName,1:""))}
        }
        
    } else  { quit {} }
}
}

For example:

set ref=$name(^My.Global(123))
lock +@ref:1
if '$test {
    // in case, the node is locked,
    // check up, by who is the node locked
    set who=##class(DC.Lock).Who(ref)
    write who.%ToJSON() --> {"pid":"2396","usr":"kav"}
}
1 0