lock/deadlock problem

[olisemalis]

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

 

[genomon]

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

Depression is merely anger without enthusiasm.

 

[TRBLUE]

SELECT ItemID, Title WITH (NOLOCK)
or make sure you do begin trans commit and end trans
In sql 2005 bit different

 

[olisemalis]

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

 

[SQLSister]

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

 

[genomon]

Right on, Sister!

Depression is merely anger without enthusiasm.

 

[olisemalis]

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

 

[genomon]

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.

 

[Crowley16]

locking hints doesn't affect the optimiser, I think you're getting confused with index hints...

--------------------
Procrastinate Now!