System.Data.SQLite

SQLITE doku writes that "but changes caused by prior SQL statements within the same transaction are preserved and the transaction remains active".

mistachkin added on 2011-09-11 13:09:52 UTC:
This issue seems to involve the .NET Entity Framework and I am not an expert on that package; however, it seems that the TransactionScope class is normally unnecessary unless you have changes that span multiple object contexts (e.g. multiple SQLite databases), see:

http://stackoverflow.com/questions/794707/why-doesnt-transactionscope-work-with-entity-framework

mistachkin added on 2011-09-11 13:40:00 UTC:
Related: http://msdn.microsoft.com/en-us/library/bb738523.aspx

mistachkin added on 2011-09-11 13:42:27 UTC:
Also, there seems to be a problem with the SQLiteConnection.EnlistTransaction method that prevents TransactionScope from working with an ObjectContext if the code involved tries to access the database more than once from inside the same transaction.

mistachkin added on 2011-09-11 15:36:54 UTC:
An experimental fix for this issue is available in check-in [f4e1038098], with unit tests.

Would it be possible for you to test this fix in your environment and report back with the outcome of your testing?

anonymous added on 2011-09-12 08:47:13 UTC:
with this patch it works now like expected. The transaction raised the Exception if some commands fails, but other commands executed normal.

anonymous added on 2011-09-12 11:23:11 UTC:
After testing again error still exist. (preview comment is wrong)

mistachkin added on 2011-09-12 12:45:35 UTC:
Did you build the new DLLs from the source code on the "bug-ccfa69fc32" branch? If so, are you seeing precisely the same behavior as before or something slightly different now?

mistachkin added on 2011-09-12 20:23:38 UTC:

Ok, I think I've figured it out.  Thanks for your help troubleshooting.

Adding some diagnostic output to the System.Data.SQLite assembly, I see this output
(the SQL below is being prepared for execution):

PRAGMA legacy_file_format=OFF

PRAGMA synchronous=Normal

PRAGMA foreign_keys=OFF

BEGIN IMMEDIATE

SELECT
[c].[RegionID] AS [RegionID],
[c].[RegionDescription] AS [RegionDescription]
FROM [Regions] AS [c] LIMIT 1

SELECT
[c].[RegionID] AS [RegionID],
[c].[RegionDescription] AS [RegionDescription]
FROM [Regions] AS [c] LIMIT 1

SELECT
[c].[RegionID] AS [RegionID],
[c].[RegionDescription] AS [RegionDescription]
FROM [Regions] AS [c] LIMIT 1

SELECT
[c].[RegionID] AS [RegionID],
[c].[RegionDescription] AS [RegionDescription]
FROM [Regions] AS [c] LIMIT 1

SELECT
[c].[RegionID] AS [RegionID],
[c].[RegionDescription] AS [RegionDescription]
FROM [Regions] AS [c] LIMIT 1

SELECT
[c].[RegionID] AS [RegionID],
[c].[RegionDescription] AS [RegionDescription]
FROM [Regions] AS [c] LIMIT 1

SELECT
[c].[RegionID] AS [RegionID],
[c].[RegionDescription] AS [RegionDescription]
FROM [Regions] AS [c] LIMIT 1

SELECT
[c].[RegionID] AS [RegionID],
[c].[RegionDescription] AS [RegionDescription]
FROM [Regions] AS [c] LIMIT 1

SELECT
[c].[RegionID] AS [RegionID],
[c].[RegionDescription] AS [RegionDescription]
FROM [Regions] AS [c] LIMIT 1

SELECT
[c].[RegionID] AS [RegionID],
[c].[RegionDescription] AS [RegionDescription]
FROM [Regions] AS [c] LIMIT 1

SELECT
[c].[RegionID] AS [RegionID],
[c].[RegionDescription] AS [RegionDescription]
FROM [Regions] AS [c] LIMIT 1

SELECT
[c].[RegionID] AS [RegionID],
[c].[RegionDescription] AS [RegionDescription]
FROM [Regions] AS [c] LIMIT 1

SELECT
[c].[RegionID] AS [RegionID],
[c].[RegionDescription] AS [RegionDescription]
FROM [Regions] AS [c] LIMIT 1

SELECT
[c].[RegionID] AS [RegionID],
[c].[RegionDescription] AS [RegionDescription]
FROM [Regions] AS [c] LIMIT 1

SELECT
[c].[RegionID] AS [RegionID],
[c].[RegionDescription] AS [RegionDescription]
FROM [Regions] AS [c] LIMIT 1

SELECT
[c].[RegionID] AS [RegionID],
[c].[RegionDescription] AS [RegionDescription]
FROM [Regions] AS [c] LIMIT 1

SELECT
[c].[RegionID] AS [RegionID],
[c].[RegionDescription] AS [RegionDescription]
FROM [Regions] AS [c] LIMIT 1

SELECT
[c].[RegionID] AS [RegionID],
[c].[RegionDescription] AS [RegionDescription]
FROM [Regions] AS [c] LIMIT 1

SELECT
[c].[RegionID] AS [RegionID],
[c].[RegionDescription] AS [RegionDescription]
FROM [Regions] AS [c] LIMIT 1

SELECT
[c].[RegionID] AS [RegionID],
[c].[RegionDescription] AS [RegionDescription]
FROM [Regions] AS [c] LIMIT 1

SELECT
[c].[RegionID] AS [RegionID],
[c].[RegionDescription] AS [RegionDescription]
FROM [Regions] AS [c] LIMIT 1

SELECT
[c].[RegionID] AS [RegionID],
[c].[RegionDescription] AS [RegionDescription]
FROM [Regions] AS [c] LIMIT 1

INSERT INTO [Territories]([TerritoryID], [TerritoryDescription], [RegionID])
 VALUES (@p0, @p1, @p2);

INSERT INTO [Territories]([TerritoryID], [TerritoryDescription], [RegionID])
 VALUES (@p0, @p1, @p2);

INSERT INTO [Territories]([TerritoryID], [TerritoryDescription], [RegionID])
 VALUES (@p0, @p1, @p2);

INSERT INTO [Territories]([TerritoryID], [TerritoryDescription], [RegionID])
 VALUES (@p0, @p1, @p2);

INSERT INTO [Territories]([TerritoryID], [TerritoryDescription], [RegionID])
 VALUES (@p0, @p1, @p2);

INSERT INTO [Territories]([TerritoryID], [TerritoryDescription], [RegionID])
 VALUES (@p0, @p1, @p2);

INSERT INTO [Territories]([TerritoryID], [TerritoryDescription], [RegionID])
 VALUES (@p0, @p1, @p2);

INSERT INTO [Territories]([TerritoryID], [TerritoryDescription], [RegionID])
 VALUES (@p0, @p1, @p2);

INSERT INTO [Territories]([TerritoryID], [TerritoryDescription], [RegionID])
 VALUES (@p0, @p1, @p2);

INSERT INTO [Territories]([TerritoryID], [TerritoryDescription], [RegionID])
 VALUES (@p0, @p1, @p2);

INSERT INTO [Territories]([TerritoryID], [TerritoryDescription], [RegionID])
 VALUES (@p0, @p1, @p2);

INSERT INTO [Territories]([TerritoryID], [TerritoryDescription], [RegionID])
 VALUES (@p0, @p1, @p2);

INSERT INTO [Territories]([TerritoryID], [TerritoryDescription], [RegionID])
 VALUES (@p0, @p1, @p2);

INSERT INTO [Territories]([TerritoryID], [TerritoryDescription], [RegionID])
 VALUES (@p0, @p1, @p2);

INSERT INTO [Territories]([TerritoryID], [TerritoryDescription], [RegionID])
 VALUES (@p0, @p1, @p2);

INSERT INTO [Territories]([TerritoryID], [TerritoryDescription], [RegionID])
 VALUES (@p0, @p1, @p2);

SQLite error (19): abort at 13 in [INSERT INTO [Territories]([TerritoryID], [TerritoryDescription], [RegionID])
 VALUES (@p0, @p1, @p2);]: PRIMARY KEY must be unique

A first chance exception of type 'System.Data.SQLite.SQLiteException' occurred in System.Data.SQLite.dll
A first chance exception of type 'System.Data.UpdateException' occurred in System.Data.Entity.dll

COMMIT

The program '[6896] testlinq.exe: Program Trace' has exited with code 0 (0x0).
The program '[6896] testlinq.exe: Managed (v4.0.30319)' has exited with code 0 (0x0). 

The above was produced using my test code with the list of IDs modified to include the
ones you added, as follows:

          long[] territoryIds = new long[] {
              1576, 1577, 1578, 1579, 1580, // NOTE: Success
              1581, 1730, 1833, 2116, 2139, // NOTE: Fail
              2140, 2141                    // NOTE: Skipped
          };

What happens deep down is that SQLite is returning an error code (19 or SQLITE_CONSTRAINT) from
the sqlite3_step() native function when attempting to INSERT the 16th row during the SaveChanges
step (i.e. the first ID violating the unique constraint, 1581).

This error causes the System.Data.SQLite code to throw an exception, terminating the rest of
the SaveChanges step because while SQLite defaults to continuing with the transaction, it does
abort the statement in progress causing the error and neither System.Data.SQLite nor the Entity
Framework has any way of knowing that it should ignore this failure.

Subsequently, the changes up until that point are successfully committed by the transaction scope;
however, the other changes are not.

The only real solution to this problem is changing the ON CONFLICT value to IGNORE;
however, I'm not sure how to get the Entity Framework to add that to its INSERT
statement(s) as I do not know enough about it or even if it is possible to do so.

Unfortunately, there is currently no way to change the default ON CONFLICT value (i.e.
it is always set on a per-statement basis).