Bypassing locks when doing insert or update

Hi,

I want to bypass locks while doing Insert or Update. I am only updating a log db and I don't care about one or two fields getting junk as I won't use it later (atleast as long as I am working with my current company ;) )

I am using MS SQL 2000

I am getting too many deadlocks and messages like these

"Process ID was deadlocked with another process and has been chosen a victim. Please rerun the transaction".

Please tell me how to achieve this.

Regards,
Noorul

you can try using a 'NOLOCK' locking hint....but u have to be sure..it wont affect ur data (ACID)....

correct..and longer part will be to try and find the source of deadlocks..and remove it..

|||Have a look to see what you have in the way of indexes on your table, particularly your clustered index. Hopefully you have an id field which only increments as your clustered index, so that it never tries to move the pages around, and your inserts can just jump straight in and out again.

Updates shouldn't have to be any different - make sure you have a good indexing strategy so that the system doesn't try to lock more of the table than it needs to.

Why are you updating a log anyway? And should I assume you mean 'audit' ?

Rob|||You absolutely CANNOT bypass locking when doing UPDATES or INSERTS or DELETES. Nor would you want to. You are getting deadlocks because your code is accessing the data in differring order, you are holding transactions open too long, and/or you are not using NOLOCK hints on your SELECT statements where appropriate.|||

Please take a look at the links below on how to resolve deadlocks. You can't eliminate them by just using locking hints in your various statements. Deadlocks are typically due to errors in your execution logic.

http://support.microsoft.com/kb/832524/

http://msdn2.microsoft.com/en-us/library/aa937573(SQL.80).aspx

|||Thanks All

I read about the clustered Index from Microsoft kb 169960. I created the clustered index with 70 fill factor. It has reduced the deadlocks down to zero. Actually I have an application that has 10 threads logging the sent SMS messages into same table. My application don't read from it, it just does insert. So far, so good. With 70 fill factor, how will that affect the memory consumption?

Regards,
Noorul