Commit a single attribute value and not whole transaction
I have a java program which does many interaction with DB (oracle) which is started by a single transaction. I need to update a column,say Y of some table say X, and that this should be committed but not the whole transaction.
Why I need that, is because when that java program to do some stuff and if fails somewhere then all the things done by java program is rolled back, but when I do commit in that java program itself after update the column in table X then it will also commit the things done till now and I won't be able to enjoy the rollback thing for whole transaction.
The value of that column is needed to handle concurrent program invocation.
java sql oracle concurrency
asked Nov 15 '18 at 12:25
Ankit ChauhanAnkit Chauhan
428
add a comment |
I have a java program which does many interaction with DB (oracle) which is started by a single transaction. I need to update a column,say Y of some table say X, and that this should be committed but not the whole transaction.
Why I need that, is because when that java program to do some stuff and if fails somewhere then all the things done by java program is rolled back, but when I do commit in that java program itself after update the column in table X then it will also commit the things done till now and I won't be able to enjoy the rollback thing for whole transaction.
The value of that column is needed to handle concurrent program invocation.
java sql oracle concurrency
asked Nov 15 '18 at 12:25
Ankit ChauhanAnkit Chauhan
428
Look in to Oracle's support for SAVEPOINTs. You can then open a transaction, do some stuff, mark a save point, do more stuff, and on an error decide which save point to roll back to.
– MatBailie
Nov 15 '18 at 12:29
docs.oracle.com/cd/B19306_01/server.102/b14220/transact.htm Refer this.
– Shaili
Nov 15 '18 at 12:30
add a comment |
I have a java program which does many interaction with DB (oracle) which is started by a single transaction. I need to update a column,say Y of some table say X, and that this should be committed but not the whole transaction.
Why I need that, is because when that java program to do some stuff and if fails somewhere then all the things done by java program is rolled back, but when I do commit in that java program itself after update the column in table X then it will also commit the things done till now and I won't be able to enjoy the rollback thing for whole transaction.
The value of that column is needed to handle concurrent program invocation.
java sql oracle concurrency
asked Nov 15 '18 at 12:25
Ankit ChauhanAnkit Chauhan
428
I have a java program which does many interaction with DB (oracle) which is started by a single transaction. I need to update a column,say Y of some table say X, and that this should be committed but not the whole transaction.
Why I need that, is because when that java program to do some stuff and if fails somewhere then all the things done by java program is rolled back, but when I do commit in that java program itself after update the column in table X then it will also commit the things done till now and I won't be able to enjoy the rollback thing for whole transaction.
The value of that column is needed to handle concurrent program invocation.
java sql oracle concurrency
java sql oracle concurrency
asked Nov 15 '18 at 12:25
Ankit ChauhanAnkit Chauhan
428
asked Nov 15 '18 at 12:25
Ankit ChauhanAnkit Chauhan
428
asked Nov 15 '18 at 12:25
Ankit ChauhanAnkit Chauhan
428
asked Nov 15 '18 at 12:25
Ankit ChauhanAnkit Chauhan
428
asked Nov 15 '18 at 12:25
Ankit ChauhanAnkit Chauhan
428
428
Look in to Oracle's support for SAVEPOINTs. You can then open a transaction, do some stuff, mark a save point, do more stuff, and on an error decide which save point to roll back to.
– MatBailie
Nov 15 '18 at 12:29
docs.oracle.com/cd/B19306_01/server.102/b14220/transact.htm Refer this.
– Shaili
Nov 15 '18 at 12:30
add a comment |
Look in to Oracle's support for SAVEPOINTs. You can then open a transaction, do some stuff, mark a save point, do more stuff, and on an error decide which save point to roll back to.
– MatBailie
Nov 15 '18 at 12:29
docs.oracle.com/cd/B19306_01/server.102/b14220/transact.htm Refer this.
– Shaili
Nov 15 '18 at 12:30
Look in to Oracle's support for SAVEPOINTs. You can then open a transaction, do some stuff, mark a save point, do more stuff, and on an error decide which save point to roll back to.
– MatBailie
Nov 15 '18 at 12:29
Look in to Oracle's support for SAVEPOINTs. You can then open a transaction, do some stuff, mark a save point, do more stuff, and on an error decide which save point to roll back to.
– MatBailie
Nov 15 '18 at 12:29
docs.oracle.com/cd/B19306_01/server.102/b14220/transact.htm Refer this.
– Shaili
Nov 15 '18 at 12:30
docs.oracle.com/cd/B19306_01/server.102/b14220/transact.htm Refer this.
– Shaili
Nov 15 '18 at 12:30
add a comment |
5 Answers
5
active
oldest
votes
I need to update a column,say Y of some table say X, and that this should be committed but not the whole transaction.
You can perform the update of X in an autonomous transaction.
CREATE PROCEDURE updateXofY(
in_id IN X.ID%TYPE,
in_y IN X.Y%TYPE
)
AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
UPDATE X
SET Y = in_y
WHERE id = in_id;
COMMIT;
END updateXofY;
/
Once you call the procedure it will update column Y of table X and COMMIT the autonomous transaction without committing the calling transaction.
The drawback of this is that the autonomous transaction is COMMITted so that if you ROLLBACK the main transaction then this will not ROLLBACK the autonomous transaction (since it is autonomous).
answered Nov 15 '18 at 12:47
MT0MT0
53.5k52756
add a comment |
Why can't you just start another transaction to do the "inner" work. Keep the current transaction running and use the new transaction to do the extra piece of work.
Unless if the outer transaction fails you want to roll back the inner transaction as well. In which case I think you need nested transactions.
answered Nov 15 '18 at 12:29
Michael WilesMichael Wiles
14.9k165692
1
To my knowledge, nested transactions don't roll back one level of nesting, they all go at once. Instead, as far as I just read briefly, Oracle supports SAVEPOINTs and at a ROLLBACK you can choose a save point to rollback to. If you don't choose which save point to roll back to, all transactions roll back.
– MatBailie
Nov 15 '18 at 12:31
add a comment |
Autonomous transactions are good advice. However, you could also open a second connection in your Java program. Handle the updates/commits in one connection and the main transaction in the other.
answered Nov 15 '18 at 13:46
Martin SchapendonkMartin Schapendonk
8,84431324
add a comment |
With naked JDBC, a new Connection as mentioned by Martin Schapendonk is the way to go.
With Spring the Propagation#REQUIRES_NEW on the Transactional annotation would be available. Similarly, with EJB one could use TransactionAttributeType#REQUIRES_NEW.
answered Nov 15 '18 at 16:37
MichalMichal
1,2961612
add a comment |
The reason behind an transaction is too be atomic. Therefore there is no general way to do this but save the value and update in another transaction.
answered Nov 15 '18 at 12:28
comradcomrad
907
2
A quick google tells me that Oracle support save points. Which should allow a partial rollback? (Although I'm not 100% clear if that's what the OP wants.)
– MatBailie
Nov 15 '18 at 12:28
Savepoints just jump back to the last one in the transaction, Right? I've understood OP's Question as if he wants to just Keep the data and discard everything else in the Transaction.
– comrad
Nov 16 '18 at 9:44
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53319482%2fcommit-a-single-attribute-value-and-not-whole-transaction%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
5 Answers
5
active
oldest
votes
5 Answers
5
active
oldest
votes
active
oldest
votes
active
oldest
votes
I need to update a column,say Y of some table say X, and that this should be committed but not the whole transaction.
You can perform the update of X in an autonomous transaction.
CREATE PROCEDURE updateXofY(
in_id IN X.ID%TYPE,
in_y IN X.Y%TYPE
)
AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
UPDATE X
SET Y = in_y
WHERE id = in_id;
COMMIT;
END updateXofY;
/
Once you call the procedure it will update column Y of table X and COMMIT the autonomous transaction without committing the calling transaction.
The drawback of this is that the autonomous transaction is COMMITted so that if you ROLLBACK the main transaction then this will not ROLLBACK the autonomous transaction (since it is autonomous).
answered Nov 15 '18 at 12:47
MT0MT0
53.5k52756
add a comment |
I need to update a column,say Y of some table say X, and that this should be committed but not the whole transaction.
You can perform the update of X in an autonomous transaction.
CREATE PROCEDURE updateXofY(
in_id IN X.ID%TYPE,
in_y IN X.Y%TYPE
)
AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
UPDATE X
SET Y = in_y
WHERE id = in_id;
COMMIT;
END updateXofY;
/
Once you call the procedure it will update column Y of table X and COMMIT the autonomous transaction without committing the calling transaction.
The drawback of this is that the autonomous transaction is COMMITted so that if you ROLLBACK the main transaction then this will not ROLLBACK the autonomous transaction (since it is autonomous).
answered Nov 15 '18 at 12:47
MT0MT0
53.5k52756
add a comment |
I need to update a column,say Y of some table say X, and that this should be committed but not the whole transaction.
You can perform the update of X in an autonomous transaction.
CREATE PROCEDURE updateXofY(
in_id IN X.ID%TYPE,
in_y IN X.Y%TYPE
)
AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
UPDATE X
SET Y = in_y
WHERE id = in_id;
COMMIT;
END updateXofY;
/
Once you call the procedure it will update column Y of table X and COMMIT the autonomous transaction without committing the calling transaction.
The drawback of this is that the autonomous transaction is COMMITted so that if you ROLLBACK the main transaction then this will not ROLLBACK the autonomous transaction (since it is autonomous).
answered Nov 15 '18 at 12:47
MT0MT0
53.5k52756
I need to update a column,say Y of some table say X, and that this should be committed but not the whole transaction.
You can perform the update of X in an autonomous transaction.
CREATE PROCEDURE updateXofY(
in_id IN X.ID%TYPE,
in_y IN X.Y%TYPE
)
AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
UPDATE X
SET Y = in_y
WHERE id = in_id;
COMMIT;
END updateXofY;
/
Once you call the procedure it will update column Y of table X and COMMIT the autonomous transaction without committing the calling transaction.
The drawback of this is that the autonomous transaction is COMMITted so that if you ROLLBACK the main transaction then this will not ROLLBACK the autonomous transaction (since it is autonomous).
answered Nov 15 '18 at 12:47
MT0MT0
53.5k52756
answered Nov 15 '18 at 12:47
MT0MT0
53.5k52756
answered Nov 15 '18 at 12:47
MT0MT0
53.5k52756
answered Nov 15 '18 at 12:47
MT0MT0
53.5k52756
53.5k52756
add a comment |
add a comment |
Why can't you just start another transaction to do the "inner" work. Keep the current transaction running and use the new transaction to do the extra piece of work.
Unless if the outer transaction fails you want to roll back the inner transaction as well. In which case I think you need nested transactions.
answered Nov 15 '18 at 12:29
Michael WilesMichael Wiles
14.9k165692
1
To my knowledge, nested transactions don't roll back one level of nesting, they all go at once. Instead, as far as I just read briefly, Oracle supports SAVEPOINTs and at a ROLLBACK you can choose a save point to rollback to. If you don't choose which save point to roll back to, all transactions roll back.
– MatBailie
Nov 15 '18 at 12:31
add a comment |
Why can't you just start another transaction to do the "inner" work. Keep the current transaction running and use the new transaction to do the extra piece of work.
Unless if the outer transaction fails you want to roll back the inner transaction as well. In which case I think you need nested transactions.
answered Nov 15 '18 at 12:29
Michael WilesMichael Wiles
14.9k165692
1
To my knowledge, nested transactions don't roll back one level of nesting, they all go at once. Instead, as far as I just read briefly, Oracle supports SAVEPOINTs and at a ROLLBACK you can choose a save point to rollback to. If you don't choose which save point to roll back to, all transactions roll back.
– MatBailie
Nov 15 '18 at 12:31
add a comment |
Why can't you just start another transaction to do the "inner" work. Keep the current transaction running and use the new transaction to do the extra piece of work.
Unless if the outer transaction fails you want to roll back the inner transaction as well. In which case I think you need nested transactions.
answered Nov 15 '18 at 12:29
Michael WilesMichael Wiles
14.9k165692
Why can't you just start another transaction to do the "inner" work. Keep the current transaction running and use the new transaction to do the extra piece of work.
Unless if the outer transaction fails you want to roll back the inner transaction as well. In which case I think you need nested transactions.
answered Nov 15 '18 at 12:29
Michael WilesMichael Wiles
14.9k165692
answered Nov 15 '18 at 12:29
Michael WilesMichael Wiles
14.9k165692
answered Nov 15 '18 at 12:29
Michael WilesMichael Wiles
14.9k165692
answered Nov 15 '18 at 12:29
Michael WilesMichael Wiles
14.9k165692
14.9k165692
1
To my knowledge, nested transactions don't roll back one level of nesting, they all go at once. Instead, as far as I just read briefly, Oracle supports SAVEPOINTs and at a ROLLBACK you can choose a save point to rollback to. If you don't choose which save point to roll back to, all transactions roll back.
– MatBailie
Nov 15 '18 at 12:31
add a comment |
1
To my knowledge, nested transactions don't roll back one level of nesting, they all go at once. Instead, as far as I just read briefly, Oracle supports SAVEPOINTs and at a ROLLBACK you can choose a save point to rollback to. If you don't choose which save point to roll back to, all transactions roll back.
– MatBailie
Nov 15 '18 at 12:31
1
1
To my knowledge, nested transactions don't roll back one level of nesting, they all go at once. Instead, as far as I just read briefly, Oracle supports SAVEPOINTs and at a ROLLBACK you can choose a save point to rollback to. If you don't choose which save point to roll back to, all transactions roll back.
– MatBailie
Nov 15 '18 at 12:31
To my knowledge, nested transactions don't roll back one level of nesting, they all go at once. Instead, as far as I just read briefly, Oracle supports SAVEPOINTs and at a ROLLBACK you can choose a save point to rollback to. If you don't choose which save point to roll back to, all transactions roll back.
– MatBailie
Nov 15 '18 at 12:31
add a comment |
Autonomous transactions are good advice. However, you could also open a second connection in your Java program. Handle the updates/commits in one connection and the main transaction in the other.
answered Nov 15 '18 at 13:46
Martin SchapendonkMartin Schapendonk
8,84431324
add a comment |
Autonomous transactions are good advice. However, you could also open a second connection in your Java program. Handle the updates/commits in one connection and the main transaction in the other.
answered Nov 15 '18 at 13:46
Martin SchapendonkMartin Schapendonk
8,84431324
add a comment |
Autonomous transactions are good advice. However, you could also open a second connection in your Java program. Handle the updates/commits in one connection and the main transaction in the other.
answered Nov 15 '18 at 13:46
Martin SchapendonkMartin Schapendonk
8,84431324
Autonomous transactions are good advice. However, you could also open a second connection in your Java program. Handle the updates/commits in one connection and the main transaction in the other.
answered Nov 15 '18 at 13:46
Martin SchapendonkMartin Schapendonk
8,84431324
answered Nov 15 '18 at 13:46
Martin SchapendonkMartin Schapendonk
8,84431324
answered Nov 15 '18 at 13:46
Martin SchapendonkMartin Schapendonk
8,84431324
answered Nov 15 '18 at 13:46
Martin SchapendonkMartin Schapendonk
8,84431324
8,84431324
add a comment |
add a comment |
With naked JDBC, a new Connection as mentioned by Martin Schapendonk is the way to go.
With Spring the Propagation#REQUIRES_NEW on the Transactional annotation would be available. Similarly, with EJB one could use TransactionAttributeType#REQUIRES_NEW.
answered Nov 15 '18 at 16:37
MichalMichal
1,2961612
add a comment |
With naked JDBC, a new Connection as mentioned by Martin Schapendonk is the way to go.
With Spring the Propagation#REQUIRES_NEW on the Transactional annotation would be available. Similarly, with EJB one could use TransactionAttributeType#REQUIRES_NEW.
answered Nov 15 '18 at 16:37
MichalMichal
1,2961612
add a comment |
With naked JDBC, a new Connection as mentioned by Martin Schapendonk is the way to go.
With Spring the Propagation#REQUIRES_NEW on the Transactional annotation would be available. Similarly, with EJB one could use TransactionAttributeType#REQUIRES_NEW.
answered Nov 15 '18 at 16:37
MichalMichal
1,2961612
With naked JDBC, a new Connection as mentioned by Martin Schapendonk is the way to go.
With Spring the Propagation#REQUIRES_NEW on the Transactional annotation would be available. Similarly, with EJB one could use TransactionAttributeType#REQUIRES_NEW.
answered Nov 15 '18 at 16:37
MichalMichal
1,2961612
answered Nov 15 '18 at 16:37
MichalMichal
1,2961612
answered Nov 15 '18 at 16:37
MichalMichal
1,2961612
answered Nov 15 '18 at 16:37
MichalMichal
1,2961612
1,2961612
add a comment |
add a comment |
The reason behind an transaction is too be atomic. Therefore there is no general way to do this but save the value and update in another transaction.
answered Nov 15 '18 at 12:28
comradcomrad
907
2
A quick google tells me that Oracle support save points. Which should allow a partial rollback? (Although I'm not 100% clear if that's what the OP wants.)
– MatBailie
Nov 15 '18 at 12:28
Savepoints just jump back to the last one in the transaction, Right? I've understood OP's Question as if he wants to just Keep the data and discard everything else in the Transaction.
– comrad
Nov 16 '18 at 9:44
add a comment |
The reason behind an transaction is too be atomic. Therefore there is no general way to do this but save the value and update in another transaction.
answered Nov 15 '18 at 12:28
comradcomrad
907
2
A quick google tells me that Oracle support save points. Which should allow a partial rollback? (Although I'm not 100% clear if that's what the OP wants.)
– MatBailie
Nov 15 '18 at 12:28
Savepoints just jump back to the last one in the transaction, Right? I've understood OP's Question as if he wants to just Keep the data and discard everything else in the Transaction.
– comrad
Nov 16 '18 at 9:44
add a comment |
The reason behind an transaction is too be atomic. Therefore there is no general way to do this but save the value and update in another transaction.
answered Nov 15 '18 at 12:28
comradcomrad
907
The reason behind an transaction is too be atomic. Therefore there is no general way to do this but save the value and update in another transaction.
answered Nov 15 '18 at 12:28
comradcomrad
907
answered Nov 15 '18 at 12:28
comradcomrad
907
answered Nov 15 '18 at 12:28
comradcomrad
907
answered Nov 15 '18 at 12:28
comradcomrad
907
907
2
A quick google tells me that Oracle support save points. Which should allow a partial rollback? (Although I'm not 100% clear if that's what the OP wants.)
– MatBailie
Nov 15 '18 at 12:28
Savepoints just jump back to the last one in the transaction, Right? I've understood OP's Question as if he wants to just Keep the data and discard everything else in the Transaction.
– comrad
Nov 16 '18 at 9:44
add a comment |
2
A quick google tells me that Oracle support save points. Which should allow a partial rollback? (Although I'm not 100% clear if that's what the OP wants.)
– MatBailie
Nov 15 '18 at 12:28
Savepoints just jump back to the last one in the transaction, Right? I've understood OP's Question as if he wants to just Keep the data and discard everything else in the Transaction.
– comrad
Nov 16 '18 at 9:44
2
2
A quick google tells me that Oracle support save points. Which should allow a partial rollback? (Although I'm not 100% clear if that's what the OP wants.)
– MatBailie
Nov 15 '18 at 12:28
A quick google tells me that Oracle support save points. Which should allow a partial rollback? (Although I'm not 100% clear if that's what the OP wants.)
– MatBailie
Nov 15 '18 at 12:28
Savepoints just jump back to the last one in the transaction, Right? I've understood OP's Question as if he wants to just Keep the data and discard everything else in the Transaction.
– comrad
Nov 16 '18 at 9:44
Savepoints just jump back to the last one in the transaction, Right? I've understood OP's Question as if he wants to just Keep the data and discard everything else in the Transaction.
– comrad
Nov 16 '18 at 9:44
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53319482%2fcommit-a-single-attribute-value-and-not-whole-transaction%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Look in to Oracle's support for SAVEPOINTs. You can then open a transaction, do some stuff, mark a save point, do more stuff, and on an error decide which save point to roll back to.
– MatBailie
Nov 15 '18 at 12:29
docs.oracle.com/cd/B19306_01/server.102/b14220/transact.htm Refer this.
– Shaili
Nov 15 '18 at 12:30