Currently, I'm facing some database locking issue in C# database programming. I'd make use the SqlConnection object BeginTransaction method to handle the inserts, updates and deletes tables in one transactions. The transactions may commit and rollback properly if any error occur. Anyway, I found some problem during implement it in my project.
First, I'd launch the first form and click at update button which declare the connection BeginTransaction and execute some of the inserts statement through SqlCommand.ExecuteScalar method into table A, and not yet call the SqlTransaction.Commit or SqlTransaction.Rollback method at the save button.
Then, I go to launch the second forms to retrieve the data from table A. The second forms will hang at there until I'd press the save button to call SqlTransaction.Commit at the first form. The same problem also occur when I try to access my data from back end through SQL Server Enterprise Manager (EM) before I press the save button. The SQL Server EM cannot return any records from table A. It seems like the transaction have not yet commit will lock the whole table from other users to access the records.
Is there any solutions to avoid the transaction from locking the whole table? Or how to make the fewer lock to my database rows when the application have to enable multi-user do reads and actual updates?
1. the default locking granularity in SQL Server is page lock (or row lock, depends on which version and what operation you do), however, the locking could be escalated if SQL Server found it's more effective to handle lock in larger granularity. Use sp_lock to check what type of the lock is, it might or might not be table lock.
2. Ideally, the begin transaction should be issued right before the actual update statement and commit/rollback transaction should be issued right after actual update statement, thus, reducing the transaction time and the possibility to cause interlocking among sessions.
To solve locking issue, the first step is to find out what cause the locking.
Sometimes, we might be able to handle on SQL Server side, but more often, we need to adjust application to interact with SQL Server better.