SQLite3 and Transactions

Hello!

I am trying to build an application which uses a SQLite3 database for
caching purposes. However, I have difficulties in getting transactions
to work.

More specifically, it is _rollback_ that's giving me headaches
Upon poking around, I've seen that when I do some actual _modifications_
during the transaction (i.e., INSERT, CREATE, etc.), then rollback works
as it should.

But if I only do a SELECT or something similar, then the rollback fails
with "database is locked", which doesn't make much sense in this
context.

This works:
| db := SQLite3Connection new. db environment: ''; connect.
| db begin.
| ( db prepare: 'CREATE TABLE eins ( zwei INTEGER )' ) execute.
| ( db prepare: 'INSERT INTO eins VALUES ( 3 )' ) execute.
| db rollback.

But this doesn't (assuming that the table 'eins' has already been
created):
| db := SQLite3Connection new. db environment: ''; connect.
| db begin.
| ( db prepare: 'SELECT COUNT(*) FROM eins' ) execute answer next first. "0"
| ( db prepare: 'PRAGMA user_version' ) execute answer next first. "0"
| db rollback.

In the second case, an ExternalDatabaseError is raised, with error
string "database is locked".

The same thing happens if the transaction just _contains_ any kind of
non-modification (SELECT and similar), say interspersed between some
INSERTS or such, like this one:
| db := SQLite3Connection new. db environment: ''; connect.
| db begin.
| ( db prepare: 'CREATE TABLE eins ( zwei INTEGER )' ) execute.
| ( db prepare: 'INSERT INTO eins VALUES ( 3 )' ) execute.
| ( db prepare: 'SELECT COUNT(*) FROM eins' ) execute answer next first. "0"
| db rollback.

According to the DatabaseAppDevGuide, I believe that I'm using the
interface correctly. But still, it won't work.

Is there anything that I'm still missing, or is it just that
SQLite3EXDI doesn't support transactions, is broken, or something of the
like?


Thanks for any enlightenment on this matter,
    Stefan
_______________________________________________
vwnc mailing list
[hidden email]
http://lists.cs.uiuc.edu/mailman/listinfo/vwnc

Hello Stefan,

The "database is locked" exception seems to be raised because your code
does not clean up open cursors before rolling back a transaction.

Your last example was:
| db := SQLite3Connection new. db environment: ''; connect.
| db begin.
| ( db prepare: 'CREATE TABLE eins ( zwei INTEGER )' ) execute.
| ( db prepare: 'INSERT INTO eins VALUES ( 3 )' ) execute.
| ( db prepare: 'SELECT COUNT(*) FROM eins' ) execute answer next first.
"0"
| db rollback.

Fetching the first row of the answer set does not disconnect the
SQLite3Cursor created by the #prepare: method, so apparently, the cursor
for the answer set of the query still locks the database (or maybe the
transaction log file, I don't know much about the SQLite internals). If
you clean up the cursor, there's no exception.


Even when executing the CREATE TABLE and INSERT statement, you should
cancel the answer set by sending #cancel or #disconnect to the
SQLite3Cursor. It will be done during finalization sooner or later, but
it's not a good idea to leave them hanging around. They will consume
resources (buffers and other data structures), and should be freed as soon
as you know that you don't need them any more.

db := SQLite3Connection new. db environment: ''; connect.
db begin.
( db prepare: 'CREATE TABLE eins ( zwei INTEGER )' ) execute; disconnect.
( db prepare: 'INSERT INTO eins VALUES ( 3 )' ) execute; disconnect.
session := ( db prepare: 'SELECT COUNT(*) FROM eins' ); execute.
session answer next first. "0"
session disconnect.
db rollback.

It might also be better to make sure that #disconnect is called when there
is an error:

session := db getSession.
[session prepare: 'SELECT COUNT(*) FROM eins' ); execute.
session answer next first. "0"]
        ensure: [session disconnect]




HTH,
Joachim Geidel

Am 12.08.11 17:03 schrieb "Stefan Unterweger" unter
<[hidden email]>:



_______________________________________________
vwnc mailing list
[hidden email]
http://lists.cs.uiuc.edu/mailman/listinfo/vwnc

Hello Stefan,

The "database is locked" exception seems to be raised because your code
does not clean up open cursors before rolling back a transaction.

Your last example was:
| db := SQLite3Connection new. db environment: ''; connect.
| db begin.
| ( db prepare: 'CREATE TABLE eins ( zwei INTEGER )' ) execute.
| ( db prepare: 'INSERT INTO eins VALUES ( 3 )' ) execute.
| ( db prepare: 'SELECT COUNT(*) FROM eins' ) execute answer next first.
"0"
| db rollback.

Fetching the first row of the answer set does not disconnect the
SQLite3Cursor created by the #prepare: method, so apparently, the cursor
for the answer set of the query still locks the database (or maybe the
transaction log file, I don't know much about the SQLite internals). If
you clean up the cursor, there's no exception.


Even when executing the CREATE TABLE and INSERT statement, you should
cancel the answer set by sending #cancel or #disconnect to the
SQLite3Cursor. It will be done during finalization sooner or later, but
it's not a good idea to leave them hanging around. They will consume
resources (buffers and other data structures), and should be freed as soon
as you know that you don't need them any more.

db := SQLite3Connection new. db environment: ''; connect.
db begin.
( db prepare: 'CREATE TABLE eins ( zwei INTEGER )' ) execute; disconnect.
( db prepare: 'INSERT INTO eins VALUES ( 3 )' ) execute; disconnect.
session := ( db prepare: 'SELECT COUNT(*) FROM eins' ); execute.
session answer next first. "0"
session disconnect.
db rollback.

It might also be better to make sure that #disconnect is called when there
is an error:

session := db getSession.
[session prepare: 'SELECT COUNT(*) FROM eins' ); execute.
session answer next first. "0"]
        ensure: [session disconnect]




HTH,
Joachim Geidel

Am 12.08.11 17:03 schrieb "Stefan Unterweger" unter
<[hidden email]>:



_______________________________________________
vwnc mailing list
[hidden email]
http://lists.cs.uiuc.edu/mailman/listinfo/vwnc

Hello Joachim,

* Joachim Geidel on Sun, Aug 14, 2011 at 12:53:47PM +0200:
> The "database is locked" exception seems to be raised because your code
> does not clean up open cursors before rolling back a transaction.

> Fetching the first row of the answer set does not disconnect the
> SQLite3Cursor created by the #prepare: method, so apparently, the cursor
> for the answer set of the query still locks the database (or maybe the
> transaction log file, I don't know much about the SQLite internals). If
> you clean up the cursor, there's no exception.

Ah, of course you're right; it didn't even cross my mind that I'm
piling up abandoned cursors left and right if I just use the database that way.

Now it works like a charm -- thanks for the cluestick.
_______________________________________________
vwnc mailing list
[hidden email]
http://lists.cs.uiuc.edu/mailman/listinfo/vwnc