PostgreSQL Lock and transactions

[FCS]

Hello,

I need more information about locks and transactions.
I migrate my projects to PostgreSQL.
For example I have to tables: TM=master and TD=detail.
Here is a pseudo-code for modify data:

Code: Select all

  SQL_Query.Execute; //Opens data set as Select * from TM
  <here I chose a record to modify> 
  UQ_To_X_rec(SQL_Query, X_rec); <and store it into record variable>
  try
      try
        SQL_Query.Lock;
        SQL_Query_D.Execute;  //Opens data set as Select * from TD 
                              //                  where TD.Link=TM.Link
        <detail data are stored in dynamic array>  
        <here on the form I change the Master and/or Detail data 
            stored in the record variable and the array for details>  

        if OK_1 then begin // if user click OK button on the form to accept changes
          SQL_Query.Edit;
          X_rec_To_UQ(SQL_Query, X_rec);  <make changes in locked record in TM>
          
          <here there are updates for details in TD table (SQL_Query_D) = OK_2>
          if OK_2                  // if update of details went OK
          then SQL_Query.Post      // for commit changes
          else SQL_Query.Cancel;   // for rollback changes
        end;

      finally
        SQL_Query.UnLock;
      end;
    except
      SQL_Query.Cancel;
      MsgBox('Error .....',mfOK);
    end;

My questions are:
1. Does SQL_Query.Lock make a global transaction for all changes till the Unlock command?
2. Is the update of details made under transaction ?
3. Will the SQL_Query.Cancel rollback all changes in TM and TD ?
4. If point 2=false, how to implement transaction for all changes and lock a record in TM?
5. In my old project I do these steps (Betrieve/Pervasive API):

Code: Select all

   if <lock record in Master table to prevent it for edition by other users>
   then begin
     <read the record to record variable>  
     <Read detail data into an array>
     <Modify data in memory> 
     if <user accepts changes> 
     then begin
        <Begin Transaction>
        if <update master record> and <update detail data>
        then <End Transaction = Commit all changes>
        else <Abort Transaction = cancel all changes>  
     end;
   end 
   else begin
     MsgBox('Record is Locked by other user');
   end; 

How implement them using UniDac?

Regards
Michal

[DemetrionQ]

    1) The Lock functionality works when TUniQuery.LockMode is set to lmOptimistic or lmPessimistic. At this, calling TUniQuery.Lock starts a transaction, creates a savepoint and lock the current record. When calling TUniQuery.UnLock, roll back to the savepoint is performed and the record is unlocked (the transaction is still active). An explicit call of TUniConnection.Commit or TUniConnection.Rollback will close the transaction and unlock the record, if it is not unlocked yet.
    2) The transaction is started for connection, therefore you can make changes in both master and detail in the context of the transaction.
    3) TUniQuery.Cancel method cancels not posted changes for the current record. If TUniQuery.Post was already called for the editing record, TUniQuery.Cancel won't affect in any way.

A sample of work with TUniQuery.Lock :

Code: Select all

  MasterQuery.LockMode := lmOptimistic;
  MasterQuery.Lock; // start a transaction and lock the current master record

  // do some changes in master and detail

  if ... then
    UniConnection.Commit  //apply all changes and unlock the master record
  else
    UniConnection.Rollback; // roll back all changes and unlock the master record

The detailed information about the mentioned properties and methods can be found in the UniDAC documentation.

[FCS]

Hello,

Thanks for your answer. It helps me a lot.

1. What will happen, when will I use this construction:

Code: Select all

    SQL_Query.Connection.StartTransaction;
    try
      SQL_Query.Lock;
      try
        < changes > 
      finally
        SQL_Query.UnLock;
        SQL_Query.Connection.Commit;
      end;
    except
      SQL_Query.Connection.Rollback;
      MsgBox('Error ....');
    end;

2. Which is the default value of the LockMode ?
3. There is only one point in the documentation, where the lmOptimistic is written. Where can I find description of LockMode and its values? Do you have more detailed documentation?

Regards
Michal

[DemetrionQ]

Hello.

1) I have added descriptions into your code:

Code: Select all

SQL_Query.Connection.StartTransaction; // Start transaction
    try
      SQL_Query.Lock; // Lock record and create savepoint LOCK_SQL_Query
      try
        < changes > 
      finally
        SQL_Query.UnLock; // Roll back to save point LOCK_SQL_Query
        SQL_Query.Connection.Commit; // Commit transaction. But since it was rolled back to the save point LOCK_SQL_Query on the previous line, nothing will be changed in the data base. 
      end;
    except
      SQL_Query.Connection.Rollback;
      MsgBox('Error ....');
    end;

2)
Default value of the TUniQuery.LockMode property is lmNone,
default value of the TUniTable.LockMode property is lmOptimistic.

3) LockMode property defines what kind of lock will be performed when editing a record (by Edit/Post methods). LockMode property can have one of the following values: lmNone, lmOptimistic, lmPessimistic.

    lmNone - No locking is performed. This should only be used in single user applications.

    lmOptimistic - Locking is performed when user posts an edited record. After this the lock is released. Locking is performed by the RefreshRecord method.

    lmPessimistic - Locking is performed when the user starts editing a record. The lock remains until the user posts or cancels the changes.

[FCS]

Hello,

Thanks a lot.

Code: Select all

  SQL_Query.Connection.StartTransaction; // Start transaction
  try
    SQL_Query.Lock; // Lock record and create savepoint LOCK_SQL_Query 

1. In this case the "SQL_Query.Lock" will not create the transaction because it is done inside the transaction. In my tests the "Lock" use the lmPessimistic lock. I can't edit this record from other computer and it is good behaviour for me.

2. As I understand, the RollBack or Commit commands automatically unlocks locked record ?

3. Can I perform Lock command for multiple records? Will the Commit or RollBack unlock these records ? In this case Is the better first start the transaction and then lock records ?

Regards
Michal

[DemetrionQ]

Hello.

2. Yes, the RollBack or Commit commands automatically unlock a locked record.
3. You can lock several records. At this, it's not necessary to start a transaction explicitly, since TUniQuery.Lock will itself start a transaction if it is not started yet. All records should be unlocked by closing the transaction (Commit or RollBack).

If you need to lock all the table, you can set an appropriate SQL query in the TUniQuery.SQLLock property, that will execute when calling the TUniQuery.Lock method. For example:

Code: Select all

  UniQuery1.Options.StrictUpdate := False;
  UniQuery1.SQLLock := 'LOCK TABLE YourTable IN ACCESS EXCLUSIVE MODE;';
  UniQuery1.Lock;

[FCS]

Hello,

Thanks a lot for your explanations.

Finally I use this code to make an update for a record;

Code: Select all

  Result:=false; 
  SQL_Query.Connection.StartTransaction;
  try
    SQL_Query.Lock;
    UQ_to_KS_rec(SQL_Query, X_rec);
    Detail_Read(X_rec.Id); //reads details from several tables by master's Id

    F_ES_E_KS:=TF_ES_E_KS.Create(self);
    OK_1:= F_ES_E_KS.Edit(X_rec); //here user makes changes and accepts them
    F_ES_E_KS.RELEASE;

    if OK_1 then begin
      try
        SQL_Query.Edit;
        KS_rec_to_UQ(SQL_Query, X_rec);  
        SQL_Query.Post;  //it is necessary to check constrains i.e. unique indexes
        if  Detail_Write(X_rec) then begin //store details
          SQL_Query.Connection.Commit;
          SQL_Query.Refresh; 
          Result:=true;
        end else begin
          SQL_Query.Cancel;
          SQL_Query.Connection.Rollback;
        end;
      except
        SQL_Query.Cancel;
        SQL_Query.Connection.Rollback;
        MsgBox('Error during update !!!',mfOK);
      end;
    end else begin
      SQL_Query.Cancel;
      SQL_Query.Connection.Rollback;
    end;
  except
    SQL_Query.Connection.Rollback;
    MsgBox('Record is locked by another user');
  end;

Similar code I use to add a new record and it's details, but without lock.
I start transaction before Lock, because (as you wrote earlier) the Post method unlock the locked record. In my case the record must be locked to the end of transaction, because I have to add/update detail data. The Post method checks constrains too.

Possible that some of the lines are redundant in this example, but of my tests indicate that the above code is working properly.

If there are errors in the code, please let me know.

Regards
Michal

[Daos]

Hello.

If I understand the Devart answer correctly, then locked (by Lock method) in a transaction record can be unlocked by the UnLock method or Commit/RollBack of the transaction only.

I have read your code, it is good. А recommendation:
the Cancel method is needed, if you have called the Edit method (the dataset switch to edit mode), and have not called the Post method yet. Then the Cancel method will cancel the change of the record, that has been made in the edit mode. If you already have called the Post method, the Cancel method has no effect.
So, in your code, it is enough to call SQL_Query.Cancel in the except block only:

Code: Select all

      except
        SQL_Query.Cancel;
        SQL_Query.Connection.Rollback;
        MsgBox('Error during update !!!',mfOK);
      end;

Because only in this block it may be needed to cancel not posted change of the record (and to cancel the edit mode of dataset).

[FCS]

Thank you.

Regards
Michal

[DemetrionQ]

Hello.

If any other questions come up, please contact us.