menu

Questions & Answers

MYSQL GET_LOCK DB2 Equivalent

I am attempting to translate a MySql statement into IBM DB2.

I query the MySql Database (using a PDO query)

"SELECT GET LOCK(lockname,timeout)"

So then I can check if the lock is active, unlock it.

if ($result === true)
{
   $this->locker->unlock($lockname);
}

Then in "unlock"

"SELECT RELEASE_LOCK(lockname);"

I can't quite understand how to work with operations as MON_GET_LOCKS.

Comments:
2023-01-11 09:00:16
Even in MySQL it is an overused function. What operation are you protecting with the locks? Is there some transactional equivalent that can be used instead?
2023-01-11 09:00:16
When migrating, it is an opportunity to simplify :)
Answers(1) :

There is an attempt to implement some similar approach with the DBMS_LOCK module.
But it seems that it has some implementation bugs in Db2 11.5.7 at least:
The DBMS_LOCK.REQUEST function invocation returns the error below, when you run exactly the same compound statement as in the example at the link above from the 2-nd session, and the same lock is not released in the 1-st session.

DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0206N  "TIMEOUT" is not valid in the context where it is used.
SQLSTATE=42703

So, if you want to implement something similar, you may do it on your own. It seems, that the algorithm is based on the table SYSTOOLS.DBMS_LOCK_DET holding these locks as rows.