SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Lock Table Problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pallab.santra1987
Starting Member

India
1 Posts

Posted - 01/24/2013 :  05:08:23  Show Profile  Reply with Quote
For SQL Server 2008:

What is the procedure to delete some data from Lock Table?
We can't use NOLOCK for Update, delete & Insert for that.

A table Name like Business_History which is in Transaction Lock.
Want to delete data where Proposal_Form_No='ABC'. If I Execute Select Operation With NOLOCK it shows 5 rows of data. But Can't delete them.

Please Help.

James K
Flowing Fount of Yak Knowledge

3758 Posts

Posted - 01/24/2013 :  08:32:26  Show Profile  Reply with Quote
That sounds like some other process is locking the table and not releasing it. For example, a transaction that has been left open without being committed inadvertently? Tables get locked all the time, but then they should released. sp_lock and sys.dm_tran_locks should give you some information about the locks being held
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2079 Posts

Posted - 01/24/2013 :  13:57:59  Show Profile  Visit jackv's Homepage  Reply with Quote
It's trying to obatin an Exclusive Lock, and is incompatible with whatever the current lock is.
use this query to see lock and blocking sessions - http://www.sqlserver-dba.com/2012/12/sql-server-find-sql-server-locks.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

denis_the_thief
Aged Yak Warrior

Canada
594 Posts

Posted - 01/25/2013 :  10:55:34  Show Profile  Reply with Quote
You may be forced to kill the processes holding the lock.

If possible, you could rollback/commit the transaction.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 01/25/2013 :  13:46:52  Show Profile  Reply with Quote
Not exactly, about your question, but putting a NOLOCK on a doesn't make any sense. Additionally, SQL will not honor that hint on a delete anyway.
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2079 Posts

Posted - 01/26/2013 :  05:51:50  Show Profile  Visit jackv's Homepage  Reply with Quote
The intention is for SQL Server to use it only for INSERT and SELECT. It'll be removes in the future version

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000