Record locking problems

[jacques marchal]

DOA 3.3.3 D4
Sometimes record lock is released before the Post or CheckBrowseMode of the row.
I use a table (Ralock) with two rows, first with key 'NE' used to prevent an other user to begin a process of delivery note (a delivery note must be compitely processed with his nr before another user attempts to begin an another delivery note).
The second row with key 'FA' acting the same manner to prevent two invoice to be initated at the same time. Invoice nr must be unique, without gap in the sequence and an invoice may be cancelled after the preview.
The flow of the machanism is like this:

Ralock.locate('NE',...)
Ralock.Edit //(lock is immediate)
Ask for transport cost in a separate window
Quickrep.Preview (or PreviewModal)
(after the close of quickRep)
Ask for Delivery note OK
if Yes,
Post in a table header the data of the delivery note
Marks the lines of DN as OK
Ralock.CheckBrowseMode (release the lock)
Exit
if Not,
Ralock.CheckBrowseMode
Exit

Same for invoice

At the 'ask for transport cost' the row NE of Ralock is locked.
An other user attempting to edit the row NE of Ralock receives the 'record is locked by another user' message.
When I am in DN preview from QuickReport, lock is released and an other user can begin a new NE, he can do Ralock.Edit (incidentaly with the
same nr and a violation of unique contrainst error when DN header is posted).

I guest QuickReport is the problem. Lock mechanism seems working in other cases, but sometimes lock is not released by CheckBrowseMode.

Anybody experimenting this ??
Jacques Marchal

 

[jacques marchal]

This is a very simple and reproducable exemple of a record locking mechanism failure.
Look at this and try (Marc).

unit Utestlock;

interface

uses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
Db, OracleData, StdCtrls, Oracle;

type
TForm1 = class(TForm)
ODSstock: TOracleDataSet;
ODSstockCODEART: TStringField;
ODSstockALPHAST: TStringField;
ODSstockCODESTAT: TStringField;
ODSstockFOURN: TStringField;
ODSstockLIBF: TStringField;
ODSstockLIBN: TStringField;
ODSstockCATEGORIE: TStringField;
ODSstockECHANTIL: TStringField;
ODSstockP1: TFloatField;
ODSstockP2: TFloatField;
ODSstockP3: TFloatField;
ODSstockP4: TFloatField;
ODSstockP5: TFloatField;
ODSstockCTVA: TStringField;
ODSstockPA: TFloatField;
ODSstockDEVPA: TStringField;
ODSstockPAFB: TFloatField;
ODSstockQSTOCK: TFloatField;
ODSstockQDISP: TFloatField;
ODSstockQRESERVE: TFloatField;
ODSstockQMINI: TFloatField;
ODSstockQBO: TFloatField;
ODSstockQCOMFOU: TFloatField;
ODSralock: TOracleDataSet;
ODSralockTYLOCK: TStringField;
ODSralockVAR1LOCK: TStringField;
ODSralockVAR2LOCK: TFloatField;
ODSralockVAR3LOCK: TFloatField;
ODSralockUSERLOCK: TStringField;
Button1: TButton;
OracleSession1: TOracleSession;
OracleLogon1: TOracleLogon;
procedure Button1Click(Sender: TObject);
procedure FormCreate(Sender: TObject);
private
{ D

 

[jacques marchal]

New precisions:
If you replace CheckBrowseMode by cancel or post or first or next .... same effect, the lock is released.
If you try to re-edit ralock after the CheckBrowseMode, the lock is not reestablished. You must do a new locate and a new edit to raise again the lock.
This lock mechanism is just useful in a sheme
edit-post without any manipulation on other tables between.
So, as I, using the lock mechanism to protect a portion of code doing operations on many tables is inoperant. You must reinvent the wheel. Edit a row of a table acting as a long term lock, put a variable at 'busy' by entering, and put as 'free' by ending...
And be caution on a silent abort and let the row as busy. All the users are locked.

By the way the record locking on paradox tables works fine in this scenario.

Last tests were made with D4 and the new DOA 3.4.6.1 dowloaded today.
Jacques

[This message has been edited by jacques marchal (edited 16 May 2002).]

[This message has been edited by jacques marchal (edited 16 May 2002).]

 

[Marco Kalter]

If you want to preserve locks across multiple records, you need to use CachedUpdates and manage the transaction through TOracleSession.ApplyUpdates and CancelUpdates.

By default each posted or cancelled record will implicitly be committed. CachedUpdates overrules this and gives you full transaction control (and therefore also locking control).

------------------
Marco Kalter
Allround Automations