Master MySQL Transactions, Locks, and Connection Pools with Practical Python Examples

Transaction

InnoDB engine supports transactions, while MyISAM does not.

<code>CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` varchar(32) DEFAULT NULL,
  `amount` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;</code>

Example: Li Jie transfers 100 to Wu Peiqi, which involves two steps – debit Li Jie's account and credit Wu Peiqi's account. Both steps must succeed together or be rolled back, which is the purpose of a transaction: all succeed or all fail.

Transactions have four ACID properties:

Atomicity

<code>Atomicity means all operations in a transaction are indivisible; they either all succeed or all roll back.</code>

Consistency

<code>Consistency ensures data integrity before and after the transaction.</code>

Isolation

<code>Isolation means a transaction should not be affected by other concurrent transactions.</code>

Durability

<code>Durability guarantees that once a transaction commits, its changes persist in the database.</code>

MySQL client

<code>mysql> select * from users;
+----+---------+---------+
| id | name    | amount |
+----+---------+---------+
| 1  | wupeiqi | 5       |
| 2  | alex    | 6       |
+----+---------+---------+
3 rows in set (0.00 sec)

mysql> begin;  -- start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update users set amount=amount-2 where id=1;  -- execute operation
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update users set amount=amount+2 where id=2;  -- execute operation
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;  -- commit transaction
Query OK, 0 rows affected (0.00 sec)

mysql> select * from users;
+----+---------+---------+
| id | name    | amount |
+----+---------+---------+
| 1  | wupeiqi | 3       |
| 2  | ale x   | 8       |
+----+---------+---------+
3 rows in set (0.00 sec)</code>

Python code

<code>import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset='utf8', db='userdb')
cursor = conn.cursor()

# start transaction
conn.begin()

try:
    cursor.execute("update users set amount=1 where id=1")
    int('asdf')
    cursor.execute("update tran set amount=2 where id=2")
except Exception as e:
    print("rollback")
    conn.rollback()
else:
    print("commit")
    conn.commit()

cursor.close()
conn.close()</code>

Locks

When many updates, inserts, or deletes happen simultaneously, MySQL uses locks to ensure data consistency.

Table‑level lock: while one session operates on a table, others must wait.

Row‑level lock: only the rows being modified are locked; other rows remain accessible.

<code>MYISAM supports table lock only; InnoDB supports both row and table locks.
-- In MYISAM any lock is a table lock.
-- In InnoDB, index‑based queries acquire row locks, otherwise table locks.</code>

Therefore we usually choose InnoDB and create indexes for efficient row‑level locking.

<code>CREATE TABLE `L1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `count` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;</code>

In InnoDB, update/insert/delete automatically acquire exclusive locks before execution and release them afterward. SELECT statements do not acquire locks by default.

To make SELECT acquire a lock, combine it with a transaction and special syntax.

for update

– exclusive lock; other sessions cannot read or write the locked rows.

<code>begin;
select * from L1 where name="武沛齐" for update;  -- name column not indexed → table lock
commit;</code>

<code>begin;
select * from L1 where id=1 for update;  -- id column indexed → row lock
commit;</code>

lock in share mode

– shared lock; other sessions can read but not write.

<code>begin;
select * from L1 where name="武沛齐" lock in share mode;  -- name not indexed → table lock
commit;</code>

<code>begin;
select * from L1 where id=1 lock in share mode;  -- id indexed → row lock
commit;</code>

Exclusive lock

Use

for update

to ensure that concurrent transactions cannot read or write the locked rows. Example: when selling a limited‑stock item, an exclusive lock prevents overselling.

<code>A: view product, remaining 100
B: view product, remaining 100

Both place an order simultaneously:
update goods set count=count-1 where id=3;
-- InnoDB locks serialize the updates.

When only 1 item remains, without a lock both could decrement to -1.
Solution: use a transaction with a SELECT ... FOR UPDATE to check stock before updating.

begin;
select count from goods where id=3 for update;
if count > 0:
    update goods set count=count-1 where id=3;
else:
    -- out of stock
commit;</code>

Shared lock

Shared locks allow reading but block writes, useful for maintaining referential integrity.

<code>SELECT * FROM parent WHERE name='Jones' LOCK IN SHARE MODE;</code>

After acquiring the shared lock, you can safely insert a child row because any transaction that tries to obtain an exclusive lock on the parent row will wait until the shared lock is released.

Database connection pool

Using a connection pool avoids the overhead of creating a new connection for each request.

<code>pip3 install pymysql
pip3 install dbutils</code>

<code>import pymysql
from dbutils.pooled_db import PooledDB

MYSQL_DB_POOL = PooledDB(
    creator=pymysql,
    maxconnections=5,
    mincached=2,
    maxcached=3,
    blocking=True,
    setsession=[],
    ping=0,
    host='127.0.0.1',
    port=3306,
    user='root',
    password='root123',
    database='userdb',
    charset='utf8'
)

def task():
    conn = MYSQL_DB_POOL.connection()
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    cursor.execute('select sleep(2)')
    result = cursor.fetchall()
    print(result)
    cursor.close()
    conn.close()

def run():
    for i in range(10):
        t = threading.Thread(target=task)
        t.start()

if __name__ == '__main__':
    run()</code>

SQL utility class

A reusable helper class simplifies database operations and reduces code duplication.

Singleton and methods

<code># db.py
import pymysql
from dbutils.pooled_db import PooledDB

class DBHelper(object):
    def __init__(self):
        self.pool = PooledDB(
            creator=pymysql,
            maxconnections=5,
            mincached=2,
            maxcached=3,
            blocking=True,
            setsession=[],
            ping=0,
            host='127.0.0.1',
            port=3306,
            user='root',
            password='root123',
            database='userdb',
            charset='utf8'
        )

    def get_conn_cursor(self):
        conn = self.pool.connection()
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        return conn, cursor

    def close_conn_cursor(self, *args):
        for item in args:
            item.close()

    def exec(self, sql, **kwargs):
        conn, cursor = self.get_conn_cursor()
        cursor.execute(sql, kwargs)
        conn.commit()
        self.close_conn_cursor(conn, cursor)

    def fetch_one(self, sql, **kwargs):
        conn, cursor = self.get_conn_cursor()
        cursor.execute(sql, kwargs)
        result = cursor.fetchone()
        self.close_conn_cursor(conn, cursor)
        return result

    def fetch_all(self, sql, **kwargs):
        conn, cursor = self.get_conn_cursor()
        cursor.execute(sql, kwargs)
        result = cursor.fetchall()
        self.close_conn_cursor(conn, cursor)
        return result

db = DBHelper()</code>

<code>from db import db

# insert
db.exec("insert into d1(name) values(%(name)s)", name="武沛齐666")

# fetch one
ret = db.fetch_one("select * from d1")
print(ret)

# fetch with condition
ret = db.fetch_one("select * from d1 where id=%(nid)s", nid=3)
print(ret)

# fetch all
ret = db.fetch_all("select * from d1")
print(ret)

# fetch with greater‑than condition
ret = db.fetch_all("select * from d1 where id>%(nid)s", nid=2)
print(ret)</code>

Context manager

Adding support for the

with

statement ensures connections are automatically returned to the pool.

<code># db_context.py
import threading
import pymysql
from dbutils.pooled_db import PooledDB

POOL = PooledDB(
    creator=pymysql,
    maxconnections=5,
    mincached=2,
    maxcached=3,
    blocking=True,
    setsession=[],
    ping=0,
    host='127.0.0.1',
    port=3306,
    user='root',
    password='root123',
    database='userdb',
    charset='utf8'
)

class Connect(object):
    def __init__(self):
        self.conn = POOL.connection()
        self.cursor = self.conn.cursor(pymysql.cursors.DictCursor)

    def __enter__(self):
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        self.cursor.close()
        self.conn.close()

    def exec(self, sql, **kwargs):
        self.cursor.execute(sql, kwargs)
        self.conn.commit()

    def fetch_one(self, sql, **kwargs):
        self.cursor.execute(sql, kwargs)
        return self.cursor.fetchone()

    def fetch_all(self, sql, **kwargs):
        self.cursor.execute(sql, kwargs)
        return self.cursor.fetchall()
</code>

<code>from db_context import Connect

with Connect() as obj:
    ret = obj.fetch_one("select * from d1")
    print(ret)
    ret = obj.fetch_one("select * from d1 where id=%(id)s", id=3)
    print(ret)
</code>

Summary

This section covers essential skills frequently used in development: transactions to ensure atomic batch operations, locks to handle concurrency, connection pools for efficient multi‑user access, and a reusable SQL helper class to avoid repetitive code.