Handling a DB2 record lock exception in PHP on IBMi

Ever run into this exception?

SQLSTATE[HY000]: General error: -913 [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0913 – Row or object TABLE in LIB type *FILE in use

Below is some example code on how you could handle a DB2 file lock in your PHP script, by catching the exception and doing want you want to do when someone has locked the record.  It could be that you email that person and tell them the need to get out of that file for your PHP script to work.  You need to make sure that your script can put what it was working on hold until someone can turn off the lock.

Alternative solution (Use a stored Procedure to find out about the file lock ahead of time.)

You could use a stored procedure to call

DSPRCDLCK   FILE(MASTER/PAYROLL)  MBR(*FIRST)  RCDNBR(1)  OUTPUT(*)

and you could get the RCDNBR from a SQL query like:

SELECT RRN(MY_TABLE)
FROM MY_TABLE WHERE ID=2

Just make sure your program doesn’t get into a race condition.  You may want to do all your sensitive changes in a SQL stored procedure that’s able to lock the file make changes and then release the locks.  You’ll also get the ability to rollback changes or not commit changes in case of a failure.

It appears that db2 php extension IBM_DB2.so (db2_* functions) will actually wait for the record lock to go away.  This will most likely make the PHP script hit its max execution time (causing a timeout) before the record lock is removed.  Therefore you have to check for the record lock ahead of time, create a record lock then do some update then unlock the record.

If you want to dive deeper into this you should check out commitment control, lock table, commit, rollback atomic etc…in the IBMi DB2 SQL reference pdf .

LOCK TABLE MY_TABLE IN EXCLUSIVE MODE  -- Will try to obtain a lock on the table
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s