Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

lock/deadlock problem

Status
Not open for further replies.
Jul 29, 2005
136
PT
Hello all,

I´m using asp.net 2.0 with defaults (pooling and timeout).
Sudently I starting getting locks/deadlocks in my database.

For each locking I run dbcc inputbuffer (spid). I hope this command will return the last sql statment each spid is trying to execute. I get something like this:

135 (blocked by 243)
SELECT field1, filed2, fieldn FROM TableA WHERE field = 1 AND otherfield =31



243 (blocked by 449)
SELECT Field1 FROM TableB WHERE field = 225


449 (blocked by 135)

INSERT INTO TableC(Tipo, GUID) VALUES (1854,'11/13/2007 10:08:35 AM0.533424')

269 (blocked by 243)
exec MySP '(10,101,2129,2131,2134)


If dbcc inputbuffer is right, I dont understand how the first 3 are blocking itself if the database of the running sql statement is different!

Any ideas?

Thank you


 
Take a look at adding locking hints to your SQL. Possibly set isolation levels in code as well.

Depression is merely anger without enthusiasm.
 
SELECT ItemID, Title WITH (NOLOCK)
or make sure you do begin trans commit and end trans
In sql 2005 bit different
 
Hello all,

I found the problem: an insert into a table was taking to much time: about 30/40 seconds. So I move some records 2 milions actually to an history table.

I wouls like to do this automatically... What would be the best approach to move records to history automatically? A job? A trigger? Anything else?


Thank you
 
Set up a DTS package (Or SSIS package if you have 2005 version).


However you should seriously think about using locking hints (or set transaction isolation level read uncommitted). You would be amazed at how much they can speed things up.


"NOTHING is more important in a database than integrity." ESquared
 
Right on, Sister!

Depression is merely anger without enthusiasm.
 
Hello,

I needed to migrate some data to an history table. Now inserts and updates are less slow.

But one question: will not locking hits disable the query optimizer? Should they be applied only to Selects? or to inserts and updates, also?

thank you
 
Typically you update with rowlock, and select with nolock. I strongly strongly urge you to spend some time with the help feature (BOL) and do a little homework on the subject - it will be time well spent.

Depression is merely anger without enthusiasm.
 
locking hints doesn't affect the optimiser, I think you're getting confused with index hints...

--------------------
Procrastinate Now!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top