Why does windows give an sqlite3.OperationalError and linux does not?

Question

The problem

I've got a programm that uses storm 0.14 and it gives me this error on windows:

sqlite3.OperationError: database table is locked

The thing is, under linux it works correctly.

I've got the impression that it happens only after a certain amount of changes have been done, as it happens in some code, that copies a lot of objects.

Turning on the debug mode gives me this on windows:

83 EXECUTE: 'UPDATE regularorder_product SET discount=? WHERE regularorder_product.order_id = ? AND regularorder_product.product_id = ?', (Decimal("25.00"), 788, 274)
84 DONE
85 EXECUTE: 'UPDATE repeated_orders SET nextDate=? WHERE repeated_orders.id = ?', (datetime.date(2009, 3, 31), 189)
86 ERROR: database table is locked

On linux:

83 EXECUTE: 'UPDATE regularorder_product SET discount=? WHERE regularorder_product.order_id = ? AND regularorder_product.product_id = ?', (Decimal("25.00"), 789, 274)
84 DONE
85 EXECUTE: 'UPDATE repeated_orders SET nextDate=? WHERE repeated_orders.id = ?', (datetime.date(2009, 3, 31), 189)
86 DONE

System info

Windows

• Windows XP SP 3
• Python 2.5.4
• NTFS partition

Linux

• Ubuntu 8.10
• Python 2.5.2
• ext3 partition

Some code

def createRegularOrderCopy(self):
    newOrder = RegularOrder()
    newOrder.date = self.nextDate
    # the exception is thrown on the next line,
    # while calling self.products.__iter__
    # this happens when this function is invoked the second time
    for product in self.products:
        newOrder.customer = self.customer
        newOrder.products.add(product)
        return newOrder

orders = getRepeatedOrders(date)
week = timedelta(days=7)

for order in orders:
    newOrder = order.createRegularOrderCopy()
    store.add(newOrder)
    order.nextDate = date + week

The question

Is there anything about sqlite3/python that differs between windows and linux? What could be the reason for this bug and how can I fix it?

Another observation

When adding a COMMIT at the place where the error happens, this error is thrown instead: sqlite3.OperationalError: cannot commit transaction - SQL statements in progress

Answers to answers

I'm not using multiple threads / processes, therefore concurrency shouldn't be a problem and also I've got only one Store object.

Answer 1

The "database table is locked" error is often a generic/default error in SQLite, so narrowing down your problem is not obvious.

Are you able to execute any SQL queries? I would start there, and get some basic SELECT statements working. It could just be a permissions issue.

Answer 2

Hard to say without a little more info on the structure of your database access (which is a little obscured by using Storm).

I'd start by reading these documents; they contain very relevant information:

1. https://storm.canonical.com/Manual#SQLite%20and%20threads

2. http://sqlite.org/lockingv3.html

Answer 3

Are you running any sort of anti-virus scanners? Anti-virus scanners will frequently lock a file after it has been updated, so that they can inspect it without it being changed. This may explain why you get this error after a lot of changes have been made; the anti-virus scanner has more new data to scan.

If you are running an anti-virus scanner, try turning it off and see if you can reproduce this problem.

Answer 4

It looks to me like storm is broken, though my first guess was virus scanner as Brian suggested.

Have you tried using sqlite3_busy_timeout() to set the timeout very high? This might cause SQLite3 to wait long enough for the lock holder, whoever that is, to release the lock.

Answer 5

I've solved the problem at the moment by replacing the sqlite3-dll with the newest version. I'm still not sure if this was a bug in the windows code of sqlite or if python installed an older version on windows than on linux.

Thanks for your help.