Python threads - crashing when they access postgreSQL

Question

Hello, here is a simple threading program which works fine:

import psycopg2
import threading
import time

class testit(threading.Thread):
    def __init__(self, currency):
        threading.Thread.__init__(self)
        self.currency = currency

    def run(self):
        global SQLConnection
        global cursor
        SQLString = "Select dval from ddata where dname ='%s' and ddate = '2009-07-17'" \
                %self.currency
        z = time.time()
        while (time.time() - z) < 2:
            print SQLString

SQLConnection = psycopg2.connect(database = "db", user = "xxxx", password = "xxxx")
cursor = SQLConnection.cursor()

a = testit('EURCZK')
b = testit('EURPLN')
a.start()
b.start()

However as soon as I try to start accessing the postgresql database in the thread with the following code, I always get a stop-sign crash:

import psycopg2
import threading
import time

class testit(threading.Thread):
    def __init__(self, currency):
        threading.Thread.__init__(self)
        self.currency = currency

    def run(self):
        global SQLConnection
        global cursor
        SQLString = "Select dval from ddata where dname ='%s'and ddate = '2009-07-17'" %self.currency
        z = time.time()
        while (time.time() - z) < 2:
            cursor.execute(SQLString)
            print cursor.fetchall()

SQLConnection = psycopg2.connect(database = "db", user = "xxxx", password = "xxxx")
cursor = SQLConnection.cursor()

a = testit('EURCZK')
b = testit('EURPLN')
a.start()
b.start()

The only difference between the two is in the while loop. I am fairly new to thread programming. Is the postgres library (psycopg2) not "thread safe"? All this is running on Windows XP. Anything I can do?

Thanks.

Answer 1

global SQLConnection
global cursor

Seems you're accessing globals from multiple threads ? You should never do that unless those globals are thread safe, or you provide the proper locking yourself.

You now have 2 threads accessing the same connection and the same cursor. They'll step on eachothers toes. psycopg2 connection might be thread safe but cursors are not.

Use one cursor(probably one connection as well) per thread.

Answer 2

bingo it's working. Someone left an answer but then seems to have removed it, to give each thread its own connection. And yep that solves it. So this code works:

import psycopg2
import threading
import time

class testit(threading.Thread):
    def __init__(self, currency):
        threading.Thread.__init__(self)
        self.currency = currency 
        self.SQLConnection = psycopg2.connect(database = "db", user = "xxxx", password = "xxxx")
        self.cursor = self.SQLConnection.cursor()

    def run(self):
        SQLString = "Select dval from ddata where dname ='%s' and ddate = '2009-07-17'" \
                %self.currency
        z = time.time()
        while (time.time() - z) < 2:
            self.cursor.execute(SQLString)
            print self.cursor.fetchall()

a = testit('EURCZK')
b = testit('EURPLN')
a.start()
b.start()