Using FOR UPDATE clause in the query language

The FOR UPDATE clause is intended for locking specific data (which is avaialble for reading from a transaction belonging to another connection) in advance while it is being read, to avoid deadlocks later, when it will be written. You can specify the tables whose data will be modified after reading. In this scenario another connection will be waiting for the data to be released since the moment when the data reading is started within a transaction, i.e. it will not be able to read locked data until the locking transaction is completed.
Not that the lock that prohibits modification of data being read in a transaction is set independently of the FOR UPDATE clause. This means that if some data is being read within a transaction, this data cannot be modified before the lock is released. If a query is executed outside of a transaction, it is capable of reading locked data.

Locks are set during the query execution and released when the transaction is completed. If a query is executed outside of a transaction, the FOR UPDATE clause is ignored.

If the FOR UPDATE clause is not followed by any table names, data read from all the tables involved in the query is locked. If tables are specified, only the data from these tables is locked. For locking purposes you can specify only top-level tables (i.e. not the tabular sections) that are involved in the query. Specify the table names instead of their aliases defined in the query. If you specify a virtual table, the data of all the tables that form the virtual table is locked. Specify the virtual table name without any parameters.

In this example of using the FOR UPDATE clause a query locks the balance register:

SELECT 
   Doc.Date, 
   Doc.Ref, 
   Doc.Company, 
   Doc.Counterparty, 
   Doc.MutualSettlementAgreement,
   Doc.DocumentTotal, 
   Doc.MutualSettlementAgreement.MonitorTotalDebt 
       AS MonitorTotalDebt,
   Doc.MutualSettlementAgreement.MaximumTotalDebt
       AS MaximumTotalDebt,
   MutualSettlementsByAgreement.AmountBalance AS AmountBalanceByAgreement
FROM 
   Document.RetailSale Doc

LEFT JOIN // Monitoring the debt amount for the agreement
   AccumulationRegister.CounterpartiesMutualSettlementsCompanies.Balance(, 
             MutualSettlementAgreement = &MutualSettlementAgreement) 
      AS MutualSettlementsByAgreement
   ON True

WHERE 
   Doc.Ref = &DocumentRef
FOR UPDATE AccumulationRegister.MutualSettlementsByAgreement.Balance 
                           // While the table of register balances is being read,
                           // no data can be written to this table
                           // to avoid collisions

Next page: Using the ISNULL() function

Be the first to know tips & tricks on business application development!

A confirmation e-mail has been sent to the e-mail address you provided .

Click the link in the e-mail to confirm and activate the subscription.