On Aug 11, 2004, at 6:49 AM, D. Richard Hipp wrote:
Oops. The db1 should do a ROLLBACK, not a COMMIT. Or db2 can do an END TRANSACTION (since it never made any changes) and allow db1 to complete instead. The point is that when two threads or processes are trying to write at the same time, one of the two must back off, abandon their transaction (using ROLLBACK) and let the other proceed.
Wow. That adds a whole lot of complexity to my code. Every transaction would be inside a loop that checks for a busy return from any statement within. And most of the places I'm using a transaction, I'm doing a few hundred inserts or updates from a number of different functions.
This really is something I'd expect to run under the hood. Since only one of the competing threads will have completed a write (right?), can't the others "postpone" their transactions somehow until they can get a write lock?
For now, I've solved the problem by adding my own locks to exclude simultaneous transactions on the same database file. I'm only using transactions for writes (is there any reason for a read-only transaction?) so if there's no way to resolve two opened write transactions, you shouldn't be able to open two in the first place.
Please let me know if there's something I'm missing here..
Thanks, -Dave
