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 (https://blog.sqlauthority.com)
28 Comments. Leave new
Hi
How to generate select statement output into Excel or textual output
Please Let me know, is it possible?
by
Palani
Using bcp command you can genrate any format u want.
byeeeeee
Dhananjai Pratap Singh
hii
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”
Imran Mohammed
thanks for the information on Transaction Lock , I was what we were looking for
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
You are right. jitendra. they create the confusion.
Thanks Gaurav and Jitendra
@ 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.
I’m glad you’ve noticed that mistake about READ UNCOMMITTED, as I was about to get really confused.
concept is crystal clear
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.
Thanks buddy
Hi,
Excellent…
please provide some more knowledge about Level-2,3,4 also.
It will be very helpful.
Thanks,
Kushal
How can we insert a record into the database object ( Table ) from a program which is locked because of update statement on same table.
I also have the same question, Please answer.
lock sql server 2008
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
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
Hi Pinal Dev,
we have SQL Server 2008 R2 Enterprise Edition x64
Do we have to enable Locked Pages?
No:
Locking pages in memory is not required on 64-bit operating systems.
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/enable-the-lock-pages-in-memory-option-windows?view=sql-server-2017.
Yes
https://support.microsoft.com/en-us/help/918483/how-to-reduce-paging-of-buffer-pool-memory-in-the-64-bit-version-of-sq
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.
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 .
Think about if there is optimistic locking then there may be deadlock condition arieses, how to solve it?
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?
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?