lLock par. problem in EditBaseRecord() for MariaDB - SOLVED

[Horizon]

Hi Mr. Rao,

Previous link is http://forums.fivetechsupport.com/viewtopic.php?f=3&t=37700

Can you please test it?

Thanks,

[nageswaragunupudi]

We are working on this.
Kindly wait.

[nageswaragunupudi]

Using the lLock parameter in the method EditBaseRecord() is not working satisfactorily.

We advise a different approach as in this following samples program. Please test and give your opinion.

Code: Select all  Expand view  RUN

#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

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

[Horizon]

Thank you mr. Rao,

It seems it is working.

[nageswaragunupudi]

Thanks for the feedback.

Now we have modified the method EditBaseRecord() in the above lines. In our tests, it is working as expected.
The modification is being released in FWH1910.
The last parameter lLock can be a codeblock also. When read fails, this codeblock is executed with oRs as parameter.

[Horizon]

Thanks for the feedback.

Now we have modified the method EditBaseRecord() in the above lines. In our tests, it is working as expected.
The modification is being released in FWH1910.
The last parameter lLock can be a codeblock also. When read fails, this codeblock is executed with oRs as parameter.

Thank you master.

[Horizon]

Thanks for the feedback.

Now we have modified the method EditBaseRecord() in the above lines. In our tests, it is working as expected.
The modification is being released in FWH1910.
The last parameter lLock can be a codeblock also. When read fails, this codeblock is executed with oRs as parameter.

Hi Mr. Rao,

The last parameter lLock can be a codeblock also. When read fails, this codeblock is executed with oRs as parameter.

Have you changed this?

October, November, December 2019

Code: Select all  Expand view  RUN

* 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

Code: Select all  Expand view  RUN

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

if we set to .T. or codeblock lLock parameter, it gives an error.

Code: Select all  Expand view  RUN

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 )

[Horizon]

[karinha]

https://dev.mysql.com/doc/refman/8.0/en/set-transaction.html

https://docs.microsoft.com/pt-br/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-ver15

https://mariadb.com/kb/pt-br/isolation/

https://mariadb.com/kb/en/set-transaction/

https://stackoverflow.com/questions/47575445/set-tx-isolation-versus-set-transaction-isolation-level

[Horizon]

Hi,

Is it a MariaDB issue? Should i remove it and install again?

Thanks.

[nageswaragunupudi]

Can you please try with .T. as the last parameter instead of codeblock?
Can you also let us know if you are using MySql or MariaDB and the version?

[Horizon]

Hi Mr. Rao,

Can you please try with .T. as the last parameter instead of codeblock?

Yes, I have tried. same error gives.

Can you also let us know if you are using MySql or MariaDB and the version?

MariaDB 10.4.

Thanks.

[Horizon]

I am Kindly wait.

[Horizon]

up

[nageswaragunupudi]

I have tested maria15.prg with 5th parameter as .T. on 4 different servers:

MySql
5.5.18
5.6.39
8.0.17

and MariaDB
5.5.5-10.3.7-MariaDB.

I did not notice any problem with any of these servers.
Please suggest any changes you suggest in maria15.prg to reproduce the error.