Locking In Sql Server

[Lafor]

Has any 1 successfully implemented a multi-user application
(using SQLServer) where updates are taken place frequently
and some sort of locking (row level or other) was used..

Could we benefit from your lights on this by sharing a few ideas?

Thanks in advance...

[antares686]

I have apps that update several times over a few seconds. What specifically are you wanting to know as I don't handle the locks myself I allow SQL? I have seen know specific performance issues unless I have large selection queries hit the server and that has been limited.

[Lafor]

Multiple users will be using the app concurrently... Wondering how to handle the case where 2 people are trying to update
the same table at the same time... How does SQL Server react?


Should we worry?

[antares686]

Deadlock condition

If you create cross updates where one resource can be locked by one connection which needs to have a resource locked by the other connection which in turn wants what connection one has locked then after a period of time one should be vicitmized for the other to complete.

Two updating same row(s).

Connection one will create a Row lock and may escalate to a Page lock or greater depending on if Update is one row or a range of rows. Connection two will wait until the locks free before it can process then it will do the same as one. However there is a command timeout that you can set to make the waiting connection give up. It is best to use unless you absolutely must get thru.

Generally SQL does a great job with these locks. You can however create conditions thru the way I listed the above Deadlock piece where they can block without a vicitim.

Use sp_who and sp_locks to see if processes are blocking each other when server gets behind. Usually it is bad order of operation that leads to this.

You can however set certain locks yourself but that can also cause grief as well.

See SQL BOL for deadlock and lock types for additional info.

[Lafor]

Thanks... much