Issue: About transaction commit 
Author: xiaoqge
State: closed
Created: 2020-11-20 13:27:05

I am using mysql 8.0, using the innodb engine, using the version of peewee 3.14, I use peewee transaction commit, without executing commit, I saw the data has been written into the database, but I did not execute db.commit() , I manually execute commands in mysql. If I don’t execute commit, I won’t be able to write. However, there will be inconsistencies when using peewee transaction commit. What is wrong with my code?

```
                    db =db = MySQLDatabase("address", host="127.0.0.1", port=3306, user="root", passwd="xxx",autocommit=False,autorollback=True)
                    with db.manual_commit():
                        db.begin()
                        Spead.create(dis="test",number="test",value=333)
```

>I saw the data has been written into the database

Assuming you mean you were using a separate connection and saw the data before it had been committed by the peewee connection? You might check your read [isolation settings](https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html).

When I run the following script I get the expected output -- the 2nd connection does not see the uncommitted row until I call commit:

```python
from peewee import *

db = MySQLDatabase('peewee_test')
db2 = MySQLDatabase('peewee_test')  # 2nd connection

class Reg(Model):
    key = TextField()
    class Meta:
        database = db

class Reg2(Reg):  # model class for accessing table using 2nd conn
    class Meta:
        database = db2
        table_name = 'reg'


db.create_tables([Reg])

with db.manual_commit() as tx:
    db.begin()
    Reg.create(key='k1')  # create a row using first conn

    db2.connect()  # query table using 2nd conn
    print('is "k1" visible to conn2 BEFORE commit?')
    print('rows in "reg" table: %s' % Reg2.select().count())

    db.commit()
    print('is "k1" visible to conn2 AFTER commit?')
    print('rows in "reg" table: %s' % Reg2.select().count())

    db2.close()

db.drop_tables([Reg])
```

Output:

```
is "k1" visible to conn2 BEFORE commit?                                                                              
rows in "reg" table: 0                                                                                               
is "k1" visible to conn2 AFTER commit?                                                                               
rows in "reg" table: 1
```

@coleifer Thank you very much for your reply. I am sure it is not an isolated problem. After a script has been executed, I entered select * from spead on the mysql command line and found that the data has indeed been written. In order to avoid my MYSQL problem, I tested it with the command line in mysql, and this problem did not appear，Did you execute it on mysql 8.0?

I will execute these commands in mysql without any problems, but the peewee script will be written into the database without executing the commit.

```
root@ubuntu-PowerEdge-R740:~# mysql -h 127.0.0.1 -uroot -pxxxxx
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 94
Server version: 8.0.22 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use address;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into spead values(1,'test1','11','11');
Query OK, 1 row affected (0.00 sec)

mysql> select * from spead;
+----+-------+--------+-------+
| id | dis   | number | value |
+----+-------+--------+-------+
|  1 | test1 |     11 |    11 |
+----+-------+--------+-------+
1 row in set (0.00 sec)

mysql> exit;
Bye
root@ubuntu-PowerEdge-R740:~# mysql -h 127.0.0.1 -uroot -pxxxx
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 95
Server version: 8.0.22 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use address;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from spead;
Empty set (0.00 sec)

mysql> 
```

My script is just the following lines

```
from peewee import *
db = MySQLDatabase("address", host="127.0.0.1", port=3306, user="root", passwd="xxxx",autocommit=False,autorollback=True)
class BaseModel(Model):
    class Meta:
        database = db

class Spead(BaseModel):
    dis = CharField(unique=True)
    number = IntegerField(null=True)
    value = IntegerField(null=True)



```


I'm using mariadb 10.4.x. The script I shared clearly shows that the rows are not visible to the 2nd connection before they have been committed. You might try running it and seeing if you get the same output as I do.

Thanks, my code problem, sorry