Is every SQL undo when it never reaches the point of COMMIT?
up vote
0
down vote
favorite
I think I got the principle right I just want to make sure I get it right.
So when autocommit is enabled it means every command I do wont be executed directly, except whose who trigger the commit themselves.
So when I've for example a basic macro running like:
statement.executeUpdate("SET autocommit = 0;")
//some code
//SQL Queries
//SQL DELETEs
//SQL INSERTs
statement.executeUpdate("COMMIT;")
Then what would happen would be - If the script runs through without any problem the script goes to the point where every SQL Statement is executed and COMMITed at the end, if not and an error or exception happens the script breaks at that point never turns to the point where the COMMIT is going to happen and every change prior to that point is undo, so that every deleted information will still be there and every insertion is thrown away.
Is it that simple or did I get something wrong?
mysql sql
edited Nov 29 at 18:14
Jerry Stratton
2,2831622
asked Nov 22 at 16:00
vMysterion
136
|
show 1 more comment
up vote
0
down vote
favorite
I think I got the principle right I just want to make sure I get it right.
So when autocommit is enabled it means every command I do wont be executed directly, except whose who trigger the commit themselves.
So when I've for example a basic macro running like:
statement.executeUpdate("SET autocommit = 0;")
//some code
//SQL Queries
//SQL DELETEs
//SQL INSERTs
statement.executeUpdate("COMMIT;")
Then what would happen would be - If the script runs through without any problem the script goes to the point where every SQL Statement is executed and COMMITed at the end, if not and an error or exception happens the script breaks at that point never turns to the point where the COMMIT is going to happen and every change prior to that point is undo, so that every deleted information will still be there and every insertion is thrown away.
Is it that simple or did I get something wrong?
mysql sql
edited Nov 29 at 18:14
Jerry Stratton
2,2831622
asked Nov 22 at 16:00
vMysterion
136
That is how transactions work. So, you are using them correctly.
– Gordon Linoff
Nov 22 at 16:02
Why not try and see what happens? ;)
– Nico Haase
Nov 22 at 16:06
If theCOMMITis not reached (due to an error or whatever), the transaction remains open. You shouldROLLBACKto close the transaction.
– Robert Kock
Nov 22 at 16:09
@RobertKock Like having the ROLLBACK in something like a finally block?
– vMysterion
Nov 22 at 16:25
@vMysterion more like a catch block. Finally is executed also in case of success
– Robert Kock
Nov 22 at 16:28
|
show 1 more comment
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I think I got the principle right I just want to make sure I get it right.
So when autocommit is enabled it means every command I do wont be executed directly, except whose who trigger the commit themselves.
So when I've for example a basic macro running like:
statement.executeUpdate("SET autocommit = 0;")
//some code
//SQL Queries
//SQL DELETEs
//SQL INSERTs
statement.executeUpdate("COMMIT;")
Then what would happen would be - If the script runs through without any problem the script goes to the point where every SQL Statement is executed and COMMITed at the end, if not and an error or exception happens the script breaks at that point never turns to the point where the COMMIT is going to happen and every change prior to that point is undo, so that every deleted information will still be there and every insertion is thrown away.
Is it that simple or did I get something wrong?
mysql sql
edited Nov 29 at 18:14
Jerry Stratton
2,2831622
asked Nov 22 at 16:00
vMysterion
136
I think I got the principle right I just want to make sure I get it right.
So when autocommit is enabled it means every command I do wont be executed directly, except whose who trigger the commit themselves.
So when I've for example a basic macro running like:
statement.executeUpdate("SET autocommit = 0;")
//some code
//SQL Queries
//SQL DELETEs
//SQL INSERTs
statement.executeUpdate("COMMIT;")
Then what would happen would be - If the script runs through without any problem the script goes to the point where every SQL Statement is executed and COMMITed at the end, if not and an error or exception happens the script breaks at that point never turns to the point where the COMMIT is going to happen and every change prior to that point is undo, so that every deleted information will still be there and every insertion is thrown away.
Is it that simple or did I get something wrong?
mysql sql
mysql sql
edited Nov 29 at 18:14
Jerry Stratton
2,2831622
asked Nov 22 at 16:00
vMysterion
136
edited Nov 29 at 18:14
Jerry Stratton
2,2831622
asked Nov 22 at 16:00
vMysterion
136
edited Nov 29 at 18:14
Jerry Stratton
2,2831622
edited Nov 29 at 18:14
Jerry Stratton
2,2831622
edited Nov 29 at 18:14
Jerry Stratton
2,2831622
2,2831622
asked Nov 22 at 16:00
vMysterion
136
asked Nov 22 at 16:00
vMysterion
136
asked Nov 22 at 16:00
vMysterion
136
136
That is how transactions work. So, you are using them correctly.
– Gordon Linoff
Nov 22 at 16:02
Why not try and see what happens? ;)
– Nico Haase
Nov 22 at 16:06
If theCOMMITis not reached (due to an error or whatever), the transaction remains open. You shouldROLLBACKto close the transaction.
– Robert Kock
Nov 22 at 16:09
@RobertKock Like having the ROLLBACK in something like a finally block?
– vMysterion
Nov 22 at 16:25
@vMysterion more like a catch block. Finally is executed also in case of success
– Robert Kock
Nov 22 at 16:28
|
show 1 more comment
That is how transactions work. So, you are using them correctly.
– Gordon Linoff
Nov 22 at 16:02
Why not try and see what happens? ;)
– Nico Haase
Nov 22 at 16:06
If theCOMMITis not reached (due to an error or whatever), the transaction remains open. You shouldROLLBACKto close the transaction.
– Robert Kock
Nov 22 at 16:09
@RobertKock Like having the ROLLBACK in something like a finally block?
– vMysterion
Nov 22 at 16:25
@vMysterion more like a catch block. Finally is executed also in case of success
– Robert Kock
Nov 22 at 16:28
That is how transactions work. So, you are using them correctly.
– Gordon Linoff
Nov 22 at 16:02
That is how transactions work. So, you are using them correctly.
– Gordon Linoff
Nov 22 at 16:02
Why not try and see what happens? ;)
– Nico Haase
Nov 22 at 16:06
Why not try and see what happens? ;)
– Nico Haase
Nov 22 at 16:06
If the
COMMIT is not reached (due to an error or whatever), the transaction remains open. You should ROLLBACK to close the transaction.– Robert Kock
Nov 22 at 16:09
If the
COMMIT is not reached (due to an error or whatever), the transaction remains open. You should ROLLBACK to close the transaction.– Robert Kock
Nov 22 at 16:09
@RobertKock Like having the ROLLBACK in something like a finally block?
– vMysterion
Nov 22 at 16:25
@RobertKock Like having the ROLLBACK in something like a finally block?
– vMysterion
Nov 22 at 16:25
@vMysterion more like a catch block. Finally is executed also in case of success
– Robert Kock
Nov 22 at 16:28
@vMysterion more like a catch block. Finally is executed also in case of success
– Robert Kock
Nov 22 at 16:28
|
show 1 more comment
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f53434606%2fis-every-sql-undo-when-it-never-reaches-the-point-of-commit%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
That is how transactions work. So, you are using them correctly.
– Gordon Linoff
Nov 22 at 16:02
Why not try and see what happens? ;)
– Nico Haase
Nov 22 at 16:06
If the
COMMITis not reached (due to an error or whatever), the transaction remains open. You shouldROLLBACKto close the transaction.– Robert Kock
Nov 22 at 16:09
@RobertKock Like having the ROLLBACK in something like a finally block?
– vMysterion
Nov 22 at 16:25
@vMysterion more like a catch block. Finally is executed also in case of success
– Robert Kock
Nov 22 at 16:28