MySQL Connector/Python: How to use the start_transaction() method?

Question

I am trying to access some records using MySQL Connector/Python but I am unable to understand how start_transaction() is used.

I would be great if someone could shed some light on uses cases where it is required.

Update: After reading the docs carefully it looks like, the start_transaction() method is only required when autocommit=True. Is that true? I am still confused over this and documentation doesn't provide any examples.

An

Answer 1

Yes, you normally use the connection.start_transaction() method to start a transaction when the autocommit feature is enabled.

Since MySQL Connector/Python is Python DB API-compliant, autocommit is disabled by default and the first SQL statement will implicitly begin a transaction. You must call connection.commit to commit the transaction.

Whitten

This means that start_transaction() is only useful when autocommit=True. Am I right? or Can I use it when autocommit=False? – An

You can use it with autocommit=False as well if you want to be more explicit. It may also be useful when you need to change the default transaction options. – Whitten

Answer 2

In MySQL, there are 3 ways of doing transactions:

1. Explicit. Preferred with multiple statements are used. (Note: the value of autocommitMul is not relevant.)

   START TRANSACTION;
   SQL statement(s)
   COMMIT;
   

2. Multiple statements in a single transaction. This is error-prone since you can forget to issue the COMMIT:

   ((autocommit is OFF))
   SQL statement(s)
   COMMIT;
   

3. Each statement is its own transaction. (Useful for casual usage where ACID is not needed.)

   ((autocommit is ON))
   SQL statement
   SQL statement
   SQL statement
   ((no COMMIT))       
   

I don't know the mapping of these to python, but it seems to disagree with the Answer by Eugene and Comment by Cody.

Spokesman

Hi there is a MySQLConnection.in_transaction property to indicate whether a transaction is active for the connection, dev.mysql.com/doc/connector-python/en/…. I have tested it to confirm it is in a transaction. – Sac

I updated my code github.com/qiulang/mysql/blob/main/crash_test.py to verify that we are in transaction when the first sql run and not in transaction after commit, but in transaction again when the next sql run. – Sac

Answer 3

1. Just to read data from database we don't require to set transactions (default transaction is REPEATABLE READ)

2. transaction are used when any write commands(update , insert and delete) and read command are used simultaneously.

3. MySQL connection autocommit default = False explicitly we need to commit or rollback a transaction.

When it comes to use of transaction they are four types of Isolation level:

READ UNCOMMITTED','READ COMMITTED','REPEATABLE READ', and 'SERIALIZABLE'

reference to transactions

Greatnephew