Hi Everyone,

Sorry for the strange subject, but the situation I'm facing is so odd that I wasn't even sure how to summarize it. First, a little context: I'm using v1.0.104 of System.Data.SQLite.Core in my C#/.NET 4.5 project. I have a table that is created as follows:

                CREATE TABLE messages_priority (
                    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
                    type TEXT,
                    json TEXT,
                    options TEXT,
                    created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL)

The purpose of my SQLite db is to locally queue messages until they can be sent to a web API, at which time they are deleted. My single consumer thread basically does a "SELECT * FROM messages_priority ORDER BY id LIMIT 1" and then if the send to the web is successful it deletes that id. This has all been working fine for months until I had one client hit a problem yesterday. They kept sending the same message over and over to the web and would never move on to anything else. Once I dug in further this is what I found:

(Note that I use a password on the database and I have not been able to find any db viewers for Windows which support the type of encryption that Windows SQLite uses, so I had to write my own simple query tool, the output of which is shown below)

select id,created from messages_priority order by id limit 5
id    created
424    2/12/2017 8:07:10 PM
423    2/12/2017 8:07:09 PM
425    2/12/2017 8:07:11 PM
426    2/12/2017 8:07:12 PM
427    2/12/2017 8:07:13 PM
**5 rows returned**

// So here we see the first problem. 424 is listed before 423 even though I'm specifying an order by id.

select id,created from messages_priority where id = 424
**No Rows**

// Now we see the next problem... even though 424 comes back in the first query, when I try to specify it in a WHERE clause it is not found.

select id,created from messages_priority where id = 423
id    created
423    2/12/2017 8:07:09 PM
**1 rows returned**

// 423, however, returns just fine that way...

delete from messages_priority where id = 424
**Command returned 0**

// delete doesn't throw an exception or anything, but indicates nothing was deleted

select id,created from messages_priority order by id limit 1
id    created
424    2/12/2017 8:07:10 PM
**1 rows returned**

// still shows up in a select with no WHERE

delete from messages_priority where id = 423
**Command returned 1**

// attempt to delete 423 is successful

select id,created from messages_priority where id = 424
id    created
424    2/12/2017 8:07:10 PM
**1 rows returned**

// now I can finally query by the id!

delete from messages_priority where id = 424
**Command returned 1**

// and delete it!

So somehow, records 423 and 424 seemed to have gotten "out of order" in the database, and until 423 was deleted, nothing could directly find 424. Because 424 could never be deleted my consumer kept getting it over and over and could never move on. That makes sense at least...

But I can't imagine how I can work around this in code once the database has gotten into this state. Does this seem like something that could be caused by improper inserts or is this possibly a bug in SQLite or the C# wrapper? I don't do anything to change the defaults in terms of concurrency or isolation settings. My understanding from the docs was that this should be thread safe without any extra locking on my end. Each thread that uses the db has its own connection var and re-uses that over and over. The producer and consumer are separate threads but there is only one of each.

This is my code for inserting the rows:

// use a transaction so we are sure to get the correct row id back
            using (var transaction = sqlConnection.BeginTransaction())
            {
                try
                {
var sql = String.Format("INSERT INTO messages_priority (type, json, options) VALUES (@p0, @p1, @p2)"); using (var command = new SQLiteCommand(sql, sqlConnection))
                    {
command.Parameters.Add(new SQLiteParameter("@p0", type)); command.Parameters.Add(new SQLiteParameter("@p1", json)); command.Parameters.Add(new SQLiteParameter("@p2", options));

                        command.ExecuteNonQuery();
                    }

                    var rowId = sqlConnection.LastInsertRowId;

                    transaction.Commit();

                    return rowId;
                }
                catch
                {
// technically shouldn't be anything to roll back as we are only doing one thing and if that threw
                    // an exception then nothing happened...
                    transaction.Rollback();
                    throw;
                }
            }

I'm pretty much stumped here, any thoughts would be greatly appreciated!

Thanks,
Jeff
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to