a %i!@sdZddlmZddlmZmZddlZddlZddlm Z ddl Z ddl m Z e j dee ejjzddlmZWneydd ZYn0edeejjeed d d Zdeejjd ddZeeedddZeedddZdS)a Database transaction management with row-level locking support. Provides context manager for safe database transactions with: - Automatic commit on success - Automatic rollback on error - Row-level locking (FOR UPDATE) - Proper connection management - Nested transaction handling Usage: from database import transaction # Simple transaction with transaction() as (conn, cursor): cursor.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1") cursor.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2") # Auto-commits on success, auto-rolls back on exception # Transaction with row-level locking (use FOR UPDATE in queries) with transaction() as (conn, cursor): cursor.execute("SELECT balance FROM accounts WHERE id = 1 FOR UPDATE") balance = cursor.fetchone()[0] if balance >= 100: cursor.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1") )contextmanager)TupleOptionalN)Error)Path)get_database_connectioncCs,ddlm}tjj|j|j|j|j|jdS)zFallback connection getterrconfig)hostportuserpassworddatabase) r mysql connectorconnectDB_HOSTDB_PORTDB_USER DB_PASSWORDDB_NAMErr6/var/www/lichun.app/lichun/ws/database/transactions.pyr+s r)connisolation_levelccs,|du}d}z|zp|rt}d|_|}|rdgd}||vrPtd||d||||fV|Wnlty}zT|rz | Wn4ty}zt d|WYd}~n d}~00WYd}~n d}~00W|r:z | Wn6ty8}zt d|WYd}~n d}~00|r(|r(z | Wn6ty}zt d|WYd}~n d}~00n|rz | Wn6ty}zt d|WYd}~n d}~00|r&|r&z | Wn6ty$}zt d|WYd}~n d}~000dS) a, Context manager for database transactions with automatic commit/rollback. Args: conn: Optional existing connection. If None, gets from pool. isolation_level: Optional transaction isolation level ('READ UNCOMMITTED', 'READ COMMITTED', 'REPEATABLE READ', 'SERIALIZABLE') Yields: Tuple of (connection, cursor) Raises: Exception: Any exception from transaction operations Note: For row-level locking, use FOR UPDATE in your SELECT queries: cursor.execute("SELECT * FROM table WHERE id = %s FOR UPDATE", (id,)) Example: # Simple transaction with transaction() as (conn, cursor): cursor.execute("INSERT INTO users (name) VALUES (%s)", ('Alice',)) cursor.execute("INSERT INTO profiles (user_id) VALUES (LAST_INSERT_ID())") # Transaction with row-level locking (use FOR UPDATE in queries) with transaction() as (conn, cursor): cursor.execute("SELECT balance FROM accounts WHERE id = %s FOR UPDATE", (1,)) balance = cursor.fetchone()[0] if balance >= 100: cursor.execute("UPDATE accounts SET balance = balance - 100 WHERE id = %s", (1,)) # Transaction with custom isolation level with transaction(isolation_level='SERIALIZABLE') as (conn, cursor): cursor.execute("SELECT COUNT(*) FROM orders") count = cursor.fetchone()[0] cursor.execute("INSERT INTO orders (order_num) VALUES (%s)", (count + 1,)) NF)zREAD UNCOMMITTEDzREAD COMMITTEDzREPEATABLE READ SERIALIZABLEzInvalid isolation level: z SET TRANSACTION ISOLATION LEVEL zError during rollback: zError closing cursor: zError closing connection: ) r autocommitcursorupper ValueErrorexecutestart_transactioncommit Exceptionrollbackloggingerrorclose)rrZcreated_connectionrZ valid_levelseZrollback_errorrrr transaction7sT*    & &  ( &  r)rcCs<t|d\}}|||WdS1s.0YdS)aO Execute a function within a transaction context. Helper function for wrapping operations in a transaction. Args: func: Function that takes (conn, cursor) as arguments conn: Optional existing connection Returns: Return value of func Example: def transfer_money(conn, cursor): cursor.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1") cursor.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2") return True result = execute_in_transaction(transfer_money) r*N)r))funcrrrrrexecute_in_transactionsr,)from_account_id to_account_idamountcCst\}}|d|f|}|r2|d|kr:td|d|f|sXtd|d||f|d||f|d|||fWd n1s0Yd S) z Example: Atomic money transfer between accounts. This example demonstrates: - Row-level locking with FOR UPDATE - Balance validation before update - Atomic operations across multiple rows z5SELECT balance FROM accounts WHERE id = %s FOR UPDATErzInsufficient fundsz0SELECT id FROM accounts WHERE id = %s FOR UPDATEzDestination account not foundz8UPDATE accounts SET balance = balance - %s WHERE id = %sz8UPDATE accounts SET balance = balance + %s WHERE id = %szsINSERT INTO transaction_log (from_account, to_account, amount, timestamp) VALUES (%s, %s, %s, NOW())N)r)r fetchoner)r-r.r/rrZ from_balancerrrexample_atomic_transfers4 r1) operation_iduser_idcCsrtX\}}|d|f|r2WddS|d|f|d|fWddS1sd0YdS)z Example: Idempotent operation using database lock. Prevents duplicate execution of operations by checking a processed operations log with row-level locking. zFSELECT id FROM processed_operations WHERE operation_id = %s FOR UPDATENFziINSERT INTO user_credits (user_id, amount) VALUES (%s, 100) ON DUPLICATE KEY UPDATE amount = amount + 100zPINSERT INTO processed_operations (operation_id, processed_at) VALUES (%s, NOW())T)r)r r0)r2r3rrrrrexample_idempotent_operations  r4)NN)N)__doc__ contextlibrtypingrrr%mysql.connectorrrsyspathlibrpathinsertstr__file__parent functionsr ImportErrorrMySQLConnectionr)r,intfloatr1r4rrrrs*      c.