MariaDB/MySQL cooperative advisary locking
Posted: Sat Mar 18, 2017 10:53 am
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
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 )