SCJP5
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.
SCJP5
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.
SCJP5
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.
SCJP5
SCJP5
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.
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.
SCJP5
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...
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...
SCJP5
Swetha Bhagavathula wrote:Yes my question specifically pointing on application not sql client based.Anyways thank you all..
.
No more Blub for me, thank you, Vicar.
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.
SCJP5
No more Blub for me, thank you, Vicar.
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.)
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.
SCJP5
SCJP5
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)...
SCJP5
imagine you have 6 records, you are going round in a loop for each record.
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;
SCJP5
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.
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