SQL SERVER – Detailed Explanation of Transaction Lock, Lock Type, Avoid Locks

Loyal reader of this blog and “Great SQL Expert” Imran Mohammed always have good attitude towards any problem. Many times his answers very interesting to read and details are very accurate. I came across his two interesting comment on this blog and I would like to share this all of you.

Priyank asked following question.

Can u tell us something about how to find which sql table is having the lock and of what type. also please tell us how to remove a lock from a locked table
thanks
Priyank

Imran Mohammed answered in great depth to this question. I personally enjoyed it very much.

@Priyank

In SQL Server 2000 (Enterprise Manager)

1. Expand server – management-currentActivity-expand
locks/processid and you will be able to see all the locks related information.

2.Expand server – management-currentActivity-expand Locks/object you can see locks by object information.

In SQL Server 2005 (SSMS, object Explorer)
Expand-server-management-double click Activity Monitor.
on left side you have three options to choose from, select those options and you can see all the locks related information.

run this stored procedure in the database.

1. sp_lock

to know the running process in the sql server, run this query,

2. select * from sysprocesses ( in sql server 2000)
3. select * from sys.sysprocesses ( in sql server 2005)

4. sp_who
5. sp_who2 will also give you some good information.

To work around the locks, you can run profiler to check which query is is creating a lock and if that is necessary.

Types of locks on object level, ( general idea)

Database : Database.
Extent : Contiguous group of eight data pages or index pages.
Key: Row lock within an index.
Page: 8-kilobyte (KB) data page or index page.
RID :Row ID. Used to lock a single row within a table.
Table: Entire table, including all data and indexes.

Types of locks;
Shared (S) – more than one Query can access the object.
Exclusive lock (X) – only one Query can access the object.
Update lock (U)
Intent share (IS)
Intent Exclusive (IX)

Just to give you a brief idea about locks, We have something called as transaction levels in sql server databases.

TRANSACTION ISOLATION LEVEL
level 0. READ COMMITTED
level 1. READ UNCOMMITTED
level 2. REPEATABLE READ
level 3. SERIALIZABLE

level 0 is the lowest level isloation level, if your database is set in this isolation level, no query will lock any resources,Under this level, there will be no locks on the database, not even shared locks.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
This data will also read uncommitted data. Data which you have not comitted, you can still read that data.

level1 is the default isolation level of the database.
Under this category you will not be able to read uncomitted data, this is also called as dirty data. Under this we will have shared locks.

As the level increases the locks also increases. The highest is the serializable.

To make you understand in detail, lets see an example of what is committed data and what is uncomitted data.

use pubs
create table example1 ( eid int, ename varchar(10))

begin tran T1
insert into example1 values ( 1, ‘example’)
go

select * from example1 — this is uncomitted data.

The above is uncomitted transaction, because you started the transaction with a begin, you have to commit the transaction, untill then the transaction will not be uncommitted.

to commit the same transaction

commit tran T1

select * from example1 — this is committed data.

To check what is the current isolation level of your database, run this command,

Dbcc useroptions — check for isolation level.

If you dont want your query to put locks on objects you might want to use something like this,

select * from example1_1 with (nolock)

This will not keep any lock, not even a shared lock on the table.

This is indepth concept try looking BOL.

Hope this helps,
Imran.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

27 thoughts on “SQL SERVER – Detailed Explanation of Transaction Lock, Lock Type, Avoid Locks

  1. We have a SQL application, If someone in one site opens it before anyone else is using it, no one else can open the program.
    it gives this error “another program is using this file”

    Like

  2. Hi,

    There is some wrong information published in the content. It’s said that “level1 is the default isolation level of the database.” and also mentioned level1 is READ UNCOMMITED.

    Actually there are 5 isolation levels as
    READ UNCOMMITTED
    READ COMMITTED
    REPEATABLE READ
    SNAPSHOT
    SERIALIZABLE

    and READ COMMITED is default level.

    Thanks – JL

    Like

  3. @ Jitender,

    I agree

    Level 0 : Read uncommited (lowest level)
    Level 1: Read committed which is default.

    I think I wrote it by mistake. Thanks for correcting me…

    Thanks,
    Imran.

    Like

  4. Jitendra:
    Actually there are 5 isolation levels in SQL Server 2005 and newer, but only 4 in SQL Server 2000, since SNAPSHOT is not an option there.
    Excellent post, as usual from Pinal Dave.
    Thanks.

    Like

  5. Pingback: SQL SERVER - 2008 - Interview Questions and Answers - Part 7 Journey to SQL Authority with Pinal Dave

  6. How can we insert a record into the database object ( Table ) from a program which is locked because of update statement on same table.

    Like

  7. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 16 of 31 Journey to SQLAuthority

  8. Begin T1:
    Update T1 set Col2 = ‘C2′ where Col1 = 3 — means the 3rd row is updated
    Waitfor delay ‘000:00:20′ — delay for 20 secs
    –meanwhile T2 begins

    Begin T2
    Update T1 set Col2 = ‘C3′ where Col1 = 3 –3rd row is updated
    — This update while not happen untill T1 ends ie takes close to 20 secs to happen

    The behaviour is same for both Read committed and Repeatable read isolation levels
    Then where does the difference between the two in handling Lost Updates

    Like

  9. I have a application with three window services running to import, import process and print files in a folder. Once I got deadlocks I’ll get more later. But after I reboot sql server all lock are gone so I don’t get deadlocks. It looks like once I got deadlocks, locks are not released.
    I know they are working on a couple of table. How to remove those locks from a table? Thanks

    Like

  10. Hi Imran
    The description about Locks is very useful. But i have query regarding this.
    I am explaining my scenario here.
    I have written a script which create the back-up table for the existing main table and it will insert deleted data from main table into my back-up table. Firstly it will insert data into the backup table and then it will delete the data from main table.
    Now the problem is that if *INSERT* command is executed and just after *Before DELETE* some one insert data into main table then i m unable to put those data into my back-up table. How could i restrict my table in INSERTION Mode.
    Please reply as soon as possible. Your any input definitely work for me.

    Reagrds
    Shaan.

    Like

  11. Please delete and post the correct information on this page . It is misleading and confusing. not everyone would go through the comments and then decide .

    Like

  12. Hi Pinal,

    I have one doubt, hope you will make it clear for me –

    If I have set TRANSACTION ISOLATION LEVEL to READ UNCOMMITTED @beginning of stored procedure,
    and all the SELECT statements in same SP, I m not using WITH(NOLOCK);
    will it be same as –
    not set TRANSACTION ISOLATION LEVEL to READ UNCOMMITTED @beginning
    and used WITH(NOLOCK) in SELECT statements underlying SP?

    I mean, are They the same things?
    are both the way of writing code will return dirty read records?

    Like

  13. Hi Shiya,

    In READ UNCOMMITTED level we don’t require WITH(NOLOCK) why because no any locking meachanism is used, in this scenario if deadlock happen then 100 percent sure that it would return dirty data or Uncommited Dependices data.

    For Reference Please read above article again or start from below point : ……….To make you understand in detail, lets see an example of what is committed data and what is uncomitted data

    use pubs
    create table example1 ( eid int, ename varchar(10))

    begin tran T1
    insert into example1 values ( 1, ‘example’)
    go
    ……………………………..
    ———————————————————–******************************

    WITH(NOLOCK) keyword will be used above Level 0 that means above READ UNCOMMITTED level .

    So it is no meaning used WITH(NOLOCK) or WITHOUT in READ UNCOMMITTED level of your below query.

    will it be same as –
    not set TRANSACTION ISOLATION LEVEL to READ UNCOMMITTED @beginning
    and used WITH(NOLOCK) in SELECT statements underlying SP?

    Like

  14. Hi Pinal Dave…. Hi All,

    hope you can help :(

    I have developed a little application for testing dead locks. I have seen in different forums that READ UNCOMMITTED allows read data even though has not been committed.

    Well, that is not working for me. I am executing the application with only one session or one database connection with with the oledb isolation level set to Read Uncommitted.

    For example, I insert a record ‘A’ to table AUTHORITY, then I delete records from table X, Y and Z, then I try to read from the table AUTHORITY again and I get X and IX dead locks. All under the same atomic transaction (And no others transactions accessing the database)

    My program is more complex than that, but in essence is what I am doing.

    So, why I am getting dead locks with Read Uncommitted under this scenario. Have I misunderstood something with the theory???

    Thanks for your Help……. Jorge

    Like

  15. Pingback: SQL SERVER – Weekly Series – Memory Lane – #043 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s