How database Transaction work with application code

Question

Suppose we have following pseudo python code to deal with Mysql:

with connection.cursor() as cursor:
    sql = "SELECT `count` FROM `sometable` WHERE `name`=%s"
    cursor.execute(sql, ('somename',))
    result = cursor.fetchone()
    if result['count'] == 1:
        sql = "UPDATE `sometable` SET `count`=%d WHERE `name`=%s"
        cursor.execute(sql, (10, 'somename'))

connection.commit()

I know transaction will provide an "all-or-nothing" proposition, but don't know how it works if mixed with application code. How can database transaction deal with appliction code like: if result['count'] == 1.

In my understanding, when committed, all sqls will be handled to Database Server to execute, and the server will make sure sqls execute "all-or-nothing". But the code above should only execute the UPDATE sql when count equals 1. What would happen if all the sqls have already handled to the database server and some other transaction just change the count?

I suspect that you are really asking if transaction prevent race conditions. See [Do database transactions prevent race conditions?](http://stackoverflow.com/q/6477574) if that was your real question. — Martijn Pieters, Sep 01 '15 at 13:18
@MartijnPieters, you are right, the link you gave me just explained. — realli, Sep 01 '15 at 13:32

score 4 · Accepted Answer · edited May 23 '17 at 12:06

The transaction includes any changes made to the database. If application code tests prevent changes from being made, they are not part of the transaction. So if result['count'] is not equal to 1 no changes have been made, because you never sent the UPDATE command to the database.

See transactions as a 'todo list' you hand to the database, and commit tells the database to go do that list. Your if test simply controls wether or not you write down the update on that list. The database doesn't have to care how you wrote the list.

If you were looking for ways to prevent the UPDATE from taking place if another transaction also updated the database in parallel, then you'll need to eiher look into locking strategies (creating an exclusive lock to force whole transactions taking place in a series) or to detect that the row has been changed during the transaction with optimistic locking. See Do database transactions prevent race conditions?.

For your specific case the latter would look like this:

with connection.cursor() as cursor:
    sql = "SELECT `count` FROM `sometable` WHERE `name`=%s"
    cursor.execute(sql, ('somename',))
    result = cursor.fetchone()
    if result['count'] == 1:
        sql = "UPDATE `sometable` SET `count`=%d WHERE `name`=%s and `count`=1"
        cursor.execute(sql, (10, 'somename'))
        if cursor.rowcount != 1:
            # another transaction updated the row already. Handle this gracefully.
            # you could elect to roll back here.

The rowcount after the UPDATE would be 0 if the row with the matching name column has already been altered to have a different count value; we were selecting for count=1, after all.

@realli: to what end? Even if you moved this to a database stored procedure you wouldn't be protected from another transaction colliding. — Martijn Pieters, Sep 01 '15 at 13:23

How database Transaction work with application code

1 Answers1