SQLite Forum

How to ReadUncommitted for c# .net framework 4.5
Login

How to ReadUncommitted for c# .net framework 4.5

(1) By anonymous on 2021-06-17 14:02:05 [link] [source]

I'm trying to ReadUncommitted

.Net Framework 4.5
Sqlite 1.0.114.0

Please help me to correct following unit-test
Following unit test failing with error "no such table: Message"
        [TestMethod]
        public void Test_Read_Uncommited()
        {
            var connectionString = $"Data Source ={_dbFile}; Cache = Shared; read_uncommitted = true;";
            using (var firstConnection = new SQLiteConnection(connectionString))
            {
                try
                {
                    firstConnection.Open();                            
                    using (var firstTransaction = firstConnection.BeginTransaction())
                    {
                        string message = "Hello World!";
                        var updateCommand = firstConnection.CreateCommand();
                        updateCommand.CommandText = "CREATE TABLE Message(Text TEXT); ";
                        updateCommand.CommandText += $"INSERT INTO Message ( Text ) VALUES ( '{message}' ); ";
                        updateCommand.ExecuteNonQuery();
                        using (var secondConnection = new SQLiteConnection(connectionString))
                        {
                            try
                            {
                                secondConnection.Open();
                                //using (var secondTransaction = secondConnection.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted))
                                {
                                    var queryCommand = secondConnection.CreateCommand();
                                    queryCommand.CommandText = "SELECT Text FROM Message;";
                                    var messageActual = (string)queryCommand.ExecuteScalar();
                                    Assert.IsTrue(string.CompareOrdinal(message, messageActual) == 0);
                                }
                            }
                            finally
                            {
                                secondConnection.Close();
                            }
                            firstTransaction.Rollback();
                        }
                    }    
                }
                finally
                {
                    firstConnection.Close();
                }         
            }
        }

(2) By Gunter Hick (gunter_hick) on 2021-06-17 14:29:49 in reply to 1 [link] [source]

Try creating the table in a separate transaction. I have no idea what the wrapper you are using may be doing transactionwise if you have multiple statements in one command.

(3) By anonymous on 2021-06-17 14:49:46 in reply to 2 [link] [source]

Adding table is not issue.Insert don't have issue. Case is failing on read
  var messageActual = (string)queryCommand.ExecuteScalar();

Here is updated test case

        public void Test_Read_Uncommited()
        {
            var connectionString = $"Data Source ={_dbFile}; Cache = Shared; read_uncommitted = true;";
            using (var firstConnection = new SQLiteConnection(connectionString))
            {
                try
                {
                    firstConnection.Open();                            
                    using (var firstTransaction = firstConnection.BeginTransaction())
                    {
                        string message = "Hello World!";
                        using (var createTable = firstConnection.CreateCommand())
                        {
                            createTable.CommandText = "CREATE TABLE Message(Text TEXT) ";
                            createTable.ExecuteNonQuery();
                        }
                        using (var insertRow = firstConnection.CreateCommand())
                        {
                            insertRow.CommandText = $"INSERT INTO Message ( Text ) VALUES ( '{message}' ); ";
                            insertRow.ExecuteNonQuery();
                        }
                        using (var secondConnection = new SQLiteConnection(connectionString))
                        {
                            try
                            {
                                secondConnection.Open();
                                //using (var secondTransaction = secondConnection.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted))
                                {
                                    var queryCommand = secondConnection.CreateCommand();
                                    queryCommand.CommandText = "SELECT Text FROM Message;";
                                    var messageActual = (string)queryCommand.ExecuteScalar();
                                    Assert.IsTrue(string.CompareOrdinal(message, messageActual) == 0);
                                }
                            }
                            finally
                            {
                                secondConnection.Close();
                            }
                            firstTransaction.Rollback();
                        }
                    }    
                }
                finally
                {
                    firstConnection.Close();
                }         
            }
        }

(4) By Gunter Hick (gunter_hick) on 2021-06-18 06:22:26 in reply to 3 [link] [source]

That is NOT what I suggested, and it obviously does not change what is going on.

To be more explicit: Be sure to COMMIT the CREATE TABLE statement on the first connection BEFORE attempting to open the second connection.

(5) By anonymous on 2021-06-19 14:51:41 in reply to 4 [link] [source]

Intention of the test is to validate following statement at  https://sqlite.org/isolation.html which says

"If two database connections shared the same cache and the reader has enabled the read_uncommitted pragma, then the reader will be able to see changes made by the writer before the writer transaction commits. The combined use of shared cache mode and the read_uncommitted pragma is the only way that one database connection can see uncommitted changes on a different database connection. In all other circumstances, separate database connections are completely isolated from one another."


However; unit test is failing. Seeking help to know if any change to unit test required to confirm the feature

(6) By mistachkin on 2021-06-19 16:05:59 in reply to 5 [source]

System.Data.SQLite does not currently have support for shared cache mode;
therefore, using PRAGMA read_uncommitted will have no effect.

(7) By anonymous on 2021-06-20 14:09:20 in reply to 6 [link] [source]

What options available for .net to achieve read_uncommitted for multi connections ?