MySQL Bugs: #89334: Insert not rollbacked in transaction when ObjectOptimisticLockingFailureExceptio

Bug #89334	Insert not rollbacked in transaction when ObjectOptimisticLockingFailureExceptio
Submitted:	20 Jan 2018 10:56	Modified:	7 Feb 2018 16:59
Reporter:	Martin Tlachac	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	Ver 14.14 Distrib 5.7.20, for Linux (x86	OS:	Ubuntu (16.04)
Assigned to:	Chiranjeevi Battula	CPU Architecture:	Any
Tags:	rollback insert transaction

Description:
https://stackoverflow.com/questions/48249117/insert-not-rollbacked-in-transactional-method...

LOG:

>START...............................
>2018-01-14T15:48:03.913784Z         8 Query     set session transaction read only    
>2018-01-14T15:48:03.913963Z         8 Query     SET autocommit=0    
>2018-01-14T15:48:03.921491Z         8 Query     select account0_.id as id1_0_0_, account0_.balance as balance2_0_0_, account0_.version as version3_0_0_ from account account0_ where account0_.id=1    
>2018-01-14T15:48:03.930767Z         8 Query     commit    
>2018-01-14T15:48:03.930929Z         8 Query     SET autocommit=1    
>2018-01-14T15:48:03.931281Z         8 Query     select @@session.tx_read_only    
>2018-01-14T15:48:03.931607Z         8 Query     set session transaction read write    
>2018-01-14T15:48:03.932052Z         8 Query     set session transaction read only    
>2018-01-14T15:48:03.932173Z         8 Query     SET autocommit=0    
>2018-01-14T15:48:03.932750Z         8 Query     select account0_.id as id1_0_0_, account0_.balance as balance2_0_0_, account0_.version as version3_0_0_ from account account0_ where account0_.id=2    
>2018-01-14T15:48:03.933613Z         8 Query     commit    
>2018-01-14T15:48:03.933744Z         8 Query     SET autocommit=1    
>2018-01-14T15:48:03.933968Z         8 Query     select @@session.tx_read_only    
>2018-01-14T15:48:03.934134Z         8 Query     set session transaction read write    
>2018-01-14T15:48:03.937186Z         8 Query     SET autocommit=0    
>2018-01-14T15:48:11.995802Z         9 Query     set session transaction read only    
>2018-01-14T15:48:11.996386Z         9 Query     SET autocommit=0    
>2018-01-14T15:48:11.999184Z         9 Query     select account0_.id as id1_0_0_, account0_.balance as balance2_0_0_, account0_.version as version3_0_0_ from account account0_ where account0_.id=2    
>2018-01-14T15:48:12.002778Z         9 Query     commit    
>2018-01-14T15:48:12.003217Z         9 Query     SET autocommit=1    
>2018-01-14T15:48:12.003848Z         9 Query     select @@session.tx_read_only    
>2018-01-14T15:48:12.004822Z         9 Query     set session transaction read write    
>2018-01-14T15:48:12.006758Z         9 Query     set session transaction read only     
>2018-01-14T15:48:12.008320Z         9 Query     SET autocommit=0    
>2018-01-14T15:48:12.012266Z         9 Query     select account0_.id as id1_0_0_, account0_.balance as balance2_0_0_, account0_.version as version3_0_0_ from account account0_ where account0_.id=1    
>2018-01-14T15:48:12.015808Z         9 Query     commit    
>2018-01-14T15:48:12.016214Z         9 Query     SET autocommit=1    
>2018-01-14T15:48:12.016792Z         9 Query     select @@session.tx_read_only    
>2018-01-14T15:48:12.017278Z         9 Query     set session transaction read write    
>2018-01-14T15:48:12.018332Z         9 Query     SET autocommit=0
>2018-01-14T15:48:12.078209Z         9 Query     insert into transfer (ammount, from_id, to_id, type) values (15, 2, 1, null)    
>2018-01-14T15:48:12.082738Z         9 Query     update account set balance=85.00, version=1 where id=2 and version=0    
>2018-01-14T15:48:12.084494Z         9 Query     update account set balance=115.00, version=1 where id=1 and version=0    
>2018-01-14T15:48:12.084946Z         9 Query     commit    
>2018-01-14T15:48:12.085086Z         9 Query     SET autocommit=1    
>2018-01-14T15:48:13.952199Z         8 Query     insert into transfer (ammount, from_id, to_id, type) values (10, 1, 2, null)    
>2018-01-14T15:48:13.954992Z         8 Query     update account set balance=90.00, version=1 where id=1 and version=0    
>2018-01-14T15:48:13.956614Z         8 Query     rollback    
>2018-01-14T15:48:13.957371Z         8 Query     SET autocommit=1    

How to repeat:
Follow commands from the log above and create two simple tables: account and transfer. For details see:

https://stackoverflow.com/questions/48249117/insert-not-rollbacked-in-transactional-method...

I haven't tried it directly from mysql console. 

Suggested fix:
Insert should be rollbacked

I think it is actually quite serious

Hello Martin Tlachac,

Thank you for the bug report.
Could you please provide repeatable test case (exact steps, C/Java version, full stack trace, sample code etc. - please make it as private if you prefer) to confirm this issue at our end?

Thanks,
Chiranjeevi.

https://github.com/tlachy/BugDb89334

see readme

Hi Martin,

Thanks for your interest in MySQL and Connector/J.

The rollback statement isn't doing what you are expecting. If you run the same directly in a mysql client you'll see this:

mysql> rollback;
Query OK, 0 rows affected, 1 warning (0,00 sec)

mysql> show warnings;
+---------+------+---------------------------------------------------------------+
| Level   | Code | Message                                                       |
+---------+------+---------------------------------------------------------------+
| Warning | 1196 | Some non-transactional changed tables couldn't be rolled back |
+---------+------+---------------------------------------------------------------+

This is so because you are using MyISAM tables, which don't support transactions. Take for example the create statement for the account table:

2018-02-07T15:33:22.812452Z         4 Query     create table account (
       id bigint not null auto_increment,
        balance decimal(19,2),
        version bigint not null,
        primary key (id)
    ) engine=MyISAM

You should make amends in your application in order to create InnoDB tables instead. I can't help you here, though.

For now I'm closing this report as not a bug. If you still see some unexpected behavior after fixing your application, then please reopen it or file a new one. Thank you.

I hope this helps.