FiveTech Support Forums

#include "fivewin.ch"

//----------------------------------------------------------------------------//

function Main()

   local oCn, oRs, oDlg, oFont, oBrw

   oCn   := FW_DemoDB()
   if oCn == nil
      return nil
   endif

   oRs   := oCn:RowSet( "SELECT ID,FIRST,CITY,SALARY FROM customer" )

   DEFINE FONT oFont NAME "TAHOMA" SIZE 0,-14
   DEFINE DIALOG oDlg SIZE 600,500 PIXEL TRUEPIXEL FONT oFont ;
      TITLE "MARIADB LOCKED EDIT"

   @ 60,20 XBROWSE oBrw SIZE -20,-20 PIXEL OF oDlg ;
      DATASOURCE oRs AUTOCOLS ;
      CELL LINES NOBORDER FASTEDIT

   WITH OBJECT oBrw
      :nEditTypes    := EDIT_GET
      :CreateFromCode()
   END

   @ 10,20 BTNBMP PROMPT "LockedEdit" SIZE 150,40 PIXEL OF oDlg FLAT ;
      ACTION LockedEdit( oBrw )

   ACTIVATE DIALOG oDlg CENTERED
   RELEASE FONT oFont

return nil

//----------------------------------------------------------------------------//

static function LockedEdit( oBrw )

   local oRsMain, oRsEdit, oRec, nWait, oCn

   oRsMain  := oBrw:oDbf
   oCn      := oRsMain:oCn
   nWait    := oCn:QueryResult( "SHOW SESSION VARIABLES LIKE 'innodb_lock_wait_timeout'" )[ 2 ]
   oCn:Execute( "SET SESSION innodb_lock_wait_timeout = 1" )
   oCn:BeginTransaction()

   MsgRun( "Reading Record", "WAIT", { || ;
      oRsEdit := oCn:RowSet( "SELECT * FROM customer WHERE ID = ? FOR UPDATE", { oRsMain:ID } ) ;
      } )

   if oCn:nError == 0
      oCn:Execute( "SET SESSION innodb_lock_wait_timeout = " + cValToChar( nWait ) )
      oRec  := TDataRow():New( oRsEdit )
      oRec:Edit()
      oCn:CommitTransaction()
      oRsMain:ReSync()
      oBrw:RefreshCurrent()
   else
      oCn:RollBack()
      oCn:Execute( "SET SESSION innodb_lock_wait_timeout = " + cValToChar( nWait ) )
      ? "Record is locked by other user"
   endif
   oBrw:SetFocus()

return nil

//----------------------------------------------------------------------------//

* FWMariaRowSet METHOD EditBaseRecord( cFieldList, lNew, bEdit, oBrw, lLock )
If lLock is set to .t. or a codeblock is specified, the record is locked while
reading and on failure to read, the codeblock is evaluated with the rowset as
parameter.
Till FWH1909, read is attempted with clause "LOCK IN SHARE MODE". This approach
was not giving the expected results as other users also are able to read the
record for lock and attempt to write was resulting in deadlock issues.
This is now modified to read with clause "FOR UPDATE". This change is working
as expected.
viewtopic.php?f=3&t=37928

    ::oRs_View:EditBaseRecord( nil, .f., { |oRec| ::GO_DETAYS(lMoveState, oRec ) }, ::oBrw, {|oRs|Locked_Problem(oRs)} )

Application
===========
   Path and name: C:\PrgW_SQL\Icra\IcraW.exe (32 bits)
   Size: ********* bytes
   Compiler version: Harbour 3.2.0dev (r1904111533)
   FiveWin  version: FWH 20.02
   C compiler version: Microsoft Visual C++ 19.20.27508 (32-bit)
   Windows version: 6.2, Build 9200 

   Time from start: 0 hours 0 mins 6 secs 
   Error occurred at: 01/04/2020, 12:27:17
   Error description: Error BASE/1081  Argument error: +
   Args:
     [   1] = C   SET @@TRANSACTION_ISOLATION = '
     [   2] = U   

Stack Calls
===========
   Called from:  => CHARACTER:__OPPLUS( 0 )
   Called from: .\source\internal\FWMARIA.PRG => FWMARIACONNECTION:INTRANSACTION( 6478 )
   Called from: .\source\internal\FWMARIA.PRG => (b)FWMARIACONNECTION( 5644 )
   Called from: .\source\internal\FWMARIA.PRG => FWMARIACONNECTION:BEGINTRANSACTION( 0 )
   Called from: .\source\internal\FWMARIA.PRG => FWMARIAROWSET:EDITBASERECORD( 4888 )
   Called from: .\TOb_Browse1_SQL.prg => TYK_LIST_SQL:GO_DETAY( 838 )