System.Data.SQLite

Login
This project makes use of Eagle, provided by Mistachkin Systems.
Eagle: Secure Software Automation
Overview

Artifact ID: 99569dc0add71ad9d9f94d8c78bf85f029318a17
Ticket: ccfa69fc3251fb3512e9f17eca7a4c8fab6302e1
Transaction on conflict behave
User & Date: mistachkin 2011-09-12 20:41:22
Changes

  1. comment changed to:
    When a transaction is running and an error occurs, ON CONFLICT clause not running default. For example when a transaction with 100 rows (inserts) is executed and row 50 raise a contraint exception, all rows above will not be executed.
    
    SQLITE doku writes that "but changes caused by prior SQL statements within the same transaction are preserved and the transaction remains active".
    
    <hr /><i>mistachkin added on 2011-09-11 13:09:52 UTC:</i><br />
    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
    
    
    <hr /><i>mistachkin added on 2011-09-11 13:40:00 UTC:</i><br />
    Related: http://msdn.microsoft.com/en-us/library/bb738523.aspx
    
    
    <hr /><i>mistachkin added on 2011-09-11 13:42:27 UTC:</i><br />
    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.
    
    <hr /><i>mistachkin added on 2011-09-11 15:36:54 UTC:</i><br />
    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?
    
    <hr /><i>anonymous added on 2011-09-12 08:47:13 UTC:</i><br />
    with this patch it works now like expected. The transaction raised the Exception if some commands fails, but other commands executed normal.
    
    <hr /><i>anonymous added on 2011-09-12 11:23:11 UTC:</i><br />
    After testing again error still exist.
    (preview comment is wrong)
    
    <hr /><i>mistachkin added on 2011-09-12 12:45:35 UTC:</i><br />
    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?
    
    <hr /><i>mistachkin added on 2011-09-12 20:23:38 UTC:</i><br />
    <verbatim>
    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).
    </verbatim>