can I rollback my transactions (which are set as con.setAutoCommit(false) )after few days ?

[Swetha Bhagavathula]

can anyone help me in finding out this ,"whether can I can I rollback my transactions (which are set as con.setAutoCommit(false) in my java jdbc application )after few days ?
.In more clear way," for instance insert, delete and update few records into my table using batch processing from my java application into to my database software ,can I rollback them after few days?Anyone please help.

[Martin Vashko]

You can rollback an active transaction which was not committed or rolled back before. So assuming that your application would be "alive" for several days, that it would hold the database connection open for the whole time and that the database does not limit the duration of transactions somehow, in principle this is possible. However, databases often limit the duration for which a connection can be held open or for which a transaction can be active, and if you keep a transaction active for an extended period of time (several days is a very extended period in this context), you increase the risk of deadlocks and other nasty side effects.

Apart of that, it is not practical. Data which are part of an uncommitted transactions are usually invisible (or only partially visible) to other transactions and therefore they would not be really useful. I think you're not understanding the real purpose of transactions in a database (or any other transactional system), which is to make processing atomic. A transaction groups together operations which should only happen together; if any of the operations fails, all the other operations in the transaction are undone too. Some database tutorial might explain this in better terms.

If you wish to "undo" changes which turned out to be wrong after several days, the way you've described is not going to work. You'd have to do this in a different way, but this is probably going to be a very complex process. What should happen when the data you're trying to revert back were already processed and incorporated into other data structures, reports, business decisions, etc? Somebody might be able to offer some advice for these if you specify your needs in much more detail, though.

[Swetha Bhagavathula]

Martin Vajsar wrote:You can rollback an active transaction which was not committed or rolled back before. So assuming that your application would be "alive" for several days, that it would hold the database connection open for the whole time and that the database does not limit the duration of transactions somehow, in principle this is possible. However, databases often limit the duration for which a connection can be held open or for which a transaction can be active, and if you keep a transaction active for an extended period of time (several days is a very extended period in this context), you increase the risk of deadlocks and other nasty side effects.

Apart of that, it is not practical. Data which are part of an uncommitted transactions are usually invisible (or only partially visible) to other transactions and therefore they would not be really useful. I think you're not understanding the real purpose of transactions in a database (or any other transactional system), which is to make processing atomic. A transaction groups together operations which should only happen together; if any of the operations fails, all the other operations in the transaction are undone too. Some database tutorial might explain this in better terms.

If you wish to "undo" changes which turned out to be wrong after several days, the way you've described is not going to work. You'd have to do this in a different way, but this is probably going to be a very complex process. What should happen when the data you're trying to revert back were already processed and incorporated into other data structures, reports, business decisions, etc? Somebody might be able to offer some advice for these if you specify your needs in much more detail, though.

Thank you Sir,for your valuable answer.
My transactions are done on the basis of "Do Everything or Nothing " principle.According to your statement .Am I right?
If so all my non-select queries oby the above principle and could insert /delete /update , can I rollback them?that is my question is?Please don't mis-undertstanding my query and i think i need help to know the perfect ans :

[Swetha Bhagavathula]

Martin Vajsar wrote:You can rollback an active transaction which was not committed or rolled back before. So assuming that your application would be "alive" for several days, that it would hold the database connection open for the whole time and that the database does not limit the duration of transactions somehow, in principle this is possible. However, databases often limit the duration for which a connection can be held open or for which a transaction can be active, and if you keep a transaction active for an extended period of time (several days is a very extended period in this context), you increase the risk of deadlocks and other nasty side effects.

Apart of that, it is not practical. Data which are part of an uncommitted transactions are usually invisible (or only partially visible) to other transactions and therefore they would not be really useful. I think you're not understanding the real purpose of transactions in a database (or any other transactional system), which is to make processing atomic. A transaction groups together operations which should only happen together; if any of the operations fails, all the other operations in the transaction are undone too. Some database tutorial might explain this in better terms.

If you wish to "undo" changes which turned out to be wrong after several days, the way you've described is not going to work. You'd have to do this in a different way, but this is probably going to be a very complex process. What should happen when the data you're trying to revert back were already processed and incorporated into other data structures, reports, business decisions, etc? Somebody might be able to offer some advice for these if you specify your needs in much more detail, though.

Thanku you Martin for your valuable answer.But i know i can perform my transaction on"Do Everything or Nothing "principle.If suppose all my insert/delete/update operations satify the principle and if i inserted (suppose today) into my db software ,can i rollback them after few days(i should not find them inserted into my table in my db software).

One more doubt i have.upto what time can i justify that my connection is alive.what is meant by the statement " as long as my connection is alive".can you please explain me clearly ,please dont mind I'm a beginner and i need help from people like you.thanku

[Martin Vashko]

What do you want to achieve by committing the transaction after a few days? As I have said earlier, if you don't commit the transaction, the data you've entered are not visible for other users in the database (or, if they are in some databases, it is usually an unwanted side-effect), so it would be natural not to insert the data at all until you're ready to actually commit them (and publish them to other users). Keeping transaction open for longer than absolutely necessary is not a good thing to do.

Concerning "as long as connection is alive": in many databases connections are terminated after some (usually configurable) time, because connection is an expensive resource and DB admins do not want the users to log in into a SQL client program and walk away for the rest of the week. There are other reasons why connection might fail after several days, a network glitch in the meantime is other quite common cause of terminating the connection prematurely.

Concerning "Do Everything or Nothing": yes, transactions are atomic. If you rollback a transaction you haven't committed, you'll practically undo all changes which happened as part of that transaction, as if they didn't occur in the first place.

Let's look at it from a different angle: why do you need to be able to rollback the changes after several days? Tell us the reason and we'll start again from that point on.

[Wendy L Gibbons]

And if there was lots of data you would quite likely have problems with your rollback segment/buffer.

[Swetha Bhagavathula]

Martin Vajsar wrote:What do you want to achieve by committing the transaction after a few days? As I have said earlier, if you don't commit the transaction, the data you've entered are not visible for other users in the database (or, if they are in some databases, it is usually an unwanted side-effect), so it would be natural not to insert the data at all until you're ready to actually commit them (and publish them to other users). Keeping transaction open for longer than absolutely necessary is not a good thing to do.

Concerning "as long as connection is alive": in many databases connections are terminated after some (usually configurable) time, because connection is an expensive resource and DB admins do not want the users to log in into a SQL client program and walk away for the rest of the week. There are other reasons why connection might fail after several days, a network glitch in the meantime is other quite common cause of terminating the connection prematurely.

Concerning "Do Everything or Nothing": yes, transactions are atomic. If you rollback a transaction you haven't committed, you'll practically undo all changes which happened as part of that transaction, as if they didn't occur in the first place.

Let's look at it from a different angle: why do you need to be able to rollback the changes after several days? Tell us the reason and we'll start again from that point on.

thanks all for your answers. I'm not sure whether my example is apt or not but i want quote an example where i need to rollback my transactions. Suppose i want to withdraw money from ATM machine having xyz bank account ,because of some hardware or software problems though i couldn't receive amount from machine but it shown me as if my money is debited from my account.In such cases do I need to rollback such transactions or not.I'm concerned only about the work of software developers let us put the end-user a side please.end-user should feel comfortable and when the machine shows him that his transactions are done though actually not my code should not show that show as if it is debited.Please forgive if this is not apt /suitable example.If you tell me any example that suits then i ll be more happy.

[Martin Vashko]

Ok, let's go on with the ATM example (I'm no expert on banking, so this will be on a very rough level). In my opinion, what's happening when you withdraw money using ATM is:

1) You put in the card, enter the PIN and the amount of money you want to withdraw.
2) The ATM communicates online with the bank's database. It will start a transaction in that database.
3) Your account is checked to make sure you're allowed to withdraw the requested amount. (If not, the ATM tells you off. The attempt is very probably logged in the database and the DB transaction is therefore committed so that the log of this attempt persists.)
4) The withdrawal is logged with your account (so that it appears on your account statement). Your account balance is reduced by the amount.
5) The ATM verifies it is able to give out the requested sum (eg. it has enough money and does not have a technical problem).
6) The DB transaction is committed.
7) The ATM sticks out the banknotes into the tray, so that you can take them.

As you can see, the duration of the transaction is very short. (It is certainly more complicated than this. Probably the credit/debit card database is separated from the account database and the monetary transactions get cleared with some delay. This at least is what is happening at my bank - when I withdraw money in the weekend, the withdrawal is logged in the account statement with the next business date. But I've stated earlier this is just a rough sketch.)

Now, something in the process can go wrong. It is very unusual (a large sums of money are potentially at stake), but it is possible that the ATM won't hand out the money. They might get jammed inside, or the operator who was loading the machine might have put a wrong denomination into the internal trays of the machine (I've read about this happening, and the bank is usually able to work this out retrospectively). So you have to go to the bank (good luck with that!) and dispute the withdrawal. If everything goes well, the bank will accept your reclamation. So - what happens now? The original transaction has been committed several days ago and cannot be undone. Actually, the bank will return the money to your account in as separate transaction (in both monetary and database sense of this word). The history of your account will show the balance go down after the unsuccessful withdrawal attempt and then go up when the bank returns you the money it owes you.

The transaction cannot be rolled back as if nothing happened. The money you unsuccessfully tried to withdraw were not in your account in the meantime. And had you suffered some damage as a consequence (say you wanted to use the money to pay your rent, but you couldn't, and the landlord has thrown you out because of that), you could try to demand a compensation from the bank. This is what I was talking about earlier - undoing a wrong transaction can have far reaching consequences that go beyond simple database processing.

[Wendy L Gibbons]

Yes if anything goes wrong a reverse transaction is then posted to your account, so you would have:

WITHDRAW 100
REFUND 100

transactions on your bank account.
They would never want this information to go missing.

[Swetha Bhagavathula]

Thank you all.could now get well clarification.
I think my online shopping is perfect example to rollback transactions.Is it not?
If we want to purchase X number of Items and want to cancel the purchase of Y number of items(y<=x) then that online shopping web-site should work with savepoint based transactions.So that we can rollback at particular savepoint.Am I right?
! question pinching my mind is ?suppose if i have nearly 20 savepoints how could I remember which transaction done at so and so savepoint?(suppose dividing my transactions into savepoints)(assume for easy purpose Named savepoint itself.if i want to rollback up to some x savepoint how can i remember such huge number of savepoint and rollback them.(for instance 20 savepoints in a day ).
Please excuse me if my question is not clear to you if clear hope Ill be more grateful for your valuable answers.

[Wendy L Gibbons]

I personally would commit at every step, and update the values required, when the customer wanted to change the order.
As I would want to update stock with every change.

[Martin Vashko]

No, savepoints are part of a transaction. When you commit or rollback the transaction, savepoints are gone. Savepoints are mostly useful in large batch processing where you want to be able to return to a last known good state and retry from that point onward, possibly fixing some problems in the process.

Generally, in a web application (like e-shop), you don't keep a transaction active between user requests.

Most e-shops employ shopping carts. When a user adds an item to a shopping cart, this happens as a single transaction. When he removes an item from the cart, it's another transaction. And when he goes for check-out, the total price of the cart is computed and the purchase is completed in a single transaction again, generally speaking. If the checkout process has several pages to go through, then you keep the state of the checkout somewhere; if you keep it in a database, then every step in the checkout process is a single database transaction. As short as possible, certainly not spanning several pages or user requests. You don'T want your user to walk off in the middle and leaving an unresolved transaction hanging around.

I think that you might want to read some SQL tutorial (or perhaps a Database tutorial). You' seem to be trying to use transactions for something they are not designed to do.

[Wendy L Gibbons]

Martin Vajsar wrote:No, savepoints are part of a transaction. When you commit or rollback the transaction, savepoints are gone. Savepoints are mostly useful in large batch processing where you want to be able to return to a last known good state and retry from that point onward, possibly fixing some problems in the process.

Generally, in a web application (like e-shop), you don't keep a transaction active between user requests.

Most e-shops employ shopping carts. When a user adds an item to a shopping cart, this happens as a single transaction. When he removes an item from the cart, it's another transaction. And when he goes for check-out, the total price of the cart is computed and the purchase is completed in a single transaction again, generally speaking. If the checkout process has several pages to go through, then you keep the state of the checkout somewhere; if you keep it in a database, then every step in the checkout process is a single database transaction. As short as possible, certainly not spanning several pages or user requests. You don'T want your user to walk off in the middle and leaving an unresolved transaction hanging around.

I think that you might want to read some SQL tutorial (or perhaps a Database tutorial). You' seem to be trying to use transactions for something they are not designed to do.

can i highlight this bit of martin's post any more, it is very important.

[Swetha Bhagavathula]

Martin Vajsar wrote:No, savepoints are part of a transaction. When you commit or rollback the transaction, savepoints are gone. Savepoints are mostly useful in large batch processing where you want to be able to return to a last known good state and retry from that point onward, possibly fixing some problems in the process.

Generally, in a web application (like e-shop), you don't keep a transaction active between user requests.

Most e-shops employ shopping carts. When a user adds an item to a shopping cart, this happens as a single transaction. When he removes an item from the cart, it's another transaction. And when he goes for check-out, the total price of the cart is computed and the purchase is completed in a single transaction again, generally speaking. If the checkout process has several pages to go through, then you keep the state of the checkout somewhere; if you keep it in a database, then every step in the checkout process is a single database transaction. As short as possible, certainly not spanning several pages or user requests. You don'T want your user to walk off in the middle and leaving an unresolved transaction hanging around.

I think that you might want to read some SQL tutorial (or perhaps a Database tutorial). You' seem to be trying to use transactions for something they are not designed to do.

Actually my concentration is not n how transactions happen.I want to focus on when a developer gets need to rollback transaction and when to set my Commit object false using my java jdbc application.Please.conveying again not the transaction management I require but when the rollback and commit statements are useful.I need a best scenario.Please help me .any scenario ..thanku. and sorry if my question is not clear .I would be thank full to you if you respond to this explaining me the appropriate scenario in real time .... thank you

[Jayesh A Lalwani]

One thing that might be confusing you is the word "transaction" is a bit overloaded.

There is database transaction which is generally short-lived. The database ensures that any changes to the database that occur within a database transaction are ACID in nature. After the database transaction is committed, it essentially dissapears, after making the changes durable. Rolling back a database transaction is trivial (from an user's POV) but you cannot rollback a database transaction after it is made durable

OTH, a business transaction is a whole another thing. A business transaction is long-lived. It is series of operations that occur together. Essentially in most applications business transactions are kept alive for years for auditing purposes. Business transactions are rolled back by undioing the changes that were made as part of the transaction. However, that process is not trivial. Also that means that business transactions can be rolled back even after they are made durable

The examples that you are giving here (the ATM transaction and the shopping cart) indicate to me that you are asking about how do you roll back a business trnasaction whereas the original question made it sound like you were asking about database transaction. I think the first thing you need to determine is what do you mean when you say transaction. If you are looking at rolling back transaction after it is made durable, chances are you mean business transaction.

ROlling back a business transaction is not trivial. You really have to design your schema to support that, and you will have to understand how the business works (for example, if the business transaction involves a human doing something... how do you tell the human to undo what s/he did?) No easy answer for you here.

[Martin Vashko]

Swetha Bhagavathula wrote:Actually my concentration is not n how transactions happen.I want to focus on when a developer gets need to rollback transaction and when to set my Commit object false using my java jdbc application...

Your original question was whether it is possible to rollback transaction after a few days, which is a very specific situation so we concentrated on that aspect. The above is quite a different question.

Anyway, I think that you should explicitly manage your transaction and therefore set autocommit to false always. I consider autocommit being on by default a serious design flaw of JDBC. But maybe someone would have a different opinion here.

As to when a rollback would happen: in an application, most of the time I use rollback to undo changes if anything during the transaction went wrong. So if the transaction cannot be successfully finished, I take it back completely. Reasons can be different: a database constraint was violated (meaning the input data was wrong), a space in the database has run out, a deadlock was detected and my session was kicked out (this actually triggers automatic rollback in Oracle to free the lock), the file I use to write data into the database is bad and cannot be processed, etc.

I actually cannot remember a case I would use rollback in an application for anything different. I can imagine using savepoints in a large batch processing to be able to go back and retry the processing from the last known good point (eg. prompting an operator to provide a correct input file if the previous one was found to contain errors), though I believe that you would need to have pretty robust error handling in your application to be able to use savepoints meaningfully and without operator intervention.

In some rare cases, the transaction can be retried after the rollback. This could be the case when handling Oracle's ORA-08177: can't serialize access for this transaction error, for example. (This error says your serializable isolation level transaction collided with another transaction and chances are it will go through if you retry it. Still, you would have to make sure that it makes sense in your case, as retrying it could lead to a lost update.)

(Of course, when I'm using an SQL client, I often issue rollbacks when I try to modify the database manually. But I assume your question regards only applications, not this kind of manual, SQL client-based work.)

[Swetha Bhagavathula]

Martin Vajsar wrote:

Swetha Bhagavathula wrote:


But I assume your question regards only applications, not this kind of manual, SQL client-based work.)

Yes my question specifically pointing on application not sql client based.Anyways thank you all.. .

[Martin Vashko]

Swetha Bhagavathula wrote:Yes my question specifically pointing on application not sql client based.Anyways thank you all.. .

You're right. I've forgotten about that in the flurry of the posts and clarifications. And you're welcome.

[chris webster]

Martin, Wendy and the others have already explained why leaving transactions uncommitted for a long time is a very bad idea. As a database application developer with many years' experience I have never seen a situation where it was wise or desirable to leave transactions uncommitted for such long periods.

If you don't want to commit a change, either don't change anything, or roll your changes back until you are ready to do it for real. And if you want to "shadow" the real data for several days, take a copy of the data and work on that instead, then make your changes to the real data when you're ready. Or consider a different data model e.g. where you record different states for your data so you can commit changes from one state to another but still identify the data that has/has not yet reached a given state. But this kind of thing requires intelligent management and application design, so think carefully about what you are really trying to do, and why.

If you want examples of why leaving transactions uncommitted for days is a bad idea, try these:

As mentioned above, on most databases your uncommitted transactions are visible to your DB session, but not to anybody else. This means your RDBMS is having to keep track of two copies of the data - "before" and "after" - until you decide to commit the transaction. As time goes on, the discrepancies between your view of the data and everybody else's will increase, which demands more resources from the RDBMS, and with large volumes of uncommitted data this could cause problems e.g. with undo/rollback segments, memory usage etc. On some databases, issuing a DML statement (insert/update/delete) may also cause the entire table to lock, which will prevent anybody else from making changes to any other records in the table either.

Meanwhile, if other users can't see your changes, their own processing is based on an out-of-date view of the "real" data, which can cause all kinds of problems e.g. if another user (or an automated process that you don't even know about) is calculating summary figures based on the records you've changed. And what if they also need to modify the same data you've changed but not yet committed? Depending on your database setup, they could get a locking error, be forced to wait for days until you commit your transaction, or have to work with "stale" data for days on end. All of these are Bad Things from the user's perspective and can lead to unpredictable consequences and inconsistent data.

Also, if you go off for days without committing your changes, your DBA is likely to spot that you are hogging resources or blocking other people's work, and they may kill your session and force your transactions to roll back anyway. When you get back to your desk, you will probably get a stern message from your DBA (DBAs are generally quite forthright in such matters) and from any other users who've been inconvenienced by your actions, and you will still have to repeat all the work you didn't commit.

[Swetha Bhagavathula]

chris webster wrote:
leaving transactions uncommitted for days is a bad idea,


Yes thank you all for your valuable explanation on this topic.But I have one more doubt.The above statement of yours states "for days" specific.SO can we come across any such situations where we need to uncommit for small durations?then commit again.. ??If so can you please tell me such scenario?what helps in making the transactions uncommit for small durations?I'm concentrating this topic because what i heard is Transaction management is one of the middleware services and that increases our application(project) more effective and efficient.

[chris webster]

You can think of an uncommitted database transaction like a lock on any resource: you don't want to hold the lock any longer than necessary, for all the reasons described by everybody above. And because a transaction is a change to existing data, you need to decide when that change should be made and how to manage conflicts etc.

Of course, long-running transactions on high volumes of data e.g. in data migration, are quite normal and these need to managed carefully to ensure you don't use up too many resources. There are also situations where you may need to have a two-phase commit, e.g. on distributed systems, but in my (limited) experience with 2-phase commits this is often more trouble than it's worth unless it's absolutely necessary for your system. Sometimes people think they need a complicated multi-phase commit transaction when really their requirements can be modelled by introducing a status flow instead. This is also useful on asynchronous jobs as you don't always know when your change will be performed and what else may be happening at the same time.

With respect, it sounds like right now you don't know much about database applications at all, so maybe you need to read up on the topic generally. In the meantime it's probably best if you keep things simple by following your organisation's policy for transactions, but be sure to ask people in your organisation to explain why they do things a particular way, so you can learn from a real-world example.

[Wendy L Gibbons]

Martin Vajsar wrote:

Swetha Bhagavathula wrote:Actually my concentration is not n how transactions happen.I want to focus on when a developer gets need to rollback transaction and when to set my Commit object false using my java jdbc application...

Your original question was whether it is possible to rollback transaction after a few days, which is a very specific situation so we concentrated on that aspect. The above is quite a different question.

Anyway, I think that you should explicitly manage your transaction and therefore set autocommit to false always. I consider autocommit being on by default a serious design flaw of JDBC. But maybe someone would have a different opinion here.

As to when a rollback would happen: in an application, most of the time I use rollback to undo changes if anything during the transaction went wrong. So if the transaction cannot be successfully finished, I take it back completely. Reasons can be different: a database constraint was violated (meaning the input data was wrong), a space in the database has run out, a deadlock was detected and my session was kicked out (this actually triggers automatic rollback in Oracle to free the lock), the file I use to write data into the database is bad and cannot be processed, etc.

I actually cannot remember a case I would use rollback in an application for anything different. I can imagine using savepoints in a large batch processing to be able to go back and retry the processing from the last known good point (eg. prompting an operator to provide a correct input file if the previous one was found to contain errors), though I believe that you would need to have pretty robust error handling in your application to be able to use savepoints meaningfully and without operator intervention.

In some rare cases, the transaction can be retried after the rollback. This could be the case when handling Oracle's ORA-08177: can't serialize access for this transaction error, for example. (This error says your serializable isolation level transaction collided with another transaction and chances are it will go through if you retry it. Still, you would have to make sure that it makes sense in your case, as retrying it could lead to a lost update.)

(Of course, when I'm using an SQL client, I often issue rollbacks when I try to modify the database manually. But I assume your question regards only applications, not this kind of manual, SQL client-based work.)

the maintime i rolled back was when the input file was wrong.

we would process a record, if it worked we set a savepoint, if it failed we rolled back to the last savepoint (the end of the previous record), then do 1 commit at the end of the batch.

[Swetha Bhagavathula]

Wendy Gibbons wrote:

the maintime i rolled back was when the input file was wrong.

we would process a record, if it worked we set a savepoint, if it failed we rolled back to the last savepoint (the end of the previous record), then do 1 commit at the end of the batch.

hai.thannks all for your posts.But how could you remember your lists of savepoints?I mean order you created for the record you process?though they are named savepoints(let us assume)how com you know which for for which record?nd created at what tie?Is there any way to kno such things from our java application?using jdbc?what i heard in in SQL we cant rememeber the order of savepoints(named even)...

[Martin Vashko]

The Connection.setSavepoint() method returns you a Savepoint instance, which you undoubtedly can "remember" in Java. You can put it in a list or store it into a variable. Naturally, that also applies to the order of savepoints - you've got full control over their creation in JDBC and therefore know the order in which they were created. Of course, to use the savepoint later, you might need to know additional information, and it is up to you to store such information together with the savepoint to be able to look up the correct one.

I honestly cannot think of a simple example where several savepoints would be useful, so I cannot make any suggestions as to how this "context" of a savepoint should look like. Generally, you'd want to tie the savepoint to the phase of processing which was just finished, to be able to return to that point later. In some particular situation, this could be a concrete record which was processed in that phase, but in general one phase could contain anywhere from zero to millions of modified records, so pairing a savepoint with a single concrete record would probably be quite unusual.

I still think that your confusion arises mainly from the fact you're not very acquainted with the basics of database processing. I've suggested reading some tutorial earlier. If you'll be using an Oracle database, I can wholeheartedly suggest reading the Oracle Database Concepts Guide, which is an excellent introduction into Oracle database. It leans towards Oracle heavily, of course, but I think it could came useful even if you'll be using a different database, especially if you ever plan to write database agnostic code.

[Swetha Bhagavathula]

Thank you Martin

[Wendy L Gibbons]

Swetha Bhagavathula wrote:

Wendy Gibbons wrote:

the maintime i rolled back was when the input file was wrong.

we would process a record, if it worked we set a savepoint, if it failed we rolled back to the last savepoint (the end of the previous record), then do 1 commit at the end of the batch.

hai.thannks all for your posts.But how could you remember your lists of savepoints?I mean order you created for the record you process?though they are named savepoints(let us assume)how com you know which for for which record?nd created at what tie?Is there any way to kno such things from our java application?using jdbc?what i heard in in SQL we cant rememeber the order of savepoints(named even)...

we didn't have a list only 1 we kept moving

imagine you have 6 records, you are going round in a loop for each record.

// this is only pseudo code
while(more records){

  get record
  process record
  if(processing worked)
      set/move savepoint 
  else
      rollback to savepoint
      set savepoint // if the savepoint is now used up
  endif
} endwhile

commit;

if records 3 and 5 failed the processing would be

get 1
process 1
set savepoint
get 2
process 2
set savepoint
get 3
process 3
rollback to savepoint after 2
set new savepoint
get 4
process4
set savepoint
get 5
process 5
rollback to savepoint after 4
set savepoint
get 6
process 6
set savepoint

commit;

[Swetha Bhagavathula]

Hai.Thank you Wendy your example is simply superb ,also thanks to Martin, the way you explained me, suggested me to read tutorials and once again thanks to one and all for your valuable posts. .
JavaRanch

[Swetha Bhagavathula]

imagine you have 6 records, you are going round in a loop for each record.

// this is only pseudo code
while(more records){

  get record
  process record
  if(processing worked)
      set/move savepoint 
  else
      rollback to savepoint
      set savepoint // if the savepoint is now used up
  endif
} endwhile

commit;

if records 3 and 5 failed the processing would be

get 1
process 1
set savepoint
get 2
process 2
set savepoint
get 3
process 3
rollback to savepoint after 2
set new savepoint
get 4
process4
set savepoint
get 5
process 5
rollback to savepoint after 4
set savepoint
get 6
process 6
set savepoint


commit;

Hai wendy, please see,hope your example matches mine ???(without coding conventions)

// SavePointDemo.java

import java.sql.*;

public class SavePointDemo

{
      public static void main(String args[])throws Exception

          {

                // create jdbc connection object
                     Class.forName(-);// driver  class name
               Connection con=DriverManager.getConnection(protocol,subname,host_name/IPaddress,portno,logical db,uname,pwd);
              
              
                 // create Statement object
             Statement st=con.createStatement();
           
                  //Begin Tx(transaction)
                 con.setAutoCommit(false);
           
                 //qry1 (outside the savepoint area)
              st.addBatch("insert into xyz_table values(1,'xxx');
              st.addBatch("update xyz_table set x_couln_name='yyy' where y_coln_name=<condition1>");
             st.addBatch"delete from xyz_table where x_coln_name=<condition2>);                           
              
// set a named savepoint mysavept)

Savepoint myspt=con.setSavepoint("mysavept");

// qry2 in savepint area
 st.addBatch("insert into xyz_table values(1,'zzz');
              st.addBatch("update xyz_table set x_couln_name='yxy' where y_coln_name=<condition1>");
             st.addBatch"delete from xyz_table where x_coln_name=<condition2>);      
//rollback upto savepoint(mysavept)
//commit tx
con.commit();//end of Tx

qry2 statements will be rollback because the qry2 but record will be rollback bcuz th image the query2 is there in savepointnare

}
                     }
              
cute batch and its queries in db s/w
             int result[]=st.executeBatch();
.
.
.//some code
}

Please excuse me if am wrong...
so what is that only one savepoint is used though we process many Tx.. is that?sorry if my assumption is wrong .. help me..!!!

[Wendy L Gibbons]

we have 1 transaction, processing many records/rows.

I savepoint moves along as more and more records are processed sucessfully.

[Wendy L Gibbons]

so to add a transaction into my example

Wendy Gibbons wrote:

we didn't have a list only 1 we kept moving

imagine you have 6 records, you are going round in a loop for each record.

// this is only pseudo code
open transaction
while(more records){

  get record
  process record
  if(processing worked)
      set/move savepoint 
  else
      rollback to savepoint
      set savepoint // if the savepoint is now used up
  endif
} endwhile

if(enough records failed)
  rollback whole transaction
else
 commit;

close transaction

if records 3 and 5 failed the processing would be
open transaction
get record 1
process 1
set savepoint
get record 2
process 2
set savepoint
get record 3
process 3
rollback record 3 to savepoint after record 2
set new savepoint
get record 4
process4
set savepoint
get record 5
process 5
rollback record 5 to savepoint after record 4
set savepoint
get record 6
process 6
set savepoint

commit
close transaction