Android Question Behaviour With b4a 3.20 upgrade and Sqlite Transaction

[Tommy h]

Hi all,
I've update b4a version from 3.0 to 3.20 and see a strange behaviour with sqlite multiple transaction.


Sample code:

DbPocket.Initialize(WorkDir, CreatoreApp & "Pocket.sqlite", False)
DbPocket.BeginTransaction
DbPocket.BeginTransaction
DbPocket.ExecNonQuery("Create Table TmpT(CodClie NvarChar(30), RagSoc NvarChar(98))
DbPocket.TransactionSuccessful
DbPocket.EndTransaction
DbPocket.Close
DbPocket.Initialize(WorkDir, CreatoreApp & "Pocket.sqlite", False)


Works fine in 3.00 version but crash with this error:
“android.database.sqlite.SQLiteDatabaseLockedException: database is locked (code 5): , while compiling: PRAGMA journal_mode”
in 3.20 version.

I use the same sql library version 1.20
Seems that more BeginTransaction without EndTransaction cause the issue.

Ps. After debugging with 3.20 b4a version I can reproduce this error even with 3.00 b4a

Thanks in advance,
Best Regards

 

[ernschd]

For me this code is working (in Version 3.20)

DbPocket.Initialize(File.DirRootExternal, "Pocket.sqlite", True)
    DbPocket.BeginTransaction
    DbPocket.ExecNonQuery("Create Table TmpT(CodClie INTEGER, RagSoc TEXT)")
    DbPocket.TransactionSuccessful
    DbPocket.EndTransaction
    DbPocket.Close
    DbPocket.Initialize(File.DirRootExternal, "Pocket.sqlite", False)

 

[Erel]

I don't see any error here. It is also not related to v3.20 (the SQL library was not updated).

Why do you call BeginTransaction twice? The database is locked after the first call.

 

[Tommy h]

Hi ernschd,
yes with a single BeginTransaction code works fine,
but I've two or more BeginTransaction statement in a loop of upgrade and a single EndTransaction at the and of db upgrade,
with b4a 3.00 this works fine but after upgrade to b4a 3.20 this is not possible anymore.

 

[Erel]

As I wrote above the SQL library was not changed in this update. This is a programming error to begin a transaction while there is another one open. The database will be locked at this point.

You should either remove the transactions code and let SQLite manage the transactions internally or start a transaction, do whatever you need to do and close it.

 

[Tommy h]

I don't see any error here. It is also not related to v3.20 (the SQL library was not updated).

Why do you call BeginTransaction twice? The database is locked after the first call.

Hi Erel,
I don't know why but if you hardreset a device (or use a device never used with b4a 3.20)
and run this code

DbPocket.Initialize(WorkDir, CreatoreApp & "Pocket.sqlite", False)
DbPocket.BeginTransaction
DbPocket.BeginTransaction
DbPocket.ExecNonQuery("Create Table TmpT(CodClie NvarChar(30), RagSoc NvarChar(98))
DbPocket.TransactionSuccessful
DbPocket.EndTransaction
DbPocket.Close
DbPocket.Initialize(WorkDir, CreatoreApp & "Pocket.sqlite", False)

It works fine, but using b4a 3.20 (with the same sql library 1.20) doesn't works
After Running b4a 3.20 even b4a 3.00 doesn't work (I need to hardreset device or use a device not used with 3.20 and it return to work)

 

[Tommy h]

As I wrote above the SQL library was not changed in this update. This is a programming error to begin a transaction while there is another one open. The database will be locked at this point.

You should either remove the transactions code and let SQLite manage the transactions internally or start a transaction, do whatever you need to do and close it.

I've written those few sample rows only to reproduce this issue, my source is more complex.
I will change the code to do a single begintrasaction at a time, but the behavior from 3.00 to 3.20 is changed in some kind of manner.
I hoped you could give me an idea about changing from one version to another and, if you try
you could reproduce this behavior simply.

 

[Erel]

Again, I don't see how it can be related to v3.20. I'm pretty sure that you can reproduce this error without v3.20.

However it doesn't really matter, this is a programming error. You should fix the code to properly start and close transactions.

 

[Tommy h]

Again, I don't see how it can be related to v3.20. I'm pretty sure that you can reproduce this error without v3.20.

However it doesn't really matter, this is a programming error. You should fix the code to properly start and close transactions.

No I can't reproduce on 3.00 I've made many and many test on it.
Sorry but have you try it?
Ok, no problem, I will do it. Thanks

 

[Erel]

Please read my answer again:

However it doesn't really matter, this is a programming error. You should fix the code to properly start and close transactions.

 

[Tommy h]

Please read my answer again:

Maybe you haven't read my answer
Ok, no problem, I will do it. Thanks

 

[John D.]

http://www.sqlite.org/lang_transaction.html

"Transactions can be deferred, immediate, or exclusive. The default transaction behavior is deferred. Deferred means that no locks are acquired on the database until the database is first accessed. Thus with a deferred transaction, the BEGIN statement itself does nothing to the filesystem. Locks are not acquired until the first read or write operation. The first read operation against a database creates a SHARED lock and the first write operation creates a RESERVED lock. Because the acquisition of locks is deferred until they are needed, it is possible that another thread or process could create a separate transaction and write to the database after the BEGIN on the current thread has executed."

Not sure whether you intended it or not, but it appears it was only a matter of luck that your old code ran at all with multiple begin commands. A slight change in speed or order of execution was all that was needed to break it.

 

[Tommy h]

http://www.sqlite.org/lang_transaction.html



Not sure whether you intended it or not, but it appears it was only a matter of luck that your old code ran at all with multiple begin commands. A slight change in speed or order of execution was all that was needed to break it.

Yes John, thank you. I've solved managing transaction correctly.