Link to home
Start Free TrialLog in
Avatar of Rob Rietow
Rob Rietow

asked on

Trying to understand BEGIN TRANSACTION, ROLLBACK, COMMIT (My status - beginner)

When I read the documentation, it says when using INSERT, UPDATE AND DELETE. Does this also include code like the below on a client machine using APPEND?

And how do I  know if the Loop completes unsuccessfully?  I had a network error earlier to day.  Say it happened in the middle of the loop below.  How would I know?  Do I need the TRY, EXCEPT statement as below? I need some indication that the Loop either completed successfully so I can issue a COMMIT  or ROLLBACK.

And can the BEGIN TRANACTON,COMMIT,ROLLBACK include multiple tables?

Procedure UpdateTable
var
  I: integer;
begin
    BEGIN TRANSACTION
   try
    With mytable do
      begin
         Select * from Mytable where mykey is null')  // Mykey is identity
         open;
         For I := 1 to 20 do
         begin
               Append;
               fieldbyname('mynumber').asInteger := I.
               post;
         end;
      end;
      COMMIT
  EXCEPT
    ROLLBACK
end;

               

Avatar of arnold
arnold
Flag of United States of America image

you would have to build in notification of failure.

usually, your code will look for the outcome of the SP in this case to see whether it exited without an error and then have a process that it has to go through.


try is the one that captures an exception it it happens, triggering an except, and a roll back.
the SP actually runs on the server, so if you lose connection to the network, much depends on whether when the SP is triggered and you lose connection whether the SP is termed in which case it will roll back the transaction if it was still processing
Avatar of Rob Rietow
Rob Rietow

ASKER

What us SP?
Strored Procedure
i.e. if it is stored on the sql server.
I am interested only on what can be done on the CLIENT machine.
the example you posted runs against a database.

on the client side you have tests to confirm whether the interaction went through or whether it failed and then you handle the outcome.
i.e. if you try to update a set of records in a transaction, and during the process you encounter that a parameter you are adding that is supposed to be unique, meaning should not exist but it does, this triggers a fault. exception and requiring a roll back of the data addition request to what it was before you began.


The short answer, if you want to be notified when a transction failes, you have to include it in your code, i.e. before roll back, you would add functions that would have the information of what the intent was and that it failed.....

nothing happens automatically, you as the coder have to define the process...
Basic question.  Is there a Begin Transaction on the Client Side?
the begin transaction is on the SQL server the point of is to have a self contained "environment" where you make the changes you need that are only committed when there are no errors encountered in the process.


I am uncertain how you envision a transaction being handled
Usually, the client side uses locks or similar tactics to obtain an exclusive lock on a local resource, i.e. a file to avoid having multiple files being read in at the same time with data being pumped into a Database.

Consider it the filling out of a deposit slip to a bank,
you list the items you are presenting and the sum total....

the transaction is performed by the teller. who then either deposits all the items you included, or tells you that one or some had issues.

in the case of an SQL transaction as you have it defined it is all or nothing.
I have the basic concept.  Can the example above be done on the Client Side?
A scenario can be defined. you have to read in data from three files.

i.e. request a lock before accessing File A. if that is successful, request a lock for exclusive access to file B, if access can not be obtained in a short window, release lock on file A.
and loop back.
your loop can only complete the task when the process can obtain all the exclusive locks it needs for files.
Some one have a code example of what is being discussed?

Not sure what you are looking for,

Are you familiar with the game that one has to hold the "talking stick" to speak?

Consider a game charades.
The person guessing has to get the "talking stick" while at the same time the person who will be drawing, has to get the marker.
Either can hold the item for 10 seconds for their partner to get the other.
if the 10 seconds lapse, the person has to return the item back into the pool.



depending on what you need you can have as the above
open transaction

loop to do stuff
 
if there is a fault at any point, the loop is broken,
and the transaction is rolled. no errors, the transaction is committed.

the other option is you open a loop
open a transaction
do a limited set,  of data,
an error, roll back just this transaction otherwise commit.

vv
ASKER CERTIFIED SOLUTION
Avatar of Rob Rietow
Rob Rietow

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial