Does mysql select need to submit a commit?

Look at your needs!

---

MySQL's innodb supports multiple transaction isolation levels:

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE

The default isolation level is REPEATABLE READ

The default isolation level of pgsql is READ COMMITTED ; the default of sql server is READ UNCOMMITTED ; the default of oracle is Read Committed

We use mysql's default transaction isolation level and turn off auto-commit transactions: SET AUTOCOMMIT = 0 . It just so happens that you don't use commit after you use select. Then you will only be able to read duplicate data in your life.

For example the following table:

connect 1, read row with id = 1
Connect 2, modify the name of the row with id=1 to pon

At this time, connect 1 and read the row with id=1. What do you think the name is? Is it 1 or pon?

The answer is 1, not pon

it's scary

How can I read pon instead of 1?

• Option 1: For connection 1, after select, specify operations such as commit, rollback, begin, etc. to open a new transaction
• Option 2: Adjust the transaction isolation level to READ COMMITTED

 import pymysql
import pymysql.cursors
from pymysql.connections import Connection
from loguru import logger
# Connect to the database


connection_1: Connection = pymysql.connect(host='192.168.31.245',
                                           user='root',
                                           password='xxxx',
                                           port=3306,
                                           database='seckill',
                                           cursorclass=pymysql.cursors.DictCursor)
cursor_1 = connection_1.cursor()


connection_2: Connection = pymysql.connect(host='192.168.31.245',
                                           user='root',
                                           password='xxxx',
                                           port=3306,
                                           database='seckill',
                                           cursorclass=pymysql.cursors.DictCursor)
cursor_2 = connection_2.cursor()

sql = """
SELECT * FROM account WHERE id=1;
""".strip()
cursor_1.execute(sql)
logger.debug(cursor_1.fetchall())
connection_1.commit()

# sql = """
# DELETE FROM account where name='jike';
# """.strip()
sql = """
UPDATE account SET name="pon" WHERE id=1;
""".strip()
cursor_2.execute(sql)
connection_2.commit()


sql = """
SELECT * FROM account WHERE id=1;
""".strip()
cursor_1.execute(sql)
logger.debug(cursor_1.fetchall())

---

Adjust the transaction isolation level, there are two scopes of SESSION and GLOBAL

 SET {SESSION | GLOBAL} TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE};

Reference: It is often necessary to confirm some information of mysql in development