Locks Unlocked

I’m likely not the first person clever enough to use this title, but as requested, this post will delve into Locks and try and explain them in a simple fashion.

Simply put, you Lock data in a database to keep multiple operations from occurring on the same data at the same time – for example, if two Update operations are trying to modify the same record in the database, there will be an issue and the data will likely be incorrect when they are done.  So, each operation will lock the resource (record, page table, etc.), and release the lock when it is finished with the data.  Too many locks on a database will hinder performance, so it is important to only use the type of Lock that is necessary for the operation.  The big trade-off here is performance vs. data integrity – too few locks, and your data may become corrupt, while too many will bog down your servers.  You will have to determine what is best for your environment based on a number of factors, including number of concurrent users/applications, hardware resources, necessity of data integrity, etc.

In SQL Server, there are seven types of Locks modes available.  I will go over each one briefly, but you should also refer to the Books Online (BOL) documentation for a full discussion of these Lock types if necessary.  Here are the 7 types of Lock modes available in SQL Server:

Shared:  This type of lock is used in Read operations (typically a SELECT statement), the resource only needs to be locked to make sure the data is not modified during the Read.  As the data will not be modified by a simple Read operation, nothing further needs to be done.

Update:  This lock type is used when a resource needs to be updated – the lock needs to be put on the data before the transaction starts to ensure that another operation is not reading data that is going to be modified before the operation is complete.  This Lock will prevent deadlocks from Shared locks simultaneously trying to promote to an Exclusive lock on the same piece of data.

Exclusive:  This Lock is used when any of the data modification commands are used, such as UPDATE or DELETE.  No other lock is allowed on the data while an Exclusive lock is in place.

Intent: These locks are basically “pre-locks” set at table level to signal that another type of Lock will be placed on a resource in that Table.  There are three types of these,

Schema: These are used by the database engine to make sure no data is updated in a table while adding a column or doing some other operation involving the database structure itself.

Bulk Update: Used for doing bulk updates – this will lock the entire table so that multiple threads can execute the bulk update concurrently and not be interrupted or cause corruption with other operations.

Key-range: This type of Lock is placed on an Index, effectively locking off a range of data from Reads and other operations.

I hope this has given you a basic idea of the concept of locking, why it is done, and what the limitations and performance implications are.

Comments (2)
  1. Nu Lock says:

    Excellent blog!!

  2. Do It Yourself says:

    Do It Yourself

    How to Rekey a Door Lock

    If you want to have a new key to open your locks, would you have the locks changed or rekeyed?

    Usually, the initial thought is to change the locks so that a new key is needed to open the lock. However, by rekeying locks, your cost will be lower and the job easier.

    Changing your locks removes the entire old lock from the door and replaces it with a brand new lock. But rekeying locks is much easier and cheaper!

    Check the brand on every door; your locks might have different manufactures. Purchase a re-key kit for each brand. They are readily available at home stores and online. The kits come with two keys and tools to re-key six door locks.

    Unlock the door, and remove the doorknob by inserting the wire tool, included in your kit, into the hole on the knob. Release the clip and pull off the knob.

    Push the lock cylinder out of the knob. Once the knob sleeve drops off, use the retainer ring tool, also in your re-key kit, to free the cylinder.

    Using your old key, turn the lock ninety degrees left or right. Take the key out and push the plug through with the plug follower tool in your kit. Exert even pressure on the plug and tool to make sure you don’t loose the springs

    Empty the plug pins and put the new key in the lock. Use tweezers to insert new color-coded pins into the plug. Follow kit instructions to ensure the new pins are in the right order. Reassemble the doorknob.

    Tips:  from   wwww.charlesthelocksmith.com

    Every brand of lock is slightly different; refer to the manufacturer’s directions for any nuances.

    If the springs do pop out when you remove the plug, your re-key kits contains instructions for replacing them.

Comments are closed.

Skip to main content