当变量 NUMBER_OF_ITERATIONS 设置为 1 时，一切正常...但是当我将其更改为大于 1 的任何值时，我会遇到一些问题。

首先，在这种情况下，当我打印 res 的值时，我得到了一个巨大的数字(比如 18446744073709551615)。

其次，但也是最重要的，在这种情况下，脚本无法处理数据，因为值的长度始终为 0...

if __name__ == '__main__':

    NUMBER_OF_ITERATIONS = 2

    conn = DBconnection()   # return a database connection

    for i in range( NUMBER_OF_ITERATIONS ):
        cursor = conn.cursor()
        res = cursor.execute( 'SELECT field 
                                 FROM table 
                                WHERE other_field = 0 
                                LIMIT 10 LOCK IN SHARE MODE' )
        print '# of selected rows: ' + str(res)

        values = []
        for elem in cursor.fetchall():
            if elem != None:
                values.append( list(elem).pop() )

        if len( values ) != 0:
            # do something...

        else:
            print 'NO VALUES AVAILABLE'
            cursor.close()
            break

    conn.close()
    print 'DONE'

我正在使用 InnoDB 存储引擎，同时在这个脚本中还有另一个脚本 python 在同一个表上上传数据(使用构造加载数据 INFILE)。

我以为可能是加载数据导致的表锁，但是1到2次(或多次)迭代有什么区别呢？一次迭代一切正常，而 2 次迭代甚至第一次迭代都变坏了。我不明白这一点。

最佳答案

我无法使用以下代码重现该问题。你能修改它来演示错误吗？

import config
import MySQLdb
import multiprocessing as mp
import random
import string
import time

def random_string(n):
    return ''.join(random.choice(string.letters) for _ in range(n))

def generate_data():
    conn=MySQLdb.connect(
        host=config.HOST,user=config.USER,
        passwd=config.PASS,db='test')    
    cursor=conn.cursor()
    while True:
        with open('/tmp/test.dat','w') as f:
            for _ in range(20):
                f.write('{b}\n'.format(b=random_string(10)))
        # sql='LOCK TABLES foo WRITE'
        # cursor.execute(sql)
        sql="LOAD DATA INFILE '/tmp/test.dat' INTO TABLE test.foo"
        cursor.execute(sql)
        conn.commit()
        # sql='UNLOCK TABLES'
        # cursor.execute(sql)        
        time.sleep(0.05)

def setup_innodb(connection):
    cursor=connection.cursor()
    sql='DROP TABLE IF EXISTS foo'
    cursor.execute(sql)
    sql='''\
        CREATE TABLE `foo` (
          `bar` varchar(10) NOT NULL
        ) ENGINE=InnoDB  
        '''
    cursor.execute(sql)
    connection.commit()

if __name__ == '__main__':
    NUMBER_OF_ITERATIONS = 20
    conn=MySQLdb.connect(
        host=config.HOST,user=config.USER,
        passwd=config.PASS,db='test')
    setup_innodb(conn)

    # Start a process which is "simultaneously" calling LOAD DATA INFILE
    proc=mp.Process(target=generate_data)
    proc.daemon=True
    proc.start()

    for i in range( NUMBER_OF_ITERATIONS ):
        cursor = conn.cursor()
        # sql='''SELECT field 
        #        FROM table 
        #        WHERE other_field = 0 
        #        LIMIT 10 LOCK IN SHARE MODE'''
        # sql='LOCK TABLES foo READ'
        # cursor.execute(sql)
        sql='''SELECT * 
               FROM foo
               LOCK IN SHARE MODE
               '''
        res = cursor.execute(sql)
        print '# of selected rows: ' + str(res)
        values = cursor.fetchall()
        # http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html
        # Locks set by LOCK IN SHARE MODE and FOR UPDATE reads are released when
        # the transaction is committed or rolled back.
        conn.commit()
        time.sleep(0.1)

    conn.close()
    print 'DONE'

产量

# of selected rows: 0
# of selected rows: 40
# of selected rows: 80
# of selected rows: 120
# of selected rows: 160
# of selected rows: 180
# of selected rows: 220
# of selected rows: 260
# of selected rows: 300
# of selected rows: 340
# of selected rows: 360
# of selected rows: 400
# of selected rows: 440
# of selected rows: 460
# of selected rows: 500
# of selected rows: 540
# of selected rows: 580
# of selected rows: 600
# of selected rows: 640
# of selected rows: 680
DONE

关于python - MySQL SELECT 的奇怪行为，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/5284475/