System.Data.SQLite

Hi there,

I have noticed an issue where a single error is output reading:
*SQLite error (21): misuse at line 112492 of [ebd01a8def]

The error above is triggered when two methods are asynchronously called in one application connecting to one database concurrently. However the error only occurs under strict conditions surrounding these two methods:

1. A count method:

            int intTempResult = 0;

            using (SQLiteConnection sqldbConnection = new SQLiteConnection(
                ConfigurationManager.ConnectionStrings["EntitiesProductSQLLite"].ToString()))
            {
                string strQuery;

                if (strDescription.Count() != 0)
                {

                    strQuery = "SELECT COUNT([ProductId]) FROM [Product] productsReal, " +
                               "[Virtual_Product_FTS] productsVirtual WHERE " +
                               "Virtual_Product_FTS MATCH '%" + strDescription +
                               "%' AND productsReal.ProductId = productsVirtual.docid";

                }
                else
                {
                    strQuery = "SELECT COUNT([ProductId]) FROM [Product]";
                }

                using (SQLiteCommand cmd = new SQLiteCommand(strQuery, sqldbConnection))
                {

                    cmd.CommandType = CommandType.Text;


                    sqldbConnection.Open();


                    using (SQLiteDataReader reader = cmd.ExecuteReader())
                    {

                        while (reader.Read())
                        {

                            intTempResult = reader.GetInt32(0);

                        }
                    }
                }
            }

2. A get page method:

            sbTemp.Append("SELECT * FROM [Product]");

            sbTemp.Append("ORDER BY [" + strSortColumn + "] " + strSortMode);

            sbTemp.Append(" LIMIT " + intRecordsPerPage + " OFFSET " + intIndex * intRecordsPerPage);


            using (SQLiteConnection sqldbConnection = new SQLiteConnection(
                ConfigurationManager.ConnectionStrings["EntitiesProductSQLLite"].ToString()))
            {

                using (SQLiteCommand sqlCmd = new SQLiteCommand(strCommand.ToString(), sqldbConnection))
                {

                    sqlCmd.CommandType = CommandType.Text;


                    sqldbConnection.Open();


                    using (SQLiteDataReader sqlDR = sqlCmd.ExecuteReader())
                    {

                        while (sqlDR.Read())
                        {

                            dbProducts.Add(Product_DL_SQL_to_BL_Model_Converter(sqlDR));

                        }
                    }

                }
            }

Now these two methods can run concurrently, connect to the database and perform their tasks without causing this error. After they have been called together subsequent calls are made to go through pages or to perform a search with no resulting error, I can debug and watch them both open and read from the database at once. The only condition that reproduces the error consistently is when a tabbed form is used to display this data and a second copy of the form is added after the first one has loaded and finished querying. A third and any other number of forms being added afterward do not ever flag this error. The result is that when the second tab loads up the first thread set running will reach:

(Method get records for paged result)

using("SQLiteDataReader sqlDR = sqlCmd.ExecuteReader()")

At the same time as the second thread reaches:

(Method get total count of records)

"SQLiteCommand cmd = new SQLiteCommand(strQuery, sqldbConnection)" 

Both lines happen at the event of failure despite both connections being separate one of these two lines causes the error message. If I breakpoint on both threads at the point at which they define their connection from the connection string no error ever occurs. So the issue must be one of concurrency based on the timing between them which I have fortunately caught. I am struggling to pin down the root cause as these two methods as you can see use completely separate objects to perform their tasks. I can only hazard a guess that this fault in the underlying library is caused by me not correctly specifying how I want to use concurrency.

This is the standard "sqlite-netFx40-setup-bundle-x86-2010-1.0.79.0" precompiled mixed assembly binary.

To crack this one I think it may be necessary to figure out what happens around line 112492 of [edb01a8def] but this hash appears to reflect a group of files. Can anyone clue me in to either the relevant file or any potential error in how I am handling concurrency around the SQLite library?

Thank you for reading this!

<hr /><i>mistachkin added on 2012-02-28 14:58:53 UTC:</i><br />
This error message is caused by some initialization auto-detection code in the "SQLite3.cs" file.  The message itself is harmless.  Normally, it should not be seen; however, there is a subtle race condition in the code that can cause it to appear.  This issue is technically a bug and will be fixed in the next release (1.0.80.0).  Thanks for the report.


<hr /><i>mistachkin added on 2012-02-28 15:08:50 UTC:</i><br />
Fixed by check-in [fb22170bc8].


<hr /><i>mistachkin added on 2012-02-29 14:58:15 UTC:</i><br />
Still open, pending tests being added.


<hr /><i>mistachkin added on 2012-03-11 06:52:08 UTC:</i><br />
Designed a suitable stress test to expose this issue, see check-in [15e0be2ffb].  This ticket is now closed.