我有一个名为“unprocessed”的表,我想在其中读取2000行,通过HTTP将它们发送到另一个服务器,然后将这些行插入“processed”表中并从“unprocessed”表中删除它们。在
我的python代码大致如下:db = MySQLdb.connect("localhost","username","password","database" )
# prepare a cursor object using cursor() method
cursor = db.cursor()
# Select all the records not yet sent
sql = "SELECT * from unprocessed where SupplierIDToUse = 'supplier1' limit 0, 2000"
cursor.execute(sql)
results = cursor.fetchall()
for row in results:
id = row[0]
if sentcorrectly="1":
sql = "INSERT into processed (id, dateprocessed) VALUES ('%s', NOW()')" % (id)
try:
inserted = cursor.execute(sql)
except:
print "Failed to insert"
if inserted:
print "Inserted"
sql = "DELETE from unprocessed where id = '%s'" % (id)
try:
deleted = cursor.execute(sql)
except:
print "Failed to delete id from the unprocessed table, even though it was saved in the processed table."
db.close()
sys.exit(0)
我希望能够同时运行这段代码,这样我就可以提高通过HTTP将这些记录发送到其他服务器的速度。
现在,如果我尝试同时运行代码,我会得到相同数据的多个副本,这些副本会发送到另一个服务器上,并保存到“已处理”表中,因为select查询在多个代码实例中获取相同的id。在
当我选择这些记录,然后在将它们移动到“已处理”表之前将它们作为一行进行处理时,如何锁定这些记录?
表是MyISAM,但我今天已经转换到innoDB,因为我意识到可能有一种方法可以更好地锁定innoDB的记录。在