MariaDB/MySQL cooperative advisary locking

General Help regarding HMG, Compilation, Linking, Samples

Moderator: Rathinagiri

Post Reply
t57042
Posts: 148
Joined: Tue Mar 16, 2010 9:46 am
Location: belgium

MariaDB/MySQL cooperative advisary locking

Post by t57042 »

Dear all,

THis is the description of an alternative way to control concurrency.
Cooperative advisary locking can be used to implement application locks or to simulate record locks. Names are locked on a server-wide basis. If a name has been locked by one client, any request by another client for a lock with the same name is blocked. This allows clients that agree on given lock names to use these names to perform cooperative advisory (record) locking.

2 Functions (build in MariaDB/MySQL) are used:

GET_LOCK(str,timeout)

Tries to obtain a lock with a name given by the string str, using a timeout of timeout seconds ( can be 0 = no waiting). Returns 1 if the lock was obtained successfully, 0 if the attempt timed out (for example, because another client has previously locked the name)

RELEASE_LOCK(str)

Releases the lock named by the string str that was obtained with GET_LOCK(str,timeout)

The following program is an adapted version of MySQL revisited:
viewtopic.php?f=5&t=5107

Function a_lock() locks the record if not locked.
If locked ,it displays a message and offers the choice to Retry/Abort.

IMHO this is a more elegant way to control concurrency then the ’SELECT ... FOR UPDATE’ method.

Please test and comment highly appreciated.

Richard Peeters

Code: Select all

//demo - R. Peeters 
// MySQL/MariaDB Cooperative Advisary Locking
#include "hmg.ch"
#include "hmgmysql.prg"
*------------------------------------------------------------------------------*
Function Main
*------------------------------------------------------------------------------*
  public tabl :="country"
  Public dbo	:= Nil
  public oQuery
  
 	Define Window win1 ;
		At 0,0 ;
		Width 620 ;
		Height 520 ;
		Title 'Query window ';
		MAIN ;
		ON INIT Connect() ;
		ON RELEASE Disconnect()
		
		ON KEY RETURN ACTION Display()
		ON KEY INSERT ACTION AppendRow()
    ON KEY DELETE ACTION DeleteRow() 
    		       				   
		Define Grid Grid_1
			Row 0
			Col 10
			Width 600
			Height 430
			Headers {'Rowid','Code','Name','Residents'}
			Widths {100,100,240,150}
			 BACKCOLOR {208,240,255}
			JUSTIFY { BROWSE_JTFY_RIGHT,BROWSE_JTFY_LEFT, BROWSE_JTFY_LEFT, BROWSE_JTFY_RIGHT} 
		end Grid 
		
		define button refresh
		  row 440
		  col 70
		  Width 70
			Height 30
			caption "refresh"
			onclick  DoQuery()
		end button
		
		define button display
		  row 440
		  col 170
		  Width 70
			Height 30
			caption "show"
			onclick  Display()
		end button
		
		define button edit
		  row 440
		  col 270
		  Width 70
			Height 30
			caption "edit"
			onclick  EditRow()
		end button
		
		define button add
		  row 440
		  col 370
		  Width 70
			Height 30
			caption "add row"
			onclick  AppendRow()
		end button
		
		define button delete
		  row 440
		  col 470
		  Width 70
			Height 30
			caption "delete"
		  onclick  DeleteRow()
		end button
		
	End Window
	
	win1.Center 
	win1.Activate 
Return  	
*------------------------------------------------------------------------------*
Procedure Connect
*------------------------------------------------------------------------------*
	// Connect
if netname()="MEDION"   
  dbo=connect2db("localhost","root","","namebook")
  createtable()
 // miscsql(dbo,"SET GLOBAL innodb_lock_wait_timeout = 1")     
else
  dbo=connect2db("medion","laptop","","namebook")
endif    
DoQuery()     
Return
*------------------------------------------------------------------------------*
Procedure Disconnect()
*------------------------------------------------------------------------------*
dbo:Destroy()
Return 
*------------------------------------------------------------------------------*
Function DoQuery()
*------------------------------------------------------------------------------*
//Local oQuery
Local i 
wow= "Select * From " + tabl
oQuery := sql(dbo,wow) 
	win1.Grid_1.DeleteAllItems() 
	For i := 1 To len(oQuery)
    win1.Grid_1.AddItem({sql2c(oQuery[i,1]), sql2c(oQuery[i,2]),sql2c(oQuery[i,3]),sql2c(oQuery[i,4])})   
  Next 
Return
*------------------------------------------------------------------------------*
Procedure DeleteRow()
*------------------------------------------------------------------------------*
//Local oQuery
Local aGridRow
Local i
Local cROWID 
	i := win1.Grid_1.Value 
	if i == 0 
		Return
	EndIf

	if MsgYesNo("Are You Sure?")
		aGridRow	:= win1.Grid_1.Item (i)
		cROWID		:= aGridRow [1]
    
     trylock= a_lock('namebook.country.' + cROWID,1) // locks row
		 if trylock > 0   // if row is locked = 0
   	     oQuery := miscsql(dbo, "DELETE FROM "+ tabl + " WHERE ROWID = " + c2sql(cROWID))
         a_unlock('namebook.country.' + cROWID) //unlock row  	
		     DoQuery () 
		 endif    
	EndIf  
Return 
*------------------------------------------------------------------------------*
Procedure EditRow()
*------------------------------------------------------------------------------*
//Local oQuery
Local oRow
Local i
Local aresults := {} 
	i := win1.Grid_1.Value 
	if i == 0 
		Return
	EndIf

aGridRow	:= win1.Grid_1.Item (i)
cROWID		:= aGridRow[1] 	

trylock= a_lock('namebook.country.' + cROWID,1) // locks row
if trylock > 0  // record is free
    oQuery:= sql(dbo, "Select * From " + tabl + " where ROWID = " + c2sql(cROWID))
		  ccode	:= sql2c(oQuery[1,2]) 
		  cname	:= sql2c(oQuery[1,3])    
	   	cresidents	:= sql2c(oQuery[1,4])                  
                       
	 	aResults := InpWindow	('Edit Row : '+cROWID,{'Code:', 'Name:' , 'residents:' },{ccode,cName ,cresidents },{32,32 ,32 },0,0,.T.)
			
		If aResults [1] != Nil 
     			cCode	:= c2sql(aResults [1]) 	
			cName		:= c2sql(aResults [2])
			cresidents:= c2sql(aResults [3])
		  oQuery:= miscsql(dbo, "UPDATE "+ tabl + " SET Code =" + ccode + ",Name = "+ cName +",residents = "+ cresidents +" WHERE ROWID = " + c2sql(cRowid))
       a_unlock('namebook.country.' + cROWID) //unlock row  
    else
       a_unlock('namebook.country.' + cROWID) //unlock row  							
		EndIf
    DoQuery ()
endif     	 
Return
*------------------------------------------------------------------------------*
Procedure AppendRow()
*------------------------------------------------------------------------------*
//Local oQuery
Local i
Local aResults := {} 	
  	aResults := InpWindow	('New Row',{'code:' ,'Name:' , 'residents:' },{'' ,'','' },{10,32,32 },0,0,.T.)
	
  	If aResults [1] != Nil
     		 ccode		:= c2sql(aResults [1])
		 cname		:= c2sql(aResults [2])
		cresidents:= c2sql(aResults [3]) 
	     oQuery	:= miscsql(dbo,"INSERT INTO "+ tabl + " VALUES (" +'0,' + ccode +',' + cname+',' + cresidents + ')')        
		   DoQuery ()
		EndIf
return 

*------------------------------------------------------------------------------*
Procedure display()
*------------------------------------------------------------------------------*
//Local oQuery
Local oRow
Local i
Local aresults := {}  
	i := win1.Grid_1.Value 
	if i == 0 
		Return
	EndIf

	aGridRow	:= win1.Grid_1.Item (i)
	cROWID		:= aGridRow[1]  	
	oQuery:= sql(dbo, "Select * From " + tabl + " where ROWID = " + c2sql(cROWID))
  
		For i := 1 To len(oQuery)
		  ccode	:= sql2c(oQuery[i,2]) 
		  cname	:= sql2c(oQuery[i,3])    
	   	cresidents	:= sql2c(oQuery[i,4])                  
    next                     
	 	aResults := InpWindow	('Row : '+cROWID,{'Code:', 'Name:' , 'residents:' },{ccode,cName ,cresidents },{32,32 ,32 },0,0,.F.)			
		DoQuery ()
     	 
Return
*------------------------------------------------------------------------------*
PROCEDURE CreateTable()
    oQuery := miscsql(dbo, "DROP TABLE country")
    oQuery := miscsql(dbo,  "CREATE TABLE country (ROWID int(4) primary key auto_increment ,CODE char(5) unique, NAME char(30), RESIDENTS int(11))")
    oQuery := miscsql(dbo,  "INSERT INTO country values (0,'LIT', 'Lithuania', 3369600), (0,'USA', 'United States of America', 305397000), (0,'POR', 'Portugal', 10617600), (0,'POL', 'Poland', 38115967), (0,'AUS', 'Australia', 21446187), (0,'FRA', 'France', 64473140), (0,'RUS', 'Russia', 141900000)")
    oQuery := miscsql(dbo,  "INSERT INTO country values  (0,'BEL', 'Belgium', 11000000)")
    
    r=sql1(dbo,'SELECT version()')
    win1.title:=r + "  " + "TABLE: " + tabl     
RETURN
*------------------------------------------------------------------------------*
function SQL2C(Value)
local cValue := ""
cValue :=alltrim(hb_valtostr(value))
return cValue
*------------------------------------------------------------------------------*
function sql1(dbo1,qstr)
oQuery :=  dbo1:query(qstr)
if oQuery:neterr()
   msgstop(oQuery:error())
   oQuery:destroy()  
   return
endif    
oRow:=oQuery:getrow(1)
ores:=oRow:fieldget(1)
oQuery:destroy()    
return ores 
*------------------------------------------------------------------------------
function a_lock(name,time)
*------------------------------------------------------------------------------
// try to lock named record for time - then retry or cancel
// returns 0 if fail - 1 if lock OK
local res
local quer
do while .T.
      quer= "select get_lock( " + c2sql(name) + "," + alltrim(str(time)) +")" 
      res=sql1(dbo,quer)  
      if res=1  //OK
        return 1
      endif
      if MsgRetryCancel ( "RECORD LOCKED" , "Attention!" )=.F.    //CANCEL
          return 0
      endif         
enddo
*------------------------------------------------------------------------------
function a_unlock(name)
*------------------------------------------------------------------------------
local quer
quer= "select release_lock( " + c2sql(name) +")" 
res=sql1(dbo,quer)
return
*------------------------------------------------------------------------------
Function InpWindow ( Title , aLabels , aValues , aFormats , row , col,yn_edit )
Local i , l , ControlRow , e := 0 ,LN , CN ,r , c , wHeight , diff , BF 

 bf := set(2)
 set(2,.f.)

l := HMG_LEN ( aLabels )

Private aResult [l]

For i := 1 to l

    if ValType ( aValues[i] ) == 'C'

        if ValType ( aFormats[i] ) == 'N'

            If aFormats[i] > 50
                e++
            Endif

        EndIf

    EndIf

    if ValType ( aValues[i] ) == 'M'  
        e++
    EndIf

Next i
    r := 0
    c := 0  

DEFINE WINDOW _InputWindow ;
    AT r,c ;
    WIDTH 380 ;
    HEIGHT (l*30) + 90 + (e*60) ;
    TITLE Title ; 
    MODAL ;
    BACKCOLOR {208,240,255};
    NOSIZE
    ON KEY RETURN ACTION _InputWindowOk()
    ControlRow :=  10

    For i := 1 to l

        LN := 'Label_' + ALLTRIM(STR(i,2,0))
        CN := 'Control_' + ALLTRIM(STR(i,2,0))

        @ ControlRow , 10 LABEL &LN OF _InputWindow VALUE aLabels [i] WIDTH 90

        do case
        case ValType ( aValues [i] ) == 'L'

            @ ControlRow , 120 CHECKBOX &CN OF _InputWindow CAPTION '' VALUE aValues[i]
            ControlRow := ControlRow + 30

        case ValType ( aValues [i] ) == 'D'

            @ ControlRow , 120 DATEPICKER &CN  OF _InputWindow VALUE aValues[i] WIDTH 140 
            ControlRow := ControlRow + 30

        case ValType ( aValues [i] ) == 'N'

            If ValType ( aFormats [i] ) == 'A'

                @ ControlRow , 120 COMBOBOX &CN  OF _InputWindow ITEMS aFormats[i] VALUE aValues[i] WIDTH 140  FONT 'Arial' SIZE 10
                ControlRow := ControlRow + 30

            ElseIf  ValType ( aFormats [i] ) == 'C'

                If HB_UAT ( '.' , aFormats [i] ) > 0
                    @ ControlRow , 120 TEXTBOX &CN  OF _InputWindow VALUE aValues[i] WIDTH 240 FONT 'Arial' SIZE 10 NUMERIC INPUTMASK aFormats [i] 
                Else
                    @ ControlRow , 120 TEXTBOX &CN  OF _InputWindow VALUE aValues[i] WIDTH 240 FONT 'Arial' SIZE 10 MAXLENGTH HMG_LEN(aFormats [i]) NUMERIC   
                EndIf

                ControlRow := ControlRow + 30
            Endif

        case ValType ( aValues [i] ) == 'C'

            If ValType ( aFormats [i] ) == 'N'
                If  aFormats [i] <= 50
                   if yn_edit=.F.
                    @ ControlRow , 120 TEXTBOX &CN  OF _InputWindow VALUE aValues[i] WIDTH 240 FONT 'Arial' SIZE 10 MAXLENGTH aFormats [i] READONLY
                   else 
                    @ ControlRow , 120 TEXTBOX &CN  OF _InputWindow VALUE aValues[i] WIDTH 240 FONT 'Arial' SIZE 10 MAXLENGTH aFormats [i] 
                   endif 
                    ControlRow := ControlRow + 30
                Else
                    @ ControlRow , 120 EDITBOX &CN  OF _InputWindow WIDTH 140 HEIGHT 90 VALUE aValues[i] FONT 'Arial' SIZE 10 MAXLENGTH aFormats[i]
                    ControlRow := ControlRow + 94
                EndIf
            EndIf

        case ValType ( aValues [i] ) == 'M'

            @ ControlRow , 120 EDITBOX &CN  OF _InputWindow WIDTH 140 HEIGHT 90 VALUE aValues[i] FONT 'Arial' SIZE 10 
            ControlRow := ControlRow + 94

        endcase

    Next i
  if yn_edit=.T.
    @ ControlRow + 10 , 80 BUTTON BUTTON_1 ;
    OF _InputWindow ;
    CAPTION 'OK' ;
    ACTION _InputWindowOk()

    @ ControlRow + 10 , 190 BUTTON BUTTON_2 ;
    OF _InputWindow ;
    CAPTION 'Cancel' ;
    ACTION _InputWindowCancel()
  else
    @ ControlRow + 10 , 135 BUTTON BUTTON_3 ;
    OF _InputWindow ;
    CAPTION 'OK' ;
    ACTION _InputWindowCancel() 
  endif    
    _SetFocus ('Control_1','_InputWindow')

END WINDOW
CENTER WINDOW _InputWindow
ACTIVATE WINDOW _InputWindow
set(2,bf)
Return ( aResult )

User avatar
Rathinagiri
Posts: 5471
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Contact:

Re: MariaDB/MySQL cooperative advisary locking

Post by Rathinagiri »

Wonderful information and implementation. Thanks a lot for sharing.
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.
User avatar
serge_girard
Posts: 3161
Joined: Sun Nov 25, 2012 2:44 pm
DBs Used: 1 MySQL - MariaDB
2 DBF
Location: Belgium
Contact:

Re: MariaDB/MySQL cooperative advisary locking

Post by serge_girard »

Bedankt Richard, helaas echter weinig tijd om het te installeren en uit te proberen. Misschien volgende week even tijd!

Serge
There's nothing you can do that can't be done...
Post Reply