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)