How to ReadUncommitted for c# .net framework 4.5
(1) By anonymous on 2021-06-17 14:02:05 [link]
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]
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]
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]
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]
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 [link]
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
What options available for .net to achieve read_uncommitted for multi connections ?