System.Data.SQLite
Artifact [3e7fe221da]
Not logged in

Artifact 3e7fe221dac0320b89c7cd9377c0bed8cdbd1258:

Ticket change [3e7fe221da] - Ticket [ccfa69fc32] Transaction on conflict behave status still Closed with 1 other change by mistachkin 2011-09-12 20:24:25.
D 2011-09-12T20:24:25.451
J comment When\sa\stransaction\sis\srunning\sand\san\serror\soccurs,\sON\sCONFLICT\sclause\snot\srunning\sdefault.\sFor\sexample\swhen\sa\stransaction\swith\s100\srows\s(inserts)\sis\sexecuted\sand\srow\s50\sraise\sa\scontraint\sexception,\sall\srows\sabove\swill\snot\sbe\sexecuted.\r\n\r\nSQLITE\sdoku\swrites\sthat\s"but\schanges\scaused\sby\sprior\sSQL\sstatements\swithin\sthe\ssame\stransaction\sare\spreserved\sand\sthe\stransaction\sremains\sactive".\r\n\r\n<hr\s/><i>mistachkin\sadded\son\s2011-09-11\s13:09:52\sUTC:</i><br\s/>\r\nThis\sissue\sseems\sto\sinvolve\sthe\s.NET\sEntity\sFramework\sand\sI\sam\snot\san\sexpert\son\sthat\spackage;\showever,\sit\sseems\sthat\sthe\sTransactionScope\sclass\sis\snormally\sunnecessary\sunless\syou\shave\schanges\sthat\sspan\smultiple\sobject\scontexts\s(e.g.\smultiple\sSQLite\sdatabases),\ssee:\r\n\r\nhttp://stackoverflow.com/questions/794707/why-doesnt-transactionscope-work-with-entity-framework\r\n\r\n\r\n<hr\s/><i>mistachkin\sadded\son\s2011-09-11\s13:40:00\sUTC:</i><br\s/>\r\nRelated:\shttp://msdn.microsoft.com/en-us/library/bb738523.aspx\r\n\r\n\r\n<hr\s/><i>mistachkin\sadded\son\s2011-09-11\s13:42:27\sUTC:</i><br\s/>\r\nAlso,\sthere\sseems\sto\sbe\sa\sproblem\swith\sthe\sSQLiteConnection.EnlistTransaction\smethod\sthat\sprevents\sTransactionScope\sfrom\sworking\swith\san\sObjectContext\sif\sthe\scode\sinvolved\stries\sto\saccess\sthe\sdatabase\smore\sthan\sonce\sfrom\sinside\sthe\ssame\stransaction.\r\n\r\n<hr\s/><i>mistachkin\sadded\son\s2011-09-11\s15:36:54\sUTC:</i><br\s/>\r\nAn\sexperimental\sfix\sfor\sthis\sissue\sis\savailable\sin\scheck-in\s[f4e1038098],\swith\sunit\stests.\r\n\r\nWould\sit\sbe\spossible\sfor\syou\sto\stest\sthis\sfix\sin\syour\senvironment\sand\sreport\sback\swith\sthe\soutcome\sof\syour\stesting?\r\n\r\n<hr\s/><i>anonymous\sadded\son\s2011-09-12\s08:47:13\sUTC:</i><br\s/>\r\nwith\sthis\spatch\sit\sworks\snow\slike\sexpected.\sThe\stransaction\sraised\sthe\sException\sif\ssome\scommands\sfails,\sbut\sother\scommands\sexecuted\snormal.\r\n\r\n<hr\s/><i>anonymous\sadded\son\s2011-09-12\s11:23:11\sUTC:</i><br\s/>\r\nAfter\stesting\sagain\serror\sstill\sexist.\r\n(preview\scomment\sis\swrong)\r\n\r\n<hr\s/><i>mistachkin\sadded\son\s2011-09-12\s12:45:35\sUTC:</i><br\s/>\r\nDid\syou\sbuild\sthe\snew\sDLLs\sfrom\sthe\ssource\scode\son\sthe\s"bug-ccfa69fc32"\sbranch?\s\sIf\sso,\sare\syou\sseeing\sprecisely\sthe\ssame\sbehavior\sas\sbefore\sor\ssomething\sslightly\sdifferent\snow?\r\n\r\n<hr\s/><i>mistachkin\sadded\son\s2011-09-12\s20:23:38\sUTC:</i><br\s/>\r\nOk,\sI\sthink\sI've\sfigured\sit\sout.\s\sThanks\sfor\syour\shelp\stroubleshooting.\r\n\r\n<verbatim>\r\nAdding\ssome\sdiagnostic\soutput\sto\sthe\sSystem.Data.SQLite\sassembly,\sI\ssee\sthis\soutput\r\n(the\sSQL\sbelow\sis\sbeing\sprepared\sfor\sexecution):\r\n\r\nPRAGMA\slegacy_file_format=OFF\r\n\r\nPRAGMA\ssynchronous=Normal\r\n\r\nPRAGMA\sforeign_keys=OFF\r\n\r\nBEGIN\sIMMEDIATE\r\n\r\nSELECT\r\n[c].[RegionID]\sAS\s[RegionID],\r\n[c].[RegionDescription]\sAS\s[RegionDescription]\r\nFROM\s[Regions]\sAS\s[c]\sLIMIT\s1\r\n\r\nSELECT\r\n[c].[RegionID]\sAS\s[RegionID],\r\n[c].[RegionDescription]\sAS\s[RegionDescription]\r\nFROM\s[Regions]\sAS\s[c]\sLIMIT\s1\r\n\r\nSELECT\r\n[c].[RegionID]\sAS\s[RegionID],\r\n[c].[RegionDescription]\sAS\s[RegionDescription]\r\nFROM\s[Regions]\sAS\s[c]\sLIMIT\s1\r\n\r\nSELECT\r\n[c].[RegionID]\sAS\s[RegionID],\r\n[c].[RegionDescription]\sAS\s[RegionDescription]\r\nFROM\s[Regions]\sAS\s[c]\sLIMIT\s1\r\n\r\nSELECT\r\n[c].[RegionID]\sAS\s[RegionID],\r\n[c].[RegionDescription]\sAS\s[RegionDescription]\r\nFROM\s[Regions]\sAS\s[c]\sLIMIT\s1\r\n\r\nSELECT\r\n[c].[RegionID]\sAS\s[RegionID],\r\n[c].[RegionDescription]\sAS\s[RegionDescription]\r\nFROM\s[Regions]\sAS\s[c]\sLIMIT\s1\r\n\r\nSELECT\r\n[c].[RegionID]\sAS\s[RegionID],\r\n[c].[RegionDescription]\sAS\s[RegionDescription]\r\nFROM\s[Regions]\sAS\s[c]\sLIMIT\s1\r\n\r\nSELECT\r\n[c].[RegionID]\sAS\s[RegionID],\r\n[c].[RegionDescription]\sAS\s[RegionDescription]\r\nFROM\s[Regions]\sAS\s[c]\sLIMIT\s1\r\n\r\nSELECT\r\n[c].[RegionID]\sAS\s[RegionID],\r\n[c].[RegionDescription]\sAS\s[RegionDescription]\r\nFROM\s[Regions]\sAS\s[c]\sLIMIT\s1\r\n\r\nSELECT\r\n[c].[RegionID]\sAS\s[RegionID],\r\n[c].[RegionDescription]\sAS\s[RegionDescription]\r\nFROM\s[Regions]\sAS\s[c]\sLIMIT\s1\r\n\r\nSELECT\r\n[c].[RegionID]\sAS\s[RegionID],\r\n[c].[RegionDescription]\sAS\s[RegionDescription]\r\nFROM\s[Regions]\sAS\s[c]\sLIMIT\s1\r\n\r\nSELECT\r\n[c].[RegionID]\sAS\s[RegionID],\r\n[c].[RegionDescription]\sAS\s[RegionDescription]\r\nFROM\s[Regions]\sAS\s[c]\sLIMIT\s1\r\n\r\nSELECT\r\n[c].[RegionID]\sAS\s[RegionID],\r\n[c].[RegionDescription]\sAS\s[RegionDescription]\r\nFROM\s[Regions]\sAS\s[c]\sLIMIT\s1\r\n\r\nSELECT\r\n[c].[RegionID]\sAS\s[RegionID],\r\n[c].[RegionDescription]\sAS\s[RegionDescription]\r\nFROM\s[Regions]\sAS\s[c]\sLIMIT\s1\r\n\r\nSELECT\r\n[c].[RegionID]\sAS\s[RegionID],\r\n[c].[RegionDescription]\sAS\s[RegionDescription]\r\nFROM\s[Regions]\sAS\s[c]\sLIMIT\s1\r\n\r\nSELECT\r\n[c].[RegionID]\sAS\s[RegionID],\r\n[c].[RegionDescription]\sAS\s[RegionDescription]\r\nFROM\s[Regions]\sAS\s[c]\sLIMIT\s1\r\n\r\nSELECT\r\n[c].[RegionID]\sAS\s[RegionID],\r\n[c].[RegionDescription]\sAS\s[RegionDescription]\r\nFROM\s[Regions]\sAS\s[c]\sLIMIT\s1\r\n\r\nSELECT\r\n[c].[RegionID]\sAS\s[RegionID],\r\n[c].[RegionDescription]\sAS\s[RegionDescription]\r\nFROM\s[Regions]\sAS\s[c]\sLIMIT\s1\r\n\r\nSELECT\r\n[c].[RegionID]\sAS\s[RegionID],\r\n[c].[RegionDescription]\sAS\s[RegionDescription]\r\nFROM\s[Regions]\sAS\s[c]\sLIMIT\s1\r\n\r\nSELECT\r\n[c].[RegionID]\sAS\s[RegionID],\r\n[c].[RegionDescription]\sAS\s[RegionDescription]\r\nFROM\s[Regions]\sAS\s[c]\sLIMIT\s1\r\n\r\nSELECT\r\n[c].[RegionID]\sAS\s[RegionID],\r\n[c].[RegionDescription]\sAS\s[RegionDescription]\r\nFROM\s[Regions]\sAS\s[c]\sLIMIT\s1\r\n\r\nSELECT\r\n[c].[RegionID]\sAS\s[RegionID],\r\n[c].[RegionDescription]\sAS\s[RegionDescription]\r\nFROM\s[Regions]\sAS\s[c]\sLIMIT\s1\r\n\r\nINSERT\sINTO\s[Territories]([TerritoryID],\s[TerritoryDescription],\s[RegionID])\r\n\sVALUES\s(@p0,\s@p1,\s@p2);\r\n\r\nINSERT\sINTO\s[Territories]([TerritoryID],\s[TerritoryDescription],\s[RegionID])\r\n\sVALUES\s(@p0,\s@p1,\s@p2);\r\n\r\nINSERT\sINTO\s[Territories]([TerritoryID],\s[TerritoryDescription],\s[RegionID])\r\n\sVALUES\s(@p0,\s@p1,\s@p2);\r\n\r\nINSERT\sINTO\s[Territories]([TerritoryID],\s[TerritoryDescription],\s[RegionID])\r\n\sVALUES\s(@p0,\s@p1,\s@p2);\r\n\r\nINSERT\sINTO\s[Territories]([TerritoryID],\s[TerritoryDescription],\s[RegionID])\r\n\sVALUES\s(@p0,\s@p1,\s@p2);\r\n\r\nINSERT\sINTO\s[Territories]([TerritoryID],\s[TerritoryDescription],\s[RegionID])\r\n\sVALUES\s(@p0,\s@p1,\s@p2);\r\n\r\nINSERT\sINTO\s[Territories]([TerritoryID],\s[TerritoryDescription],\s[RegionID])\r\n\sVALUES\s(@p0,\s@p1,\s@p2);\r\n\r\nINSERT\sINTO\s[Territories]([TerritoryID],\s[TerritoryDescription],\s[RegionID])\r\n\sVALUES\s(@p0,\s@p1,\s@p2);\r\n\r\nINSERT\sINTO\s[Territories]([TerritoryID],\s[TerritoryDescription],\s[RegionID])\r\n\sVALUES\s(@p0,\s@p1,\s@p2);\r\n\r\nINSERT\sINTO\s[Territories]([TerritoryID],\s[TerritoryDescription],\s[RegionID])\r\n\sVALUES\s(@p0,\s@p1,\s@p2);\r\n\r\nINSERT\sINTO\s[Territories]([TerritoryID],\s[TerritoryDescription],\s[RegionID])\r\n\sVALUES\s(@p0,\s@p1,\s@p2);\r\n\r\nINSERT\sINTO\s[Territories]([TerritoryID],\s[TerritoryDescription],\s[RegionID])\r\n\sVALUES\s(@p0,\s@p1,\s@p2);\r\n\r\nINSERT\sINTO\s[Territories]([TerritoryID],\s[TerritoryDescription],\s[RegionID])\r\n\sVALUES\s(@p0,\s@p1,\s@p2);\r\n\r\nINSERT\sINTO\s[Territories]([TerritoryID],\s[TerritoryDescription],\s[RegionID])\r\n\sVALUES\s(@p0,\s@p1,\s@p2);\r\n\r\nINSERT\sINTO\s[Territories]([TerritoryID],\s[TerritoryDescription],\s[RegionID])\r\n\sVALUES\s(@p0,\s@p1,\s@p2);\r\n\r\nINSERT\sINTO\s[Territories]([TerritoryID],\s[TerritoryDescription],\s[RegionID])\r\n\sVALUES\s(@p0,\s@p1,\s@p2);\r\n\r\nSQLite\serror\s(19):\sabort\sat\s13\sin\s[INSERT\sINTO\s[Territories]([TerritoryID],\s[TerritoryDescription],\s[RegionID])\r\n\sVALUES\s(@p0,\s@p1,\s@p2);]:\sPRIMARY\sKEY\smust\sbe\sunique\r\n\r\nA\sfirst\schance\sexception\sof\stype\s'System.Data.SQLite.SQLiteException'\soccurred\sin\sSystem.Data.SQLite.dll\r\nA\sfirst\schance\sexception\sof\stype\s'System.Data.UpdateException'\soccurred\sin\sSystem.Data.Entity.dll\r\n\r\nCOMMIT\r\n\r\nThe\sprogram\s'[6896]\stestlinq.exe:\sProgram\sTrace'\shas\sexited\swith\scode\s0\s(0x0).\r\nThe\sprogram\s'[6896]\stestlinq.exe:\sManaged\s(v4.0.30319)'\shas\sexited\swith\scode\s0\s(0x0).\s\r\n\r\nThe\sabove\swas\sproduced\susing\smy\stest\scode\swith\sthe\slist\sof\sIDs\smodified\sto\sinclude\sthe\r\nones\syou\sadded,\sas\sfollows:\r\n\r\n\s\s\s\s\s\s\s\s\s\slong[]\sterritoryIds\s=\snew\slong[]\s{\r\n\s\s\s\s\s\s\s\s\s\s\s\s\s\s1576,\s1577,\s1578,\s1579,\s1580,\s//\sNOTE:\sSuccess\r\n\s\s\s\s\s\s\s\s\s\s\s\s\s\s1581,\s1730,\s1833,\s2116,\s2139,\s//\sNOTE:\sFail\r\n\s\s\s\s\s\s\s\s\s\s\s\s\s\s2140,\s2141\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s//\sNOTE:\sSkipped\r\n\s\s\s\s\s\s\s\s\s\s};\r\n\r\nWhat\shappens\sdeep\sdown\sis\sthat\sSQLite\sis\sreturning\san\serror\scode\s(19\sor\sSQLITE_CONSTRAINT)\sfrom\r\nthe\ssqlite3_step()\snative\sfunction\swhen\sattempting\sto\sINSERT\sthe\s16th\srow\sduring\sthe\sSaveChanges\r\nstep\s(i.e.\sthe\sfirst\sID\sviolating\sthe\sunique\sconstraint,\s1581).\r\n\r\nThis\serror\scauses\sthe\sSystem.Data.SQLite\scode\sto\sthrow\san\sexception,\sterminating\sthe\srest\sof\r\nthe\sSaveChanges\sstep\sbecause\swhile\sSQLite\sdefaults\sto\scontinuing\swith\sthe\stransaction,\sit\sdoes\r\nabort\sthe\sstatement\sin\sprogress\scausing\sthe\serror\sand\sneither\sSystem.Data.SQLite\snor\sthe\sEntity\r\nFramework\shas\sany\sway\sof\sknowing\sthat\sit\sshould\signore\sthis\sfailure.\r\n\r\nSubsequently,\sthe\schanges\sup\suntil\sthat\spoint\sare\ssuccessfully\scommitted\sby\sthe\stransaction\sscope;\r\nhowever,\sthe\sother\schanges\sare\snot.\r\n\r\nThe\sonly\sreal\ssolution\sto\sthis\sproblem\sis\schanging\sthe\sON\sCONFLICT\svalue\sto\sIGNORE;\r\nhowever,\sI'm\snot\ssure\show\sto\sget\sthe\sEntity\sFramework\sto\sadd\sthat\sto\sits\sINSERT\r\nstatement(s)\sas\sI\sdo\snot\sknow\senough\sabout\sit\sor\seven\sif\sit\sis\spossible\sto\sdo\sso.\r\n\r\nUnfortunately,\sthere\sis\scurrently\sno\sway\sto\schange\sthe\sdefault\sON\sCONFLICT\svalue\s(i.e.\r\nit\sis\salways\sset\son\sa\sper-statement\sbasis).\r\n</verbatim>
K ccfa69fc3251fb3512e9f17eca7a4c8fab6302e1
U mistachkin
Z dabdf96a3e23be0585cbf38a2f7235cf