Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 7 of 7 total
Thread When does a transaction try to apply a lock??
Wed, Mar 29 2023 12:35 AMPermanent Link

Ian Branch

Avatar

Hi Team,
I have this basic code..
{code}
 //
 SetLength(aTablesArray, 5);
 //
 aTablesArray[0] := 'LineItems';
 aTablesArray[1] := 'Parts';
 aTablesArray[2] := 'BackOrders';
 aTablesArray[3] := 'JobTickets';
 aTablesArray[4] := 'APJobTickets';
 //
 dmS.DBC1.StartTransaction(aTablesArray);
 //
 try
   LogMessage(' - In the Transaction');
   JobTickets.Edit;
   LineItems.Edit;
   Parts.Edit;
   //
   {$IFDEF SILogging}
   LogMessage('..Qty Needed = ' + nQtyNeeded.ToString);
   LogMessage('..Wty Parts - WtyQty = ' + Parts.FieldByName('WtyQty').AsString);
   LogMessage('..Coy Parts - CoyQty = ' + Parts.FieldByName('CoyQty').AsString);
   {$ENDIF}
   //
   Blah..
   Blah..
   Blah..
   //
   /// //////////////////////////////////////////////////////////////////////////////
   /// Finish Transaction here
   /// /////////////////////////////////////////////////////////////////////////////
   //
   dmS.DBC1.Commit;
   //
 except
   //
   on E: Exception do
   begin
     if (E is EDatabaseError) and (E is EEDBError) then
     begin
       //
       var sTable: string;
       //
       if ContainsText(EEDBError(E).ErrorMsg, 'LineItems') then sTable := 'LineItems';
       if ContainsText(EEDBError(E).ErrorMsg, 'Parts') then sTable := 'Parts';
       if ContainsText(EEDBError(E).ErrorMsg, 'BackOrders') then sTable := 'BackOrders';
       if ContainsText(EEDBError(E).ErrorMsg, 'JobTickets') then sTable := 'JobTickets';
       if ContainsText(EEDBError(E).ErrorMsg, 'APJobTickets') then sTable := 'APJobTickets';
       //
       if (EEDBError(E).ErrorCode = EDB_ERROR_LOCK) then
         TaskMessageDlg('Possible record lock in place!', 'Unable to process the part for the Job Ticket at this time.' + sLineBreak +
           'Another application may have one or more relevant records open in the ' + sTable + ' table for editing at this time.' + sLineBreak +
           'Please retry a little later.', mtError, [mbOK], 0)
       else
         ShowMessage('Unknown or unexpected database engine error # ' + IntToStr(EEDBError(E).ErrorCode) + ' - ' + EEDBError(E).ErrorMsg);
     end
     else
       ShowMessage('Unknown or unexpected error has occurred');
     //
     dmS.DBC1.RollBack;
     LineItems.Refresh;
     //
   end;
   //
 end;
 //
{code}

At what point does the transaction try to put a lock on the table??
The reason for the question is that with this in place, the User got a "ElevateDB Error #300 Cannot lock the table Parts in the schema Default for transaction access." error.
I am unsure if this was at the point of StartTransaction, or, when trying to set the Parts table to edit.
Note - The "    LogMessage(' - In the Transaction');" was not logged.

Regards,
Ian
Wed, Mar 29 2023 2:06 PMPermanent Link

Greg Hallam

Microcalm Solutions Inc

It has to be at the "edit" given you can move the pointer after starting a transaction or even make multiple edits of different rows in the same table all within the same transaction.
Wed, Mar 29 2023 4:50 PMPermanent Link

Terry Swiers

> It has to be at the "edit" given you can move the pointer after starting a transaction or even make multiple edits of different rows in the same table all within the same transaction.

START TRANSACTION locks the specified tables (or all of them if you don't specify) immediately against changes from any session other than the one that it was started on.   

You can verify this by opening TWO instances of EDB Manager against the same database.  Run the following script in the first instance:

SCRIPT
BEGIN
START TRANSACTION;
END

Without making any additional changes, edit any table in the same database from the second instance of EDB Manager.  If you are using pessimistic locking, it will error out when you first attempt to set a row to edit mode.  If using opportunistic locking, it will error out when you attempt to post the change.
Wed, Mar 29 2023 4:58 PMPermanent Link

Ian Branch

Avatar

Terry Swiers wrote:
>>START TRANSACTION locks the specified tables (or all of them if you don't specify) immediately against changes from any session other than the one that it was started on.   

Thanks Terry.  I figured as much due to where the error log reported.  Just needed to confirm.
OK.  Now to handle that situation.

Regards,
Ian
Wed, Mar 29 2023 10:59 PMPermanent Link

Ian Branch

Avatar

OK.  I have done this now..
{code}
 //
 SetLength(aTablesArray, 5);
 //
 aTablesArray[0] := 'LineItems';
 aTablesArray[1] := 'Parts';
 aTablesArray[2] := 'BackOrders';
 aTablesArray[3] := 'JobTickets';
 aTablesArray[4] := 'APJobTickets';
 //
 {$IFDEF SILogging}
 LogMessage(' - dmS.DBC1.StartTransaction(aTablesArray);');
 {$ENDIF}
 //
 try
   //
   dmS.DBC1.StartTransaction(aTablesArray);
   //
 except
   //
   on E: Exception do
   begin
     if (E is EDatabaseError) and (E is EEDBError) then
     begin
       var sTable: string;
       if ContainsText(EEDBError(E).ErrorMsg, 'LineItems') then sTable := 'LineItems';
       if ContainsText(EEDBError(E).ErrorMsg, 'Parts') then sTable := 'Parts';
       if ContainsText(EEDBError(E).ErrorMsg, 'BackOrders') then sTable := 'BackOrders';
       if ContainsText(EEDBError(E).ErrorMsg, 'JobTickets') then sTable := 'JobTickets';
       if ContainsText(EEDBError(E).ErrorMsg, 'APJobTickets') then sTable := 'APJobTickets';
       //
       if (EEDBError(E).ErrorCode = EDB_ERROR_LOCK) then
         TaskMessageDlg('Possible record lock in place!', 'Unable to process the part for the Job Ticket at this time.' +
           sLineBreak + 'Another application may have one or more relevant records open in the ' + sTable + ' table for editing at this time.' + sLineBreak +
           'Please retry a little later.' + sLineBreak + 'Parts Issue will now exit.', mtError, [mbOK], 0)
       else
         ShowMessage('Unknown or unexpected database engine error # ' + IntToStr(EEDBError(E).ErrorCode) + ' - ' + EEDBError(E).ErrorMsg);
     end
     else
       ShowMessage('Unknown or unexpected error has occurred' + #13 + EDatabaseError(E).Message);
     //
     LineItems.Refresh;
     //
     Result := False;
     //
     Exit;
     //
   end;
   //
 end;
 //
{code}
Should be OK now.

Ian
Thu, Mar 30 2023 8:54 AMPermanent Link

Fernando Dias

Team Elevate Team Elevate

Ian,

A transaction lock is acquired at StartTransaction, but there are other types of locks.
Now, my question is, for how long is the transaction active?
Do you have control on it or is it controlled by the user?
The key with EDB transactions is that they should be as fast as possible.
Your code after "Parts.Edit;" might be the source of the issues, depending on what "blah blah blah" is.

The solution you propose doesn't look like a good design to me but to me, but again, I'm not sure about what you are doing in the "blah blah blah " part of the code.

--
Fernando Dias
[Team Elevate]
Thu, Mar 30 2023 10:19 AMPermanent Link

Ian Branch

Avatar

Hi Fernando,
In the instances that have occurred, It never got that far, it always failed at the StartTransaction.

Ian
Image