Table lockout

2004-06-07
2004-06-15
  • Nobody/Anonymous

    Does it seem reasonable that a table would remain locked after you do a SELECT on it, then open a reader, do the read then close the reader? I'm running into that situation. Here is the code:

    string viewItem = comboBox8.Text;

    // Populate the "tsb header" combo box
    IDbCommand Cmd = Conn.CreateCommand();
    Cmd.CommandText = "SELECT * FROM tsb WHERE tsbId = (?)";
    IDbDataParameter p = Cmd.CreateParameter();
    p.Value = viewItem.ToString();
    Cmd.Parameters.Add(p);

    IDataReader reader = Cmd.ExecuteReader();

    if(reader.Read())
    {
        string data = (string)reader[1];
    }
       
    reader.Close();
    reader.Dispose();

    At a later point, code that tries to do a SELECT on the same table gets a "table is locked" exception.

    Anyone have any suggestions?

    Thanks,

    H Keller

     
    • Nobody/Anonymous

      Also, The connection was created elsewhere.

       
    • Nobody/Anonymous

      Intead of saying:

        string data = (string)reader[1];

      why not say:

      string data = Read.GetString(1);

       
    • Alexander Gavrilov

      The adapter does definitely work with sequential readers provided they were closed appropriatey. The exception you're getting could be caused by the access of the same table from two parallel threads or processes. Specifically, this exception will occur if one thread tries to insert or delete some rows from table whereas the other thread is reading the same table.

       
    • Nobody/Anonymous

      Thanks for the suggestion about using
      string data = Read.GetString(1);
      However, I get a syntax error using that syntax. The compiler can't find 'Read' in the namespace.

       
    • Wilson Chan

      Wilson Chan - 2004-06-09

      the "Read" is a declared reader object, u just declared it and use it, it IS not a namespace!

       
    • Nobody/Anonymous

      Alexander,
      Interesting thought. In this case, the above code is executed when a "selecteIndexChanged" event occurs in a combo box. Any additional code execution using a table is the result of some other button action, so I'm having a bit of a time rationalizing the possability of another thread executing and trying to simultaneously access the same table. If I comment out the code that opens and uses the reader, I don't have a problem. Of course that doesn't prove any thing does it.

      Also, the code that gets the "locked" exception (when the appropriate button is depressed) is an UPDATE to the table using a DataAdapter.

       
    • Nobody/Anonymous

      Wilson,

      Actually in this case the compilier did complain in that manor. But also in this case, I should have used string data = reader.GetString(1); which does work. I took the comment out of context.

       
    • Anonymous - 2004-06-09

      Alexander,

      I just tried an experiment. I moved the above code to immediately above the code getting the "locked" exception. I still get the exception in the int result = adapter.Update(dataset);call.

      The complete code is:private void button21_Click(object sender, System.EventArgs e)
      {
           string viewItem = comboBox8.Text;

           // Populate the "tsb header" combo box
           IDbCommand Cmd = Conn.CreateCommand();

           Cmd.CommandText = "SELECT FROM tsb          WHERE tsbId = (?)";
           IDbDataParameter p = Cmd.CreateParameter();
           p.Value = viewItem.ToString();
           Cmd.Parameters.Add(p);

           IDataReader reader = Cmd.ExecuteReader();

           if(reader.Read())
          {
                //string data = (string)reader[1];
                string data = reader.GetString(1);

          }

          reader.Close();
          reader.Dispose();

          // tsb pb edit data entry
          // first get data from "tsb - TSB Number"      combobox
          string newItem = comboBox8.Text;
          if(comboBox8.Text == "Enter Data" ||
          comboBox8.Text.Trim() == "")        // all spaces
          {
                // no new data was entered
               MessageBox.Show("No new \"TSB\" data was entered !");
          }
          else
          {
               string newHeader = textBox1.Text;
               string newBody = textBox2.Text;
               comboBox8.Text = "Enter Data";
               textBox1.Text = "Enter Data";
               textBox2.Text = "Enter Data";

               // add the newItem, newHeader and newBody to the TSB
               // database
              SQLiteDataAdapter adapter = new SQLiteDataAdapter();
              adapter.SelectCommand = Conn.CreateCommand();                       adapter.SelectCommand.CommandText =
                          "SELECT tsbId, tsbHeader, tsbBody FROM tsb";
                     adapter.UpdateCommand = Conn.CreateCommand();
                     adapter.UpdateCommand.CommandText =
                           "UPDATE tsb SET tsbHeader=@tsbHeader, tsbBody=@tsbBody WHERE tsbId=@tsbId";

                     IDbDataParameter param = adapter.UpdateCommand.CreateParameter();
                     param.ParameterName = "@tsbHeader";
                     param.SourceColumn = "tsbHeader";
                     adapter.UpdateCommand.Parameters.Add(param);
                     param = adapter.UpdateCommand.CreateParameter();
                     param.ParameterName = "@tsbBody";
                     param.SourceColumn = "tsbBody";
                     adapter.UpdateCommand.Parameters.Add(param);
                     param = adapter.UpdateCommand.CreateParameter();
                     param.ParameterName = "@tsbId";
                     param.SourceColumn = "tsbId";
                     param.SourceVersion = DataRowVersion.Original;
                     adapter.UpdateCommand.Parameters.Add(param);

                     DataSet dataset = new DataSet();
                     adapter.Fill(dataset);
                     DataRow row = dataset.Tables[0].Rows[0];
                     row["tsbHeader"] = newHeader;
                     row["tsbBody"] = newBody;
             
                     int result = adapter.Update(dataset);

                     dataset.Dispose();
                     adapter.Dispose();

                  }
              }

       
      • Alexander Gavrilov

        Thanks for posting this code. It reveals the bug in the implementation of SQLiteDataAdapter. It doesn't allow any Update commands after calling Fill function. I'll investigate it further and will keep you posted.

         
        • Nobody/Anonymous

          Hi

          I was getting exactly the same error using one connection and the datareader.

          I am not a c++ programmer, so tell me if this is totally wrong.

          I had a look at the code and noticed that in the Close method of the DataReader, the UnCompile method of the command was not being called (it looks to me that this cleans up the command?). so i added a call to the UnCompile method in the datareader's Close method (basically added mpCmd->UnCompile(); after the first if statement).

          The error doesnt occur for me anymore...

          Don't know if this is any good :) but this is a great library, and I would like to use it...

           
    • Anonymous - 2004-06-10

      Thanks Alexander, I'll be looking forward to the fix.

      Howard Keller

       
      • Alexander Gavrilov

        I hope the version 0.16 fixed the issue.

         

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:





No, thanks