mysql python concurrent access on same table column

Question

I've explored a few solutions without much success. I have two python processes (created with Popen from subprocess) and one mysql table (called persone) containing one row with two columns: Age:0, id:1. One process selects that row, take it's age and increments it by one. It does that 1000 times. The second does the same thing but decrements it instead. I run each process in parallel.

Theoretically, I would like that at the end, the age remains at zero. The thing is I keep getting random values between 100 and -100 at the end of mymain.py which I guess means there are accesses that are done at the same time, corrupting the database. I was wondering what I was missing?

Here is the code I use to test this:

ajoutAge.py

import MySQLdb as lite

num=1000
connexion = lite.connect(host="localhost", user="root", passwd="123", db="test")
with connexion:
    for i in range(num):
       cur = connexion.cursor()
       cur.execute('start transaction')
       cur.execute('select Age from persone where id=1')
       age = cur.fetchone()[0]
       cur.execute('update persone set Age='+str(age+1)+' where id=1')
       # cur.execute('rollback')
       cur.execute('commit')
print "ajout Done"

retraitAge.py

import MySQLdb as lite

num=1000
connexion = lite.connect(host="localhost", user="root", passwd="123", db="test")
with connexion:
    for i in range(num):
        cur = connexion.cursor()
        cur.execute('start transaction')
        cur.execute('select Age from persone where id=1')
        age = cur.fetchone()[0]
        cur.execute('update persone set Age='+str(age-1)+' where id=1')
        cur.execute('commit')
print "retrait Done"

mymain.py

from subprocess import Popen

a=Popen("python ajoutAge.py", shell=True)
aa=Popen("python ajoutAge.py", shell=True)
b=Popen("python retraitAge.py", shell=True)
bb=Popen("python retraitAge.py", shell=True)

a.communicate()
aa.communicate()
b.communicate()
bb.communicate()
print "Main done"

My table is using InnoDB as the storage engine.

Bill Karwin · Accepted Answer · 2013-03-12 00:44:04Z

4

You're creating a race condition.

Every time you SELECT the current age, there is a split-second between that and your UPDATE. In that split-second, the other process could be (and apparently sometimes is) updating the value.

So in the first process, when you UPDATE the value to age+1, it's incrementing based on a slightly outdated value.

To fix this, you have a couple of options:

SELECT ... FOR UPDATE, which locks the row, preventing the other process from modifying it before you finish your transaction. You need to make sure you don't commit your SELECT transaction, which would release the lock, allowing another race condition.
UPDATE persone SET age = age+1 WHERE id=1 (or age=age-1 of course). In other words read the value in the same expression where you change the value, so it's atomic and no concurrent process can "sneak in" and change it in between.

answered Mar 12, 2013 at 0:44

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Sam Over a year ago

wow, well I would have liked to learn that at school. thanks! I guess I still need transactions for the first option, but not for the second one, am I right?

Bill Karwin Over a year ago

Right, for the first option you need to ensure that the SELECT and the UPDATE run in the same transaction, so it holds the lock until you're done with the row.

Bill Karwin Over a year ago

PS: If you got a Comp Sci degree without being exposed to the concept of a race condition, then you got ripped off.

Sam Over a year ago

Oh don't worry, I knew about the race condition! I simply didn't know how to handle it with Mysql ;) thanks again!

Collectives™ on Stack Overflow

mysql python concurrent access on same table column

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related