The ROLLBACK statement rolls back (ends) a transaction,
destroying any changes to SQL-data so that they never become
visible to subsequent transactions. The required syntax for
the ROLLBACK statement is as follows. 
 
ROLLBACK [ WORK ] [ AND [ NO ] CHAIN ] 
[ TO [ SAVEPOINT ] { | } ]
 
The ROLLBACK statement will either end a transaction,
destroying all data changes that happened during any of the
transaction, or it will just destroy any data changes that
happened since you established a savepoint. The basic form
of the ROLLBACK statement is just the keyword ROLLBACK (the
keyword WORK is simply noise and can be omitted without
changing the effect). 
 
The optional AND CHAIN clause is a convenience for
initiating a new transaction as soon as the old transaction
terminates. If AND CHAIN is specified, then there is
effectively nothing between the old and new transactions,
although they remain separate. The characteristics of the
new transaction will be the same as the characteristics of
the old one — that is, the new transaction will have the
same access mode, isolation level and diagnostics area size
(we'll discuss all of these shortly) as the transaction
just terminated. The AND NO CHAIN option just tells your
DBMS to end the transaction — that is, these four SQL
statements are equivalent: 
 
ROLLBACK;
 
ROLLBACK WORK;
 
ROLLBACK AND NO CHAIN;
 
ROLLBACK WORK AND NO CHAIN;
 
All of them end a transaction without saving any transaction
characteristics. The only other options, the equivalent
statements: 
 
ROLLBACK AND CHAIN;
 
ROLLBACK WORK AND CHAIN;
 
both tell your DBMS to end a transaction, but to save that
transaction's characteristics for the next transaction. 
 
ROLLBACK is much simpler than COMMIT: it may involve no more
than a few deletions (of Cursors, locks, prepared SQL
statements and log-file entries). It's usually assumed that
ROLLBACK can't fail, although such a thing is conceivable
(for example, an encompassing transaction might reject an
attempt to ROLLBACK because it's lining up for a COMMIT). 
 
ROLLBACK cancels all effects of a transaction. It does not
cancel effects on objects outside the DBMS's control (for
example the values in host program variables or the settings
made by some SQL/CLI function calls). But in general, it is
a convenient statement for those situations when you say
"oops, this isn't working" or when you simply don't care
whether your temporary work becomes permanent or not.
 
Here is a moot question. If all you've been doing is
SELECTs, so that there have been no data changes, should you
end the transaction with ROLLBACK or COMMIT? It shouldn't
really matter because both ROLLBACK and COMMIT do the same
transaction-terminating job. However, the popular conception
is that ROLLBACK implies failure, so after a successful
series of SELECT statements the convention is to end the
transaction with COMMIT rather than ROLLBACK.
 
MariaDB (and most other DBMSs) supports rollback of SQL-data
change statements, but not of SQL-Schema statements. This
means that if you use any of CREATE, ALTER, DROP, GRANT,
REVOKE, you are implicitly committing at execution time.
 
INSERT INTO Table_2 VALUES(5); 
DROP TABLE Table_3 CASCADE;
 
ROLLBACK;
 
The result will be that both the INSERT and the DROP will go
through as separate transactions so the ROLLBACK will have
no effect. 
 

 
 
 
 
 
 
 

URL: https://mariadb.com/kb/en/rollback/