How to use Firebird WITH LOCK with Delphi Firedac

Question

I’m trying to Block a table on my system in order to prevent other users from entering the system when the procedure is running, how can I do this using the "WITH LOCK" with the "Firedac"?

• This eating blocks the table for recording or also blocks to visualization?
• Case lock to visualization and recording, how to use correctly?
• How to identify via Delphi that the table or record is locked?

Answer 1

I found in that Firebird documentation, the correct way to use this command. The documentation talks about some precautions when using this command. From what I read in the documentation, it returns a compromised line if it uses the WITH LOCK.

I read on some unofficial Firebird forums that the lines are automatically "released" when performing a commit or rollback in the transaction. That is, it does not lock the tables, but rather the lines that were in the SQL of WITH LOCK executed.

Unfortunately I don’t have much experience in Firebird, so in a way, it’s very complicated to say all the impacts of using this command in your application. Maybe, as an outline solution and less impact on your system and database, use a flag within the database (in a table of parameters, for example) would make the process more streamlined.

For example: if that column is like TRUE, the system would return a message claiming that it is not ready for connection as it is going through an update process.

I hope I’ve helped.

Answer 2

After Tanto Tentar I solved the question. In order to function in the FireDac will have to disable the Autocommit ex: FDConnection1.TxOptions.AutoCommit := False;.

• P - This eating blocks the table for recording or also blocks to visualization?

• R - The Command WITH LOCK blocks lines that will be brought in an instruction SQL as @Lucas de Souza Cruz said. ex: SELECT * FROM TABELA WHERE FIELD1=VALUE WITH LOCK and unlocks when a command COMMIT or ROLLBACK the command WITH LOCK does not lock for viewing.

• P - If lock for viewing and recording, how to use correctly?

• R - The Command WITH LOCK locks only for Recording as stated above and does not lock for viewing.

• P - How to identify via Delphi that the table or record is locked?

• R - To test just try to insert or edit the table if you present a message like this:

Execution caused by system error that does not preclude Successful Execution of subsequent statements. Lock Conflict on no Wait transaction.

The table row is blocked. You can make exceptions treatment to avoid message in English... It goes from the imagination of each.