General Description
Database locking is a varied, evolving, complicated, and technical topic.
A lock is used when multiple users need to access a database concurrently. This prevents data from being corrupted or invalidated when multiple users try to read while others write to the database. Any single user can only modify those database records (that is, items in the database) to which they have applied a lock that gives them exclusive access to the record until the lock is released. Locking not only provides exclusivity to writes but also prevents (or controls) reading of unfinished modifications (AKA uncommitted data).
Locks can occur for the following types of items: Tables, Data Rows, Data Blocks, Cached Items, Connections and entire systems.
Most of what we’re calling transactional locking relates to the ability of a database management system (DBMS) to ensure reliable transactions that adhere to these ACID properties. ACID is an acronym that stands for Atomicity, Consistency, Isolation, and Durability. However, all of these properties are related and must be considered together. They are more like different views of the same object than independent things.
Factor in slowness
In multi-user systems, locks are a significant factor to slowness because of access requests on items that are being read from or written to by other users and are already locked, that forces them to wait for each item to be unlocked. It is critical to understand that locks will often remain after a statement has finished executing. That is, a transaction may be busy with different, subsequent activity but still hold locks on a table due to an earlier statement. Transactions may even be idle. This is especially dangerous if the application allows user think time within database transactions.
There are a number of problems that can be caused by database locking. They can generally be broken down into 4 categories: Lock Contention, Long Term Blocking, Database Deadlocks, and System Deadlocks.
In order to avoid these problems as much as possible they should be monitored regularly and handled individually. The AimBetter system monitors your database in real time and allows you to see locks and long queries running on your databases at any time.
Difficulty in identifying past issues
When we don’t regularly monitor locks and long queries we cannot identify problems in running queries and procedures in until we catch them just in time of occurrence and even then it is possible that while we run our tests the problem ends and we fail to catch it in time.
AimBetter records all the events in your system and keeps them for as long as you set, so when you come to handle deadlocks and long queries from AimBetter you can view such events that occurred in the past and deal with each case individually.
Here is an example of long locks queries stored in history:
Remote control out of the office
AimBetter is a complete Web system that allows you to connect to it via any Internet-connected device and monitor your system from anywhere, even when you’re not in the office.
Analysis and pattern recognition
AimBetter lets you see all the long queries and locks currently active on your system and allows you to copy them to the SQL Server Management Studio, make changes and simulate them in order to explore ways to resolve:
Users committing exceptional heavy transactions
AimBetter allows you to identify users and applications that run exceptionally long transactions and deal each case accordingly. The user name you’ll see a column Session:
Collision between applications and users activity
AimBetter system allows you to see all users running long queries or locking elements and all applications competing for the same resources. You can identify them in accordance with Session and Program column screen.
Summary
You can AimBetter system using real-time monitor locks and long queries and know about such events have occurred in history and use this information to optimize your systems, improve performance, to significantly reduce the frequency of locks and shorten the time of long-running queries.