python - Python 脚本中的 MySQL 接口(interface)错误

Question

我有以下定义:

def sql_processes(db1, infile_name, z):
    cursor = db1.cursor()
    print "Processing " + infile_name
    PrintLog("Adding " + infile_name + "to MySQL...")
    vcf_reader = vcf.Reader(open(infile_name, 'r'))
    for record in vcf_reader:
        snp_position='_'.join([record.CHROM, str(record.POS)])
        ref_F = float(record.INFO['DP4'][0])
        ref_R = float(record.INFO['DP4'][1])
        alt_F = float(record.INFO['DP4'][2])
        alt_R = float(record.INFO['DP4'][3])
        AF = (alt_F+alt_R)/(alt_F+alt_R+ref_F+ref_R)
        sql_test_query = "SELECT * from snps where snp_pos='" + snp_position + "'"
        try:
            sql_insert_table = "INSERT INTO snps (snp_pos, " + str(z) + "g) VALUES ('" + snp_position + "', " + str(AF) + ")"
            cursor.execute(sql_insert_table)
        except db1.IntegrityError, e:
            sql_insert_table = "UPDATE snps SET " + str(z) + "g=" + str(AF) + " WHERE snp_pos='" + snp_position + "'";
            cursor.execute(sql_insert_table)
        db1.commit()
    cursor.close()
    print "Processing of " + infile_name + "done!"
    PrintLog("Added " + infile_name + "to MySQL!")

def extractAF(files_vcf):
    z=6
    snp_dict=[]
    db1 = MS.connect(host="localhost",user="root",passwd="sequentia2",db="SUPER_SNP_calling")
    threads = []
    for infile_name in sorted(files_vcf):
        t = Thread(target = sql_processes, args = (db1, infile_name, z))
        threads.append(t)
        z+=1
    count_t = 1
    my_threads = []
    for t in threads:
        t.start()
        my_threads.append(t)
        if count_t == 8:
            for x in my_threads:
                x.join()
            my_threads = []
            count_t = 0
        count_t+=1

这里的目的是同时读取多个文件并更新 MySQL 数据库。但是，这会引发以下错误:

Exception in thread Thread-1:
Traceback (most recent call last):
  File "/usr/lib/python2.7/threading.py", line 810, in __bootstrap_inner
    self.run()
  File "/usr/lib/python2.7/threading.py", line 763, in run
    self.__target(*self.__args, **self.__kwargs)
  File "./SUPER_mysql4.py", line 457, in sql_processes
    cursor.execute(sql_insert_table)
  File "/usr/local/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 202, in execute
    self.errorhandler(self, exc, value)
  File "/usr/local/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
InterfaceError: (0, '')

*** Error in `Segmentation fault (core dumped)

为什么会发生这种情况？

Answer 1

我认为这个错误是基于mysql锁

MySQL uses table-level locking for MyISAM, MEMORY and MERGE tables, page-level locking for BDB tables, and row-level locking for InnoDB table.Table updates are given higher priority than table retrievals. Therefore, when a lock is released, the lock is made available to the requests in the write lock queue and then to the requests in the read lock queue. This ensures that updates to a table are not “starved” even if there is heavy SELECT activity for the table. However, if you have many updates for a table, SELECT statements wait until there are no more update.

由于使用不同线程插入同一个表可能会锁定表以锁定 1st 线程开始的插入。因此其他线程和 SELECT 必须等待完成 INSERT 在所有线程中。

希望这对你有帮助

for t in threads:
    t.start()
    t.join()

Thread.join

Wait until the thread terminates. This blocks the calling thread until the thread whose join() method is called terminates – either normally or through an unhandled exception – or until the optional timeout occurs

了解更多http://dev.mysql.com/doc/refman/5.0/en/internal-locking.html