Timeline for ROLLBACK doesn't work after INSERT INTO newly created destination table
Current License: CC BY-SA 3.0
16 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Jan 20, 2015 at 14:44 | vote | accept | Dimitry K | ||
| Jan 19, 2015 at 18:27 | comment | added | mustaccio | I'm not suggesting anything, just trying to understand what the answer is. | |
| Jan 19, 2015 at 17:25 | comment | added | ypercubeᵀᴹ | If you start with autocommit off on the other hand, you'll have to always commit or rollback the transaction. I'm not sure why you are suggesting that. I suggest explicitly starting and committing the transactions. | |
| Jan 19, 2015 at 17:23 | comment | added | ypercubeᵀᴹ | Yes, there are two parts on the reasoning but the main part in my opinion, that the OP could not figure out was the implicit commit by the create table. | |
| Jan 19, 2015 at 17:23 | comment | added | mustaccio |
In other words, if the problem is in the autocommit setting, it should not matter if you start the transaction after the CREATE implicitly or explicitly.
|
|
| Jan 19, 2015 at 17:22 | comment | added | mustaccio |
But your answer starts with some statements... cause an implicit commit, and the suggestion is to move around the CREATE TABLE statement...
|
|
| Jan 19, 2015 at 17:21 | history | edited | ypercubeᵀᴹ | CC BY-SA 3.0 |
added 24 characters in body
|
| Jan 19, 2015 at 17:17 | comment | added | ypercubeᵀᴹ |
@mustaccio No, I'm saying that "By default, MySQL starts the session for each new connection with autocommit mode enabled, so MySQL does a commit after each SQL statement if that statement did not return an error." That's what my "autocommit (guess)" refers to. I'm guessing he had autocommit on. The explicit transaction he had started had been commited by the CREATE TABLE but the INSERT transaction was auto-committed.
|
|
| Jan 19, 2015 at 17:13 | comment | added | mustaccio |
So you're saying that the implicit commit before the INSERT, caused by the DDL statement, also somehow causes a commit after the insert?
|
|
| Jan 19, 2015 at 16:23 | comment | added | Dimitry K |
@ypercube welcome! It took me a while to figure where exactly this CREAT TABLE will cause an implicit commit... So had to do this outline on paper anyways :) @RolandoMySQLDBA thank for rapid input as well. I have read few dozens of your replies in the last year and they helped me a lot!!
|
|
| Jan 19, 2015 at 16:03 | comment | added | ypercubeᵀᴹ | And @RolandoMySQLDBA for your kind words. I am the FGITW today (and only 15 seconds faster than you ;) | |
| Jan 19, 2015 at 15:52 | comment | added | ypercubeᵀᴹ | @Dimitry, thnx for the edit. | |
| S Jan 19, 2015 at 15:44 | history | edited | ypercubeᵀᴹ | CC BY-SA 3.0 |
tried to visualize what actually happens. maybe someone finds useful.
|
| S Jan 19, 2015 at 15:44 | history | suggested | Dimitry K | CC BY-SA 3.0 |
tried to visualize what actually happens. maybe someone finds useful.
|
| Jan 19, 2015 at 15:10 | review | Suggested edits | |||
| S Jan 19, 2015 at 15:44 | |||||
| Jan 19, 2015 at 14:45 | history | answered | ypercubeᵀᴹ | CC BY-SA 3.0 |