Transaction Database locking

• • Reply

  13 years ago

  by spycreepers

  michael selom

  , Ghana

  Joined 13 years ago

  Hi, 

  When a transaction is open, the database is getting locked for other user to fetch the records from database.

  I have to update multiple database table with huge data (Lac). So the transaction will be open for longer period (some times hours).

  Mean while if somebody tries to fetch or do select query it give timeout exception. And it does not function until the transaction is closed.
  

  How to avoid this database locking for other users.

   Below is the code that i have used:

              using (DbConnection dbconn = base.db.CreateConnection())
              {
                  dbconn.Open();
                  DbTransaction dbtran = dbconn.BeginTransaction();
                  try
                  {            
                     
                      Here inserting, updating to multiple table will be happening
  

                      dbtran.Commit();
  
                  }
                  catch (SqlException ex)
                  {
                      dbtran.Rollback();
  
                  }
                  finally
                  {
                      dbconn.Close();
                  }
              }    
  

   

  Regards,

  Hareesh Kumar

   

  Report abuse
• • Reply

  13 years ago

  by jmcilhinney

  John McIlhinney

  Sydney, Australia

  Joined 15 years ago

  This is how you need to think when you have a question.  You want your transaction to behave differently.  Your creating your transaction by calling BeginTransaction ob your connection.  Step 1: read the MSDN documentation for the BeginTransaction method.  Lo and behold, BeginTransaction is overloaded and you can specify an isolation level for the transaction.
  

  Report abuse
• • Reply

  13 years ago

  by spycreepers

  michael selom

  , Ghana

  Joined 13 years ago

  Thanks for your reply!

  I tried with different type of isolation levels.That could not help me.

  Once the insertion starts, the table is locked for other users to select the data until the transaction is complete.

  I tried select query with NOLOCK option (SELECT * FROM ORDERS WITH(NOLOCK)). It worked.

  This does not block other users from  selecting the existing data from the table. It gets only committed data from the table
  

  Report abuse