Sqlite transaction bug

Archive
1

I’ve discovered what I think is a bug in the SQLite module. It doesn’t seem to handle transactions correctly.

I created an app that would separately execute the following queries:




```cs]create table [Product

( 
  [IdProduct] integer primary key, 
  [ProductCode] nvarchar, 
  [Description] nvarchar 
);



It executes the 'begin transaction' and the 'create table' without any problems but when it attempts to create the index it just hangs.

If I remove the begin transaction everything works fine.

Is this a bug?
2

You should use a commit transaction before you create a index but i dont see why you would use a transaction on a create table instruction.

3

David@ Emrol,

I don’t believe that is correct. You should be able to execute as many queries as you like inside a transaction. What I’m doing in perfectly acceptable in the world of SQL.

However, I am more used to SQL Server and if anyone can give me a good reason why this is not possible then I’m quite happy to listen.

4

In the world of SQL server, I would recommend not creating, dropping (any changes to the db schema) table/indexes inside transactions (including temporary tables) for performance and locking the sys tables. Transaction locks will be added to the sys tables. Better to use a try/catch block to clean if error.

5

Kiwi_Stu,

I appreciate what you are saying about the system tables but the point is here that these queries in a transaction should work. I’ve tried the same thing in a SQLite desktop application and it works fine.

I was merely trying to point out a possible bug for the GHI team.

6

I was unable to reproduce the issue. Could you post a small test program that can reproduce it for you?

7

Hi John,

I’ve managed to work out what was causing the issue. To protect the resources I was wrapping each ExecuteNonQuery calls in lock statements. If you do this for a begin transaction then subsequent calls to ExecuteNonQuery will lock up.

However, it doesn’t make sense to lock the resources in this way. If you are creating a transaction then the lock statement should really encompass the whole transaction.

When I change the code to do this it work fine.