在使用pyodbc与多进程并行更新MSSQL表时出现太多死锁错误

Question

我试图打开pickle文件中有数据，然后用该数据更新MSSQL表。它花了很长时间，10天更新1,000,000行。所以我写了一个脚本来实现更多的并行性。我运行的进程越多，得到的错误就越多

(<class 'pyodbc.Error'>, Error('40001', '[40001] [Microsoft][ODBC SQL Server Dri
ver][SQL Server]Transaction (Process ID 93) was deadlocked on lock resources wit
h another process and has been chosen as the deadlock victim. Rerun the transact
ion. (1205) (SQLExecDirectW)'), <traceback object at 0x0000000002791808>)  

正如你在我的代码中看到的，我一直试图处理更新，直到成功，甚至在这里睡了一秒钟

while True:
    try:
        updated = cursor.execute(update,'Yes', fileName+'.'+ext, dt, size,uniqueID )
        break
    except:
        time.sleep(1)
        print sys.exc_info() 

这是因为当你在windows中使用多处理模块时，它使用os。刷出而不是os。叉?

是否有办法做到这一点，将提供更多的速度?

我被告知这个表可以处理更多的事务。

#!C:/Python/python.exe -u
import pyodbc,re,pickle,os,glob,sys,time
from multiprocessing import Lock, Process, Queue, current_process

def UpDater(pickleQueue):
   for pi in iter(pickleQueue.get, 'STOP'):
        name = current_process().name
        f=pi
        cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=database.windows.net;DATABASE=DB;UID=user;PWD=pwd');
        cursor = cnxn.cursor()
        update = ("""UPDATE DocumentList
                SET Downloaded=?, DownLoadedAs=?,DownLoadedWhen=?,DownLoadedSizeKB=?
                WHERE DocNumberSequence=?""")
        r = re.compile('d+')
        pkl_file = open(pi, 'rb')
        meta = pickle.load(pkl_file)
        fileName = meta[0][0]
        pl = r.findall(fileName)
        l= int(len(pl)-1)
        ext = meta[0][1]
        url = meta[0][2]
        uniqueID = pl[l]
        dt = meta[0][4]
        size = meta[0][5]
        while True:
            try:
                updated = cursor.execute(update,'Yes', fileName+'.'+ext, dt, size,uniqueID )
                break
            except:
                time.sleep(1)
                print sys.exc_info() 
        print uniqueID  
        cnxn.commit()
        pkl_file.close()
        os.remove(fileName+'.pkl')
        cnxn.close()
if __name__ == '__main__':
    os.chdir('Pickles')
    pickles = glob.glob("*.pkl")
    pickleQueue=Queue();processes =[];
    for item in pickles:
        pickleQueue.put(item)

    workers = int(sys.argv[1]);
    for x in xrange(workers):
            p = Process(target=UpDater,args=(pickleQueue,))
            p.start()
            processes.append(p)
            pickleQueue.put('STOP')
    for p in processes:
        p.join()

我使用的是Windows 7和python 2.7 Anaconda Distribution

编辑下面使用行锁的答案阻止了错误的发生。然而，更新速度仍然很慢。原来主键上的老式索引需要100倍的加速

Answer 1

可以尝试一下。利用睡眠是一个坏主意。首先，你能尝试行级锁定吗?

    update = ("""UPDATE DocumentList WITH (ROWLOCK)
            SET Downloaded=?, DownLoadedAs=?,DownLoadedWhen=?,DownLoadedSizeKB=?
            WHERE DocNumberSequence=? """)

另一个选择是将每个包在一个事务中:

    update = ("""
        BEGIN TRANSACTION my_trans;
            UPDATE DocumentList
            SET Downloaded=?, DownLoadedAs=?,DownLoadedWhen=?,DownLoadedSizeKB=?
            WHERE DocNumberSequence=?;
        END TRANSACTION my_trans;
    """)

这些解决方案对你有用吗?