transaction incoherence

[Alain Spineux]

Hello

I'm trying to write some testing programs to test the cluster I will setup later.

I wrote 2 scripts, one make some changes to the data, and the other check the coherence of them.
The first one distribute a stock of item between shops. The number of item ( SUM(count) ) must stay always the same because I give to new shops what I removed from oler one.
The second one compare the initiale number of item with the sum of all distributed again shop.

After some iteration the second one find a difference.

I tried different way to start and commit a transaction without success.

I am using Centos-4.1

mysql-4.1.12-3.RHEL4.1
mysql-server-4.1.12-3.RHEL4.1
MySQL-python-1.0.0-1.RHEL4.1

I tried also MySQL-python-1.2.0 without success

Any idea

My scripts follows.

Any idea
-- initdb --

CREATE DATABASE ha;
USE ha;

CREATE TABLE shop (id SERIAL, now DATETIME, num INT, count INT );

-- ha_common.py --

!/usr/bin/python2

db_host, db_user, db_passwd, db_name= 'localhost', 'root', '', 'ha'

create_delay=0.05
check_delay=10.0
collect_delay=20.0

stock_init=5000

-- ha_create.py --

!/usr/bin/python2

import sys, os, time
import MySQLdb

from ha_common import *

db=MySQLdb.connect(host = db_host, user=db_user, passwd=db_passwd, db=db_name)
c=db.cursor()
c.execute('SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE')
print 'db._transactional:', db._transactional
try:
db.autocommit(0)
except:
c.execute('SET AUTOCOMMIT = 0')

c.execute('SELECT count(*) FROM shop')
print c.fetchall()

c.execute('DELETE FROM shop')
c.execute('SELECT count(*) FROM shop')
print c.fetchall()
db.commit()

num=0
delay=0
now=time.time()
while True:
if num==0:
c.execute('insert into shop (now, num, count) value ( "%s", %d, %d )' % (time.strftime('%Y-%m-%d %H:%M:%S'), num, stock_init))
db.commit()
else:
# start a transaction
#c.execute('START TRANSACTION')
# find a shop having stock
c.execute('SELECT id, count FROM shop ORDER BY count DESC')
id, count=c.fetchone()
c.fetchall()

    if count<2: raise 'Stock is going low !!!'
    count1=count/2
    count2=count-count1

    # update current 
    c.execute('UPDATE shop SET count=%s WHERE id=%s', (count1, id))
    # insert new record
    print num, count2, delay
    c.execute('INSERT INTO shop (now, num, count) VALUE ( %s, %s, %s )', (time.strftime('%Y-%m-%d %H:%M:%S'), num, count2))
    db.commit()

delay=(now+create_delay)-time.time()
if delay>0:
    time.sleep(delay)
    now+=create_delay

num+=1

c.close()

-- ha_check.py --

!/usr/bin/python2

import sys, os, time
import MySQLdb

from ha_common import *

db=MySQLdb.connect(host = db_host, user=db_user, passwd=db_passwd, db=db_name)
c=db.cursor()
try:
db.autocommit(0)
except:
c.execute('SET AUTOCOMMIT = 0')

c.execute('SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE')
c.execute('SELECT count(*) FROM shop')
print 'Number of shop', c.fetchall()[0][0]

now=time.time()
i=1;
while True:
# check sum(count)==stock_init
c.execute('SELECT SUM(count) FROM shop')
total=c.fetchall()[0][0]

if total!=stock_init:
    raise 'stock %d <> %d' % (total, stock_init)

if i%10==0:
    print 'test=%d\r' % (i,)
i+=1

c.close()

[Andy Dustman]

I recommend using MySQLdb-1.2.0, or 1.2.1rc3, over 1.0.0.

In 1.2.0, autocommit is turned off by default. I guess your code must be so that it will work with 1.0 and 1.2, since 1.0 doesn't have an autocommit() method.

What storage engine are you using for your tables? The default MyISAM tables do not support transactions yet (may in MySQL-5.1 or a later version; it's on their TODO list). Your only real options right now are ENGINE=INNODB or BDB.

The only other idea I have right now is to execute db.rollback() in the loop of your ha_check.py to ensure you start a new transaction each time.

It may seem a little odd, but you can also do stuff like this:

c.execute('SELECT SUM(count) FROM shop')
for total, in c: pass

i.e. use the cursor c as an iterator.

[Alain Spineux]

YES !

Of course I was using the default storage engine. I was thinking it was INNODB.
I dropped the tables and created new INNODB tables and it worked.
The start of my check script stopped my create script, their was no commit in my check script ! This was the proof !

Thanks

Now I am writing the "collector" that will migrate stock (count) from old record/shop to new one.

Best regards.

[Andy Dustman]

You can also convert existing MyISAM tables to InnoDB with ALTER TABLE tablename ENGINE=INNODB (check the ALTER TABLE syntax in the docs)

[Alain Spineux]

thanks