Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

LOCK TABLE PROBLEM Expand / Collapse
Author
Message
Posted Thursday, January 24, 2013 3:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 24, 2013 5:43 AM
Points: 4, Visits: 5
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.
Post #1411020
Posted Thursday, January 24, 2013 4:02 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:42 AM
Points: 1,261, Visits: 2,287
pallab.santra1987 (1/24/2013)
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.


Seriously ?


- Divine Flame
Post #1411040
Posted Thursday, January 24, 2013 4:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 24, 2013 5:43 AM
Points: 4, Visits: 5

Divine Flame

Yes Seriously.

I want a query(Delete) which works on nolock data of a lock table.


If you have any idea .. please Help...
Post #1411043
Posted Thursday, January 24, 2013 4:13 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:42 AM
Points: 1,261, Visits: 2,287
Okay, jokes apart. I think you want to delete the data from a table which is already locked as some other query is doing some modification in that table.

If that is the case, you will not be able to delete the data from the table untill the first transaction is not complete. This is required to ensure data consistency & integrity as per the ACID property.

If you are facing excessive locking then you need to look for the queries that are involved in locking & optimize them.

Note: NOLOCK can't be used with DML operations such as INSERT, UPDATE & DELETE. Apart from that it should be avoided in SELECT statements too, as it actully enables dirty reads (you can read the data which is not-commited & even can be rolled back later by the transaction.)


- Divine Flame
Post #1411047
Posted Thursday, January 24, 2013 4:19 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, March 14, 2014 2:19 AM
Points: 2,820, Visits: 3,916
check with sp_who2 , which spid is locking that table

then for all fetched do dbcc inputbuffer(sp_id), see what kind of transaction is there
then decide which spid you can kill (CAREFULLY) or wait for the transactions to get completed



-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1411052
Posted Thursday, January 24, 2013 4:47 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:47 AM
Points: 41,525, Visits: 34,442
Wait until whatever is using the rows has finished and the locks are released. If there's an orphaned transaction locking the rows, check with DBCC OpenTran and investigate whether or not to kill the session involved.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1411059
Posted Thursday, January 24, 2013 5:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 24, 2013 5:43 AM
Points: 4, Visits: 5
THANKS @ALL... for your valuable reply...
Post #1411078
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse