Do Ddl Statements Always Give You an Implicit Commit, or Can You Get an Implicit Rollback

Do DDL statements always give you an implicit commit, or can you get an implicit rollback?

No, it will always commit.

If you want to rollback, you'll have to do it before the DDL.

If you want to isolate the DDL from your existing transaction, then you will have to execute it in its' own, separate transaction.

DDL command commits transaction even if it fails

That's because Oracle issues a commit both before and after DDL, as per the documentation:

Oracle Database issues an implicit COMMIT under the following circumstances:

• Before any syntactically valid data definition language (DDL) statement, even if the statement results in an error
• After any data definition language (DDL) statement that completes without an error

oracle - what statements need to be committed?

DML (Data Manipulation Language) commands need to be commited/rolled back. Here is a list of those commands.

Data Manipulation Language (DML) statements are used for managing data
within schema objects. Some examples:

INSERT - insert data into a table
UPDATE - updates existing data within a table
DELETE - deletes records from a table, the space for the records remain
MERGE - UPSERT operation (insert or update)
CALL - call a PL/SQL or Java subprogram
EXPLAIN PLAN - explain access path to data
LOCK TABLE - control concurrency

How to prevent mysql implicit commit

Another hacky approach that I just tried successfully is to get the create table DDL via the mysql specific command

SHOW CREATE TABLE `tableName`

Then make some regexp magic and craft a new DDL query that will create a temporary table based on the original table, with all the alter table changes incorporated into the create table.

In my PHP based project, I did the following to add a unique index to a temporary table. It did the trick and no more implicit commit occurred midst the transaction.

$createDDL = ... get from SHOW CREATE TABLE `tableName`
$nr = 0;
$createDDL = preg_replace("/CREATE TABLE `$tableName` \(/", "CREATE TEMPORARY TABLE `$tmpName` (\nUNIQUE `ukey-1` ($uniqCols),", $createDDL, -1, $nr);
if (!$nr)
  throw new Exception("CREATE TABLE replacement error. No reps made.");
mysqli_query($con, $createDDL);

EDIT By the way here are some bug (feature) reports (since many years). In the first you can see a response (dating to 2006) that states: since this behavior is the same as in oracle db, this is the consistent one...

• http://bugs.mysql.com/bug.php?id=22857
• http://bugs.mysql.com/bug.php?id=28109

Maybe a feature request "spamming campaign" should be initiated for this feature request to revive!

mysql: working around implicit transaction commits?

No. MySQL does not support transactional DDL. You either need to separate your DDL statements from DML statements, or perhaps try to use migration tool like RuckUsing

Determining the current AUTOCOMMIT DDL setting

Yes, there is.
If the AUTOCOMMIT DDL has been set to OFF, then M_SESSION_CONTEXT contains the key
DDL_AUTO_COMMIT with value FALSE.

select key, value 
    from m_session_context 
where 
    connection_id = current_connection
and key ='DDL_AUTO_COMMIT';

KEY             VALUE
DDL_AUTO_COMMIT FALSE

So, if you don't find this record, the AUTOCOMMIT DDL mode is ON (default).

---

Related Topics