How to use record locking (rlock()) with MySQL tables

vivekdeodhar — Wed, 02 Dec 2009 15:25:00 GMT

When I started thinking of shifting my back-end from VFP tables to Mysql, I was
confronted with the problem of non-availability of "rlock()" with mysql tables.

In all cases, I wanted to use "rlock", because I did not want two users to start
working on same record in a table at the same time. Why?

Take an example of Sale invoice entry. I have two tables, one "sale" and another "saleitems".
First one holds common data like date, customer name etc and the other one holds
item codes,qty,rate etc. for each item in that sales invoice.

If two users edit same invoice at the same time, since there is no lock, both will be allowed
to get their own cursors and can make changes. Only at the time of saving, one of them will be
told that "underlying data has changed. What do you want to do?"

Some invoices of my customers are really big and complex, and naturally, they would want to know if someone is working on it, BEFORE another one starts editing, and NOT at the time of saving.

Actually, there are functions called "get_lock()", "release_lock" and "is_used_lock()" in mysql.

get_lock('anything',timeout) creates a lock by name of 'anything' and no other user/session can use lock name 'anything'. However the problem is that when you use release_lock(), ALL locks created by session calling release_lock() are released!

The workaround is to create a unique user ID for each thread at application startup and place a lock by using get_lock() on it.

Create a mysql table which holds locking reference (table name+ID of the row to lock) and the user's unique ID.

Whenever you want to use "rlock()" functionality, insert the locking reference and user ID into this table.

For example, if your unique ID is "2T30W763R" and you want to lock row having ID 345765 in table "invoice", you insert ("invoice345765","2T30W763R") into this table.

If some other user wants to edit this record, the code first looks up whether this "invoice345765" exists in the table. If yes, check whether the first users still holds the lock. This is necessary because, after starting work and placing a lock, first user's application might have crashed, powered out etc. If this happens, mysql table would still hold the lock row information, but actual mysql lock placed by that user at application startup terminates automatically if first user's connection handle goes out of scope.

This can be verified by checking is_used_lock(first user's unique ID). This ID is available from lock table's row. If the lock is valid, then this record is actually locked, otherwise the second user deletes this row and inserts another row by his/her unique ID.

User removes inserted row when the lock is no longer required, so that other users wishing to work on that record can acquire lock on it (by inserting a row) and start working.

The entire code is given below. Comments are placed wherever I felt necessary. You should change the "yourschemaname","oConn" and "autoincID" to whatever is your actual value.

*********MYSQL DATABASE PART ***************

A. Create a table in your schema to hold lock information.

Note: First column holds table name+rowID to lock
      Second column holds user's unique ID (see below for details about this user ID)

DROP TABLE IF EXISTS `yourschemaname`.`locklist`;
CREATE TABLE `yourschemaname`.`locklist` (
`lcknm` char(40) NOT NULL,
`unqid` char(10) NOT NULL,
PRIMARY KEY (`lcknm`),
KEY `locklist_unqid` (`unqid`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

B. Create a stored Procedure named "testlock".

DELIMITER $$

DROP PROCEDURE IF EXISTS `yourschemaname`.`testlock` $$
CREATE DEFINER=`root`@`%` PROCEDURE `testlock`(mylcknm varchar(40))
BEGIN

set @lockres=null,@otherunqid=null,@otherconn=null,@rowsinres=null;
select unqid into @otherunqid from locklist where lcknm=mylcknm;
if not @otherunqid IS NULL then
   select is_used_lock(@otherunqid) into @otherconn;
   if @otherconn IS NULL then
      update locklist set unqid=@myunqid where lcknm=mylcknm;
      select row_count() into @rowsinres;
      if not @rowsinres>0 then
         set @lockres=2;
      else
         set @lockres=1;
      end if;
      delete from locklist where unqid=@otherunqid;
   else
      set @lockres=3;
   end if;
else
   insert into locklist (lcknm,unqid) values (mylcknm,@myunqid);
   select row_count() into @rowsinres;
      if not @rowsinres>0 then
         set @lockres=4;
      else
         set @lockres=1;
      end if;
end if;
select @lockres lockresult,@otherconn othcon;

END $$

DELIMITER ;

*********MYSQL DATABASE PART COMPLETE***********

************VFP PART ******************

C. Create unique ID for your mysql session.

   Insert this code after you get mysql connection handle (oConn in my case)

   Note:
   This Code creates a unique(well, for my purpose, it is sufficiently unique) ID. It is stored
   as _screen.unqid and a mysql lock is placed against this name.

   addproperty(_screen,'unqid',sys(2015,10))
   if not sqlexec(oconn,"select get_lock(?_screen.unqid,10)","lockres")=1
      sqldisconnect(oconn)
      messagebox('Connected but could not get global lock handle.'+chr(13)+'Please log in again',16,'')
   endif
   if used('lockres')
      use in lockres
   endif
     sqlexec(oconn,"set @myunqid=?_screen.unqid") && create mysql unique ID variable so we can use it throughout in SP

   (REMEMBER that you should not use release_lock() anywhere in your application or
    this lock will also be released!)

D. Create following property in your form

   Property Name : idlocked , Value : None

E. Create a method in your form named "islocked" and paste following into it.

**tests if current record can be locked in mysql. called from forms' edtbtn
lparameter lcidlocked
local lcalias,lclockyn,lnlockresult
store alias() to lcalias
sele 0
if not sqlexec(oconn,"CALL testlock('"+lcidlocked+"')")>0 &&calls the stored procedure
retu .f.
endif

*SP will return a cursor holdng the result integer.
*get return value from cursor returned by SP and close it.

lnlockresult=val(lockresult) &&lockresult is field in returned cursor

do case
case lnlockresult=1 && success
   lclockyn=''
case lnlockresult=2
   lclockyn='Previous lock expired but could not set new Lock to this Record.'
case lnlockresult=3
   lclockyn='Record is Locked by other user under Connection ID : '+trans(othcon) &&othcon is field in returned cursor
case lnlockresult=4
   lclockyn='No other lock but could not set new Lock to this Record.'
otherwise &&null or error
   lclockyn='Undefined error returned by database.'
endcase
use

if used(lcalias)
   sele (lcalias)
endif

if empty(lclockyn) &&lock ok
   thisform.idlocked=lcidlocked
   retu .t.
else &&lock failed
   thisform.idlocked='' &&for safety
   messagebox(lclockyn,16,'Message')
   retu .f.
endif

F. Insert the following code into your edit button "click" (insert this at the beginning).

Note:
for example, if you want to lock record in mysql table "invoice" which has autoinc id 345765,
then your code will be
lclockname="invoice"+trans(345765)

local lclockname
lclockname=yourfilename+trans(your actual autoincID of row to lock)
if not thisform.islocked(lclockname)
   retu .f.
endif

G. Use the following code for removing locked row name from table "locklist" when your editing is complete.

Note:
Your lock's name is already stored into form property "idlocked" at the time of placing the lock.
You can create another method named "removelock" in your form class and place this code into it.

sqlexec(oconn,"delete ignore from locklist where lcknm=?thisform.idlocked")
thisform.idlocked=''

H. Remove mysql lock on userid when closing application

   sqlexec(oconn,"do release_lock(?_screen.unqid)")

************VFP PART COMPLETE******************

In essence, when your framework is complete, you can place locks on mysql table rows just by single command

thisform.islocked(lclockname)

End.

Vivek Deodhar
02-12-2009