SQLite.Net-PCL
SQLite.Net-PCL copied to clipboard
oysteinkrog
savePoint is not valid, and should be the result of a call to SaveTransactionPoint
I got the following exception:
System.ArgumentException was unhandled by user code
HResult=-2147024809
Message=savePoint is not valid, and should be the result of a call to SaveTransactionPoint.
Parameter name: savePoint
ParamName=savePoint
Source=SQLite.Net
StackTrace:
at SQLite.Net.SQLiteConnection.DoSavePointExecute(String savePoint, String cmd)
at SQLite.Net.SQLiteConnection.RunInTransaction(Action action)
at SQLite.Net.SQLiteConnection.InsertOrReplaceAll(IEnumerable objects)
===
some class methods in my project that call dbConnection.InsertOrReplaceAll()
===
at System.Threading.Tasks.Task.InnerInvoke()
at System.Threading.Tasks.Task.Execute()
InnerException:
My scenario is nearly the same as mentioned by wegascee mentioned in issue #308
I have 3 tables that have different structures. Each has their own model class for insertion and retrieval.
I have several async tasks that have combinations of different data objects to be inserted in the said 3 tables. Also, I have async tasks that try to fetch data from the said 3 tables. They may trigger in any order.
Scenario 1:
Each table has its own insert and retrieve method, with some conditions to sanitize data. I run into this exception soon enough.
Scenario 2:
I tried using locks in the insert methods. Verified that the task threadIds are different. Runs fine for some time. But when the operating tasks grow in number and more threads are created, I encounter this exception.
Scenario 3:
I scrapped the locks mechanism and instead tried using separate concurrentQueue objects for each table, in a producer-consumer pattern. My async tasks push the data to the queues, and consumer loops, separate for each table, run, dequeing the data and inserting them to the db. This loop runs in a separate task. I verified that there is only one such loop running for a table at any time. I still run into this exception soon enough.
I also have another exception sometimes: "cannot rollback - no transaction is active", but I have yet to identify that scenario.
In all the scenarios, I'm using a single connection object, spawned by using the normal constructor. "SQLiteConnection(platform, dbLocation)"
Is there way you can create a test that demonstrates this problem? I'm not really surprised that it happens as the locking is really not safe, but I've yet to see it fail myself.
The following class reproduces the problem:
class TestSQLite
{
private static string dbLocation = Path.Combine(Windows.Storage.ApplicationData.Current.LocalCacheFolder.Path, "testdb.sqlite");
private static SQLiteConnection dbConnection;
private Random random;
public TestSQLite()
{
dbConnection = new SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), dbLocation);
createTables();
random = new Random();
}
private void createTables()
{
dbConnection.CreateTable<ItemTypeA>();
dbConnection.CreateTable<ItemTypeB>();
dbConnection.CreateTable<ItemTypeC>();
}
public Dictionary<string, ItemTypeA> getItemAs(List<string> ids)
{
HashSet<string> hashes = new HashSet<string>(ids);
var fetchedRows = dbConnection.Table<ItemTypeA>().Where(item => hashes.Contains(item.id));
Dictionary<string, ItemTypeA> resultsMap = new Dictionary<string, ItemTypeA>();
foreach(ItemTypeA item in fetchedRows)
{
resultsMap[item.id] = item;
}
return resultsMap;
}
public void putItemAs(List<ItemTypeA> items)
{
// The InsertOrReplaceAll statement causes the said exception
dbConnection.InsertOrReplaceAll(items);
// Individual inserts are working fine though
/*
foreach (ItemTypeA item in items)
{
dbConnection.InsertOrReplace(item);
}
*/
}
public Dictionary<string, ItemTypeB> getItemBs(List<string> ids)
{
HashSet<string> hashes = new HashSet<string>(ids);
var fetchedRows = dbConnection.Table<ItemTypeB>().Where(item => hashes.Contains(item.id));
Dictionary<string, ItemTypeB> resultsMap = new Dictionary<string, ItemTypeB>();
foreach (ItemTypeB item in fetchedRows)
{
resultsMap[item.id] = item;
}
return resultsMap;
}
public void putItemBs(List<ItemTypeB> items)
{
// The InsertOrReplaceAll statement causes the said exception
dbConnection.InsertOrReplaceAll(items);
// Individual inserts are working fine though
/*
foreach (ItemTypeB item in items)
{
dbConnection.InsertOrReplace(item);
}
*/
}
public Dictionary<string, ItemTypeC> getItemCs(List<string> ids)
{
HashSet<string> hashes = new HashSet<string>(ids);
var fetchedRows = dbConnection.Table<ItemTypeC>().Where(item => hashes.Contains(item.id));
Dictionary<string, ItemTypeC> resultsMap = new Dictionary<string, ItemTypeC>();
foreach (ItemTypeC item in fetchedRows)
{
resultsMap[item.id] = item;
}
return resultsMap;
}
public void putItemCs(List<ItemTypeC> items)
{
// The InsertOrReplaceAll statement causes the said exception
dbConnection.InsertOrReplaceAll(items);
// Individual inserts are working fine though
/*
foreach (ItemTypeC item in items)
{
dbConnection.InsertOrReplace(item);
}
*/
}
private string generateRandomString(int length)
{
string charset = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
return new string(Enumerable.Repeat(charset, length).Select(s => s[random.Next(s.Length)]).ToArray());
}
private ItemTypeA generateRandomItemA()
{
char id = (char)random.Next(97, 100);
string valA = generateRandomString(1000);
long valB = DateTime.Now.Ticks / TimeSpan.TicksPerMillisecond;
string valC = generateRandomString(1000);
return new ItemTypeA(id.ToString(), valA, valB, valC); ;
}
private ItemTypeB generateRandomItemB()
{
char id = (char)random.Next(97, 100);
string valA = generateRandomString(1000);
long valB = DateTime.Now.Ticks / TimeSpan.TicksPerMillisecond;
string valC = generateRandomString(1000);
return new ItemTypeB(id.ToString(), valA, valB, valC);
}
private ItemTypeC generateRandomItemC()
{
char id = (char)random.Next(97, 100);
string valA = generateRandomString(1000);
long valB = DateTime.Now.Ticks / TimeSpan.TicksPerMillisecond;
string valC = generateRandomString(1000);
return new ItemTypeC(id.ToString(), valA, valB, valC);
}
public void doRandomTesting()
{
Random random = new Random();
// runs a number of reader tasks
Task.Run(() => {
for(int index = 0; index < 100; index++)
{
Task.Run(() => {
List<string> ids = new List<string>();
for (int index1 = 0; index1 < 10; index1++)
{
char id = (char)random.Next(97, 100);
ids.Add(id.ToString());
}
getItemAs(ids);
Debug.WriteLine("Finished an iteration of getting itemAs");
});
Task.Run(() => {
List<string> ids = new List<string>();
for (int index1 = 0; index1 < 10; index1++)
{
char id = (char)random.Next(97, 100);
ids.Add(id.ToString());
}
getItemBs(ids);
Debug.WriteLine("Finished an iteration of getting itemBs");
});
Task.Run(() => {
List<string> ids = new List<string>();
for (int index1 = 0; index1 < 10; index1++)
{
char id = (char)random.Next(97, 100);
ids.Add(id.ToString());
}
getItemCs(ids);
Debug.WriteLine("Finished an iteration of getting itemCs");
});
}
});
// runs a number of writer tasks
Task.Run(() => {
for (int index = 0; index < 100; index++)
{
Task.Run(() => {
List<ItemTypeA> items = new List<ItemTypeA>();
for(int index1 = 0; index1 < 10; index1++)
{
items.Add(generateRandomItemA());
}
putItemAs(items);
Debug.WriteLine("Finished an iteration of putting itemAs");
});
Task.Run(() => {
List<ItemTypeB> items = new List<ItemTypeB>();
for (int index1 = 0; index1 < 10; index1++)
{
items.Add(generateRandomItemB());
}
putItemBs(items);
Debug.WriteLine("Finished an iteration of putting itemBs");
});
Task.Run(() => {
List<ItemTypeC> items = new List<ItemTypeC>();
for (int index1 = 0; index1 < 10; index1++)
{
items.Add(generateRandomItemC());
}
putItemCs(items);
Debug.WriteLine("Finished an iteration of putting itemCs");
});
}
});
}
}
class ItemTypeA
{
[PrimaryKey, Unique]
public string id { get; set; }
public string valueA { get; set; }
public long valueB { get; set; }
public string valueC { get; set; }
public ItemTypeA() { }
public ItemTypeA(string val1, string val2, long val3, string val4)
{
id = val1; valueA = val2; valueB = val3; valueC = val4;
}
}
class ItemTypeB
{
[PrimaryKey, Unique]
public string id { get; set; }
public string valueA { get; set; }
public long valueB { get; set; }
public string valueC { get; set; }
public ItemTypeB() { }
public ItemTypeB(string val1, string val2, long val3, string val4)
{
id = val1; valueA = val2; valueB = val3; valueC = val4;
}
}
class ItemTypeC
{
[PrimaryKey, Unique]
public string id { get; set; }
public string valueA { get; set; }
public long valueB { get; set; }
public string valueC { get; set; }
public ItemTypeC() { }
public ItemTypeC(string val1, string val2, long val3, string val4)
{
id = val1; valueA = val2; valueB = val3; valueC = val4;
}
}
Usage is as follows:
TestSQLite test = new TestSQLite();
test.doRandomTesting();
An observation is that calling InsertOrReplaceAll(items) causes the said exception, while looping over the items and doing individual inserts works fine. I'm under the assumption that batch inserts are more performant than individual inserts.
Another scenario I observed is that if I successfully run the testing job with individual inserts and then run the test again with batch inserts, I get the following exception, but not every time:
SQLite.Net.SQLiteException was unhandled by user code
HResult=-2146233088
Message=not an error
Source=SQLite.Net
StackTrace:
at SQLite.Net.SQLiteCommand.ExecuteNonQuery()
at SQLite.Net.SQLiteConnection.Execute(String query, Object[] args)
at SQLite.Net.SQLiteConnection.DoSavePointExecute(String savePoint, String cmd)
at SQLite.Net.SQLiteConnection.RunInTransaction(Action action)
at SQLite.Net.SQLiteConnection.InsertOrReplaceAll(IEnumerable objects)
at SQLiteTestApp.TestSQLite.putItemAs(List`1 items)
at SQLiteTestApp.TestSQLite.<doRandomTesting>b__15_5()
at System.Threading.Tasks.Task.InnerInvoke()
at System.Threading.Tasks.Task.Execute()
InnerException:
I'm experiencing a similar issue, again with threading (I have around 10 tables which fetch async from the web and may arrive in any order, which my tasks then use to update the database). I've had a lot of sqlite issues getting it running, and ended up implementing semaphore slim's as async locks in the hope that would resolve the issue.
It had until I made some changes to the thread management last week and I'm now recieving the same error message from the UpdateAll method.
To resolve it in the short term I have turned the runInTransaction flag to false
It's not even always a real locking issue that cause this, but a more fundamental 'issue'. The RunInTransaction uses sqlite savepoints internally. Sqlite savepoints can be nested, so it's allowed to set them as follows:
- set savepoint A
- set savepoint B
- release savepoint B
- release savepoint A
but with multiple threads, you can have different simultaneous calls, so that savepoints might not be properly nested:
- set savepoint A from thread1
- set savepoint B from thread2
- release savepoint A from thread1 => releases savepoint A and nested savepoints within savepoint A
- release savepoint B from thread2 => causes the mentioned exception, because sqlite considers all nested savepoints to be final when (one of) the root savepoint(s) is released. At this point savepoint B is already supposed to be released
See also: https://www.sqlite.org/lang_savepoint.html for more savepoint details
