Message to the second user that the record is locked

[starhu]

My software is used simultaneously by many users. The customer wants so as if a user navigates to a record (a customer sheet) then he can modify the record, but the second (and 3d etc.) users who click on the same customer would see a message (this record is locked by xy user) and they shouldn't be able to edit it.

When the first user navigates away then another user can edit it.

How can I do this?

[AndreyZ]

To inform users about locking records, you can use the LockMode property ( http://www.devart.com/mydac/docs/devart ... ckmode.htm ), for example, set it to lmPessimistic. In this case, records that are being edited will be locked on the server, and other users will not be able to start editing them.

[garik]

With lmPessimistic mode turned on for InnoDB table is there a way to detect if the current record is locked by another user and show a message to prevent editing?

[AlexP]

Hello,

When using the pessimistic lock mode in case if record is already locked, you will get an error "Lock wait timeout exceeded; try restarting transaction", you can handle it in a try...except block by its code - 1205, i.e.:

Code: Select all

  try
    MyQuery2.Edit;
  except
    on E: MyError do
      case e.ErrorCode of
        1205: ShowMessage('The record is edited by another user.');
        else ShowMessage(e.Message)
      end;
  end;

[garik]

The problem with this is that the exception will occur after 50 seconds or whatever value innodb_lock_wait_timeout is set to. Until the timeout the application seems frozen to the user and keeps showing hourglass icon. Any other ideas on how to detect locked record faster without waiting for timeout?

[AlexP]

Hello,

This is a specificity of MySQL server work. You can decrease wait timeout in your application by calling the command:

MyConnection1.ExecSQL('set innodb_lock_wait_timeout=1')

This won't affect global server settings and will be applied only for the current session.

[garik]

Thanks Alex. I guess that's the only option we have.

[AlexP]

Hello,

If you have any further questions, feel free to contact us.