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 test_thread_transaction(self):
myk = 'test_multi_thread_inserttion'
autoid, rows = self.mydb.insert("INSERT INTO test1(k,v) VALUES('%s', 1);" % escape(myk))
assert_equal(autoid, 1, "autoid")
assert_equal(rows, 1, "rows")
LOOP = 50
THREAD = 10
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)
threads = []
for tn in xrange(THREAD):
t = threading.Thread(target=work)
t.start()
threads.append(t)
for t in threads:
t.join()
aid, v2 = self.mydb.query_one("SELECT autoid, v FROM test1 WHERE k='%s'" % escape(myk))
assert_equal(int(v2), THREAD*LOOP+1, "finally v")
assert_equal(int(aid), 1, "autoid")
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')
def my_escape(s):
if isinstance(s, (int, long)):
return "%d" % s
return "'%s'" % escape(s)
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)
