def test_lost_connection_during_session(self):
k = 'test_rollback'
mydb1 = BaseDB(myconfig)
mydb2 = BaseDB(myconfig)
autoid, rows = mydb1.insert("INSERT INTO test1(k,v) VALUES('%s', 1);" % escape(k))
assert_equal(autoid, 1, "autoid")
assert_equal(rows, 1, "rows")
tid1, = mydb1.query_one("SELECT CONNECTION_ID();")
try:
with mydb1.session() as conn:
rows = conn.execute("UPDATE test1 SET v=2 WHERE k='%s'" % escape(k))
assert_equal(rows, 1, "rows")
# create lost connection exception
mydb2.execute("KILL %d" % int(tid1))
# this statement will raise exception
conn.execute("UPDATE test1 SET v=3 WHERE k='%s'" % escape(k))
except OperationalError as e:
if not is_lost_connection_exception(e):
raise
else:
assert False, "Must get an exception."
# nothing is committed
v, = mydb1.query_one("SELECT v FROM test1 WHERE k='%s'" % escape(k))
assert_equal(v, '1')
v, = mydb2.query_one("SELECT v FROM test1 WHERE k='%s'" % escape(k))
assert_equal(v, '1')
def work():
mydb = BaseDB(myconfig)
for i in xrange(LOOP):
with mydb.session() as conn:
# MUST use `FOR UPDATE` to block reading
_, v = conn.query_one("SELECT autoid,v FROM test1 WHERE k='%s' FOR UPDATE;" % escape(myk))
rows2 = conn.execute("UPDATE test1 SET v=%d WHERE k='%s'" % (int(v)+1, escape(myk)))
assert rows2 == 1, (rows2, 1)
def test_basic(self):
mydb = BaseDB(myconfig)
mydb2 = BaseDB(myconfig)
aid, rows = mydb.insert("INSERT INTO test1(k,v) VALUES('%s','%s')" % ("aaa", "AAA"))
assert_equal(aid, 1, 'autoid')
assert rows == 1, "insert 1 row"
rows = mydb.execute("UPDATE test1 SET v='%s' WHERE k='%s'" % ('BBB', 'aaa'))
assert rows == 1, "update 1 row"
# Read Committed
values = mydb2.query("SELECT autoid, k, v FROM test1 LIMIT 10;")
assert len(values) == 1, "only 1 record in table"
aid, k, v = values[0]
assert aid == '1' and k == 'aaa' and v == 'BBB', ("result must be commit: %s" % values)
# Cannot Read uncommitted
with mydb.session() as conn:
values = conn.query("SELECT autoid, k, v FROM test1 WHERE k='aaa'")
assert len(values) == 1, "only 1 record in table"
aid, k, v = values[0]
assert aid == '1' and k == 'aaa' and v == 'BBB', "result must be commit"
rows = conn.execute("UPDATE test1 SET v='%s' WHERE k='%s'" % ('CCC', 'aaa'))
assert rows == 1
# read uncommitted
values = mydb2.query("SELECT autoid, k, v FROM test1 LIMIT 10;")
assert len(values) == 1, "only 1 record in table"
aid, k, v = values[0]
assert aid == '1' and k == 'aaa' and v == 'BBB', ("result is not commit: %s" % values)
values = mydb2.query("SELECT autoid, k, v FROM test1 LIMIT 10;")
assert len(values) == 1, "only 1 record in table"
aid, k, v = values[0]
assert aid == '1' and k == 'aaa' and v == 'CCC', "result is committed"
def test_rollback(self):
k = 'test_rollback'
mydb1 = BaseDB(myconfig)
mydb2 = BaseDB(myconfig)
autoid, rows = mydb1.insert("INSERT INTO test1(k,v) VALUES('%s', 1);" % escape(k))
assert_equal(autoid, 1, "autoid")
assert_equal(rows, 1, "rows")
try:
with mydb1.session() as conn:
rows = conn.execute("UPDATE test1 SET v=2 WHERE k='%s'" % escape(k))
assert_equal(rows, 1, "rows")
raise Exception("I want a rollback")
except Exception as e:
if str(e) != "I want a rollback":
raise
else:
assert False, "Must get an exception."
v, = mydb1.query_one("SELECT v FROM test1 WHERE k='%s'" % escape(k))
assert_equal(v, '1')
v, = mydb2.query_one("SELECT v FROM test1 WHERE k='%s'" % escape(k))
assert_equal(v, '1')
