Implicit rollback is the behaviour on transactional engines:

MariaDB [test]> drop table if exists x;

Query OK, 0 rows affected (0.02 sec)

MariaDB [test]> CREATE TABLE `x` (

    ->   `x` int(11) DEFAULT NULL,

    ->   `y` int(11) DEFAULT NULL

    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> start transaction;

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> insert into x values (3,5);

Query OK, 1 row affected (0.00 sec)

MariaDB [test]> Bye

danblack@catinthehat:~$ mysql test

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 19156

Server version: 10.0.21-MariaDB-1~jessie-log mariadb.org binary distribution

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [test]> select * from x;

Empty set (0.00 sec)

please show the code/sql that shows an explicit commit? What is the table engine type?

danblack, implicit rollback is the behavior only in case of connection termination, just as your example with MySQL client shows.
mikhail describes a different situation, when the connection stays alive, and the next script (I suppose rather the next part of the same script?) is able to re-use this connection. That is,

BEGIN;

# transactional DML 1

# something happened, job was interrupted

# another job starts on the same connection

BEGIN;

# transactional DML 2

COMMIT;

So, the current behavior is that upon the second BEGIN, "transactional DML 1" is committed. Mikhail wants to be able to configure the server so instead it's rolled back.

ack, thanks elenst, make sense. Is this a FR variable to work around poorly implemented applications?

I'm thinking implicit behaviours should emit a warning as this seems to be a application execution path problem - lack of proper exception handling (MDEV-7389 to trace it happening if an application doesn't log it) or poor application pooling. The @@in_transaction variable is available if the application can't determine its own state.

For the cases of application functions doing their own mini transactions is the following a correct paradigm (for non-recursive functions)?

if (!@@in_transaction) {

  START TRANSACTION;

  $started_trx = true;

}

SAVEPOINT my_funct;

..

except {

   ROLLBACK TO my_funct;

}

if ($started_trx) COMMIT;

Is there anything else SQL needs to do to support application coding paradigms? (if so separate FR)

Is there anything else SQL needs to do to support application coding paradigms? (if so separate FR)

1.
Yes, since 10.1.1 can have compound statements outside of stored programs it can be easy implemented on application side.

IF @@in_transaction THEN

	ROLLBACK;

END IF;

START TRANSACTION;

But I can't check how it works because unable upgrade my server to 10.1.8 MDEV-9039

2.
And what we will do with already written applications (frameworks/connections library's)?
I think that this option still makes sense.

But I can't check how it works because unable upgrade my server to 10.1.8 MDEV-9039

mikhail, last night I added a comment to MDEV-9039 with a workaround for the problem, did you try it?

# mysql

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 8

Server version: 10.1.8-MariaDB MariaDB Server

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> DELIMITER '|'; IF @@in_transaction THEN ROLLBACK; END IF| DELIMETER ';'| START TRANSACTION;

MariaDB [(none)]> SELECT 1;

    -> 

    -> |

+---+

| 1 |

+---+

| 1 |

+---+

1 row in set (0,00 sec)

Don't understand how restore standard delimiter.

I don't see why this feature may be needed in that particular form. In the connection pooling when a new "user" is assigned to the existing connection from the pool, it needs to reset the connection anyway. Like, restore session variables, remove temporary tables and user variables, change the current database back, etc. This "etc" includes rolling back an unfinished transaction.

So what you really need, I suppose, is an easy way to "reset" the connection. Normally one uses CHANGE USER for that, but we can have something like a dedicated "RESET CONNECTION" command too.

So what you really need, I suppose, is an easy way to "reset" the connection. Normally one uses CHANGE USER for that, but we can have something like a dedicated "RESET CONNECTION" command too.

Yes, this is exactly what I need, thank you, Sergei. I need 'RESET CONNECTION' command which will also do implicit rollback.

I couldn't find documentation for 'CHANGE USER' statement. Where I can found description?

And question by 'CHANGE USER' you mean SQL or application user? I ask this because interested see in 'SHOW PROCESSLIST' also application users in separated column.

It would be interesting such variant:

On application side:

RESET CONNECTION;

SET @ap_user = 'application_user';

try {

    START TRANSACTION;

    /* useful code */

    COMMIT;

} catch {

    ROLLBACK;

}

RESET CONNECTION;

Administrator may see processlist with application users:

SELECT P.*, get_user_variable(P.ID, 'ap_user') FROM INFORMATION_SCHEMA.PROCESSLIST P