none
reading data during a transaction returns null RRS feed

  • Question

  • With SQL Server 2005 i have the following problem:

    - i begin a transaction (with ReadCommitted as IsolationLevel)

    - during the transaction (before committing it) i try to read some data (with ExecuteScalar or with ExecuteReader) passing to the OleDbCommand constructor the OleDbTransaction object and so using the same OleDbConnection of the transaction.

    - even if in the database there are records which should be found from the query the OleDbCommand returns NULL, so it does not find any records (there are no Exception, only null as return value).
    if i execute the same query from the Management Studio it returns records.
    moreover, if i execute the OleDbCommand using "null" as OleDbTransaction (so using another OleDbConnection to db) it return records. this happens even if i try to read tables which are not modified by the transaction.

    so, if i read using the transaction it does not find any record, if i read outside the transaction (but with the transaction not yet committed) it finds the right records.

    i would have expected the exactly opposite behaviour.

    i hope my explanation is clear. does anyone have some hints to resolve this problem?


    Monday, March 9, 2009 8:58 AM

All replies

  • Hello

    Do you have a small repro for the behvior you are observing? I tried the following code and was able to successfully read the data.

                string connectionString = "Provider=SQLOLEDB;Data Source=.;Initial Catalog=oldbtest;Integrated Security=SSPI";
                OleDbConnection connection = new OleDbConnection(connectionString);

                using (connection)
                {
                    connection.Open();
                    // setup the database
                    string setupCommandString = @"if not exists (select * from sys.tables where name = 'test')
                                begin
                                create table test
                                (id int primary key,
                                name nvarchar(100));

                                insert into test
                                values( 1, N'value');
                                end";
                    OleDbCommand setupCommand = new OleDbCommand(setupCommandString, connection);
                    setupCommand.ExecuteNonQuery();

                    OleDbTransaction transaction = connection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
                    OleDbCommand command = new OleDbCommand("select * from test", connection, transaction);
                    object value = command.ExecuteScalar();
                    if (value == null)
                    {
                        Console.WriteLine("Failed");
                    }
                    else
                    {
                        Console.WriteLine("Pass");
                    }

                    // commit the transaction
                    transaction.Commit();
                }


    Please remember to mark the replies as answers if they help you.
    Tuesday, March 10, 2009 4:35 AM
  • Hi d.pav,

    Could you clarify how you create the transaction and what happens in the transaction before you try to read the data? Providing sample code which reproduces the problem would allow us to help you much easier. Also, which provider (i.e. SQLOLEDB) are you using, and what connection options? Remember to remove any sensitive information (e.g. passwords) from your code.

    Good luck,
    Jared

    Monday, March 16, 2009 8:54 PM