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;
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;
ASKER
What us SP?
Strored Procedure
i.e. if it is stored on the sql server.
i.e. if it is stored on the sql server.
ASKER
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...
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...
ASKER
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 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.
ASKER
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.
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.
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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