Locking and display message

[mierlp]

Hi,

I've readed many threads on the form but can't figure out the right
way how to handle. The LockDemo shows a little bit, but not all.

I use InnoDB tables and now i want to prevent users from editing
the same record when it's in use with a ohter user.

I use now MyQuery.BeforeEdit this code :

Code: Select all

  if not dmTables.qry_action.Connection.InTransaction then begin
     dmTables.qry_action.Connection.StartTransaction;
     dmTables.qry_action.Lock(lrImmediately);
  end;

At this point i also would like to check if the record is locked and
display the use a mesage, so thought...let's try this :

Code: Select all

  if dmTables.qry_action.Connection.InTransaction then begin
     Application.MessageBox('Record in use/can't changed !', 'Record in use', MB_OK+MB_ICONASTERISK+MB_DEFBUTTON1+MB_APPLMODAL);
     dmTables.qry_action.Connection.Rollback;
     Abort;
  end;

When i start a second instance of the apps on the same
computer and would like to change the same record, it's
not possible, but my apps hangs and doesn't show
the message.

How to solve this problem are there some good
examples because i can't find them....thanks

Greetz
Peter

[Antaeus]

Try to do the following:
1) set innodb_lock_wait_timeout parameter (in th My.ini file or in the command line when starting the MySQL server). Its default value is 50 (measured in seconds). This allows you to manage the timeout after which the server error is raised.
2) change your code to handle this error. The code may look like the following one:

Code: Select all

   try 
     dmTables.qry_action.Edit;
   except
     on e: EMyError do 
       if e.ErrorCode = 1205 then begin
         Application.MessageBox('Record in use/can't changed !', 'Record in use', MB_OK+MB_ICONASTERISK+MB_DEFBUTTON1+MB_APPLMODAL);
         Abort;
       end;

The server will automatically roll the transaction back. For more information the read MySQL Reference Manual.