CODEWITHSUNDEEP
mysqltransactionsinnodb
Eric Pigeon
Eric Pigeon

Reputation: 1131

InnoDb transactions with create statements

Are sql statements such as CREATE TABLE tbl_name ..... allowed in transactions.

For example:

begin;

CREATE TABLE .......;

sdfghjk;

rollback;

The table is still created despite a statement in the transaction failing, and a rollback at the end. Is there a way to prevent the table from being created if a statement in the transaction fails?

Upvotes: 0

Views: 599

Answers (2)

ravnur
ravnur

Reputation: 2852

If you still need to use table you can do create temporary table..... It doesn't commit transaction but will be deleted when connection will be closed.

Upvotes: 1

Todd
Todd

Reputation: 31720

DDL statements are allowed within transactions, but are not generally impacted by the transactions. From the MySQL Documentation on what can and cannot be rolled back:

Some statements cannot be rolled back. In general, these include data definition language (DDL) statements, such as those that create or drop databases, those that create, drop, or alter tables or stored routines.

You should design your transactions not to include such statements. If you issue a statement early in a transaction that cannot be rolled back, and then another statement later fails, the full effect of the transaction cannot be rolled back in such cases by issuing a ROLLBACK statement.

Source

Upvotes: 1

Related Questions