Transaction Database locking

db Ghana
  • 11 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

     

  • 11 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.

  • 11 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

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“I invented the term Object-Oriented, and I can tell you I did not have C++ in mind.” - Alan Kay