Does SQLiteJDBC from xerial support transactions?

[julianbui]

I have posted the same question on the zentus board.

Does this version of SQLite's JDBC driver support transactions? I
read that SQLite's JDBC driver does not.

Please let me know.

Thanks,
Julian

[Taro L. Saito]

Transaction processing is supported in SQLite JDBC.

Here is a test code that works properly:
http://www.xerial.org/svn/project/XerialJ/trunk/sqlite-jdbc/src/test/java/org/sqlite/TransactionTest.java

[julianbui]

Thanks for responding so quickly, Taro.

Do you know what version of SQLiteJDBC transactions started being
supported? I notice your test code is from the svn repository. Are
transactions supported in stable releases?

On Oct 25, 5:43 am, "Taro L. Saito" <l...@xerial.org> wrote:
> Transaction processing is supported in SQLite JDBC.
>

> Here is a test code that works properly:http://www.xerial.org/svn/project/XerialJ/trunk/sqlite-jdbc/src/test/...

[Taro L. Saito]

I guess all versions of SQLite JDBC do support transactions.

And also, the version 3.5.7 or later had been released after I
confirmed that all the tests I mentioned before were passed
successfully.

But I recommend you to use the latest one (3.6.4 for now) because
eariler releases contain some SQLite's own bugs.

[julianbui]

I notice that SQLite's sql grammar uses begin and end/commit to mark
beginning and ends of transactions. The code you provided does not
have the end/commit sql query. It only has a connection.commit().
Are the "begin" and "end/commit" sql commands necessary when being
used with JDBC? I have assumed up to this point that the JDBC should
be abstracting that.

Julian

[Taro L. Saito]

Hi Julian,

In SQLite JDBC, auto commit mode is on by default, that means there is
no need to
wrap a single update operation with begin and commit statements.
Each update operation is automatically wrapped with begin and commit.

If you have to pack multiple update operations within a transaction,
first you have to set the auto commit mode to false by calling
Connection.setAutoCommit(false) method.
Then, issue commit. In this case, the update data not stored on the
disk before the commit.


ResultSet rs;
String countSql = "select count(*) from trans;";

stat1.executeUpdate("create table trans (c1);");
conn1.setAutoCommit(false);

assertEquals(1, stat1.executeUpdate("insert into trans values
(4);"));

// transaction not yet commited, conn1 can see, conn2 can not
rs = stat1.executeQuery(countSql);
assertTrue(rs.next());
assertEquals(1, rs.getInt(1));
rs.close();
rs = stat2.executeQuery(countSql);
assertTrue(rs.next());
assertEquals(0, rs.getInt(1));
rs.close();

conn1.commit();

[julianbui]

Hi again, Taro,

Maybe I am doing something wrong, but I'm finding that using batch
operations without transactions is much much slower than batch
operations with transactions. With transactions I'm getting 55 writes/
ms for the first 10000 writes. Without transactions I'm getting like
0.3 writes/ms for the first 10000 writes. This goes against intuition
since transactions require extra disk i/o for journaling. Do you know
if theres something I'm doing wrong, or can you provide some insight?
I've attached some simple code below.

Please let me know.

Thanks,
Julian

//**** WITH TRANSACTIONS
conn.setAutoCommit(false);
...
for i = 0 to some large number { dataInsertPs.addBatch(); }
int[] results = dataInsertPs.executeBatch();
dataInsertPs.close();
conn.commit();
----------------------------------------
//**** WITHOUT TRANSACTIONS
conn.setAutoCommit(true);
...
for i = 0 to some large number { dataInsertPs.addBatch(); }
int[] results = dataInsertPs.executeBatch();
dataInsertPs.close();
----------------------------------------

[Taro L. Saito]

I guess you misunderstood that the second program also uses
transactions
for each update operation in the batch, so it may be significantly
slower than the first program.

If you want to further improve the insertion performance of SQLite,
also try to issue the following command before the updates:

statement.executeUpdate("pragma synchronous=off");

This setting is not safe for concurrent updates, but when creating
database, it is quite efficient.

[julianbui]

Thanks for the pragma tip, I'll try that.

Why is that second program I posted using a transaction for every
update in the batch? I assumed the term batch implies that a bunch of
operations get done at the same time. Do you know if there's a way to
use batch operations without transactions at all?

Julian

[Taro L. Saito]

Generally speaking, to turn off auto commit is a preferred way in
using batch operations.

See the JDBC manual:
http://java.sun.com/j2se/1.3/docs/guide/jdbc/spec2/jdbc2.1.frame6.html

Your two programs examples seem to be equivalent to the following in
SQLite JDBC:

(the first one)
begin transaction
insert ...
insert ..
commit;

(the second one)
begin transaction
insert ...
commit
begin transaction
insert ...
commit