what is difference in locking and deadlocking 1

[Rousseau10]

what is difference in locking and deadlocking

thanks

 

[vongrunt]

Locking happens all the time - even when you do vanilla SELECT (without NOLOCK clause).

Deadlocking is "bad" case of locking. Here is simplified example:

- process 1: lock A, attempt to lock B
- process 2: lock B, attempt to lock A

Result: both processes wait indefinitely.

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba

 

[JamesLean]

FYI, when SQL Server detects a deadlock it will rollback one of the transactions and return an error 1205 to the connection.

--James

 

[Rousseau10]

Is it dealocking just the slected records or the whole recordset?

 

[vongrunt]

To jameslean: yup I know, that was simplified for illustrative purposes.

To rousseau10: deadlock happens on resource (lock or thread), not row or recordset. What gets eventually rolled back is transaction, whatever it affects.

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba

 

[JamesLean]

Sorry vongrunt, that was meant as additional info for the poster - I'm sure you are aware of how deadlocks are handled! ;-)

Rousseau10, another point to note is that there is a difference between deadlocking and normal lock blocking. Blocking occurs when one process has locks on a resource and another process needs to acquire locks on the same resource - the second process has to wait until the first process releases it's locks. In this case, the second process will wait indefinitely until it can successfully gain the locks it needs.

Blocking can be a major cause of performance degradation in a database and can often be attributed to poorly-written queries/transaction that hold locks longer than necessary.

--James

 

[Rousseau10]

what exactly is a process or thread?

sorry for silly questions - newbie

 

[JamesLean]

A process in this case is basically a query being executed over a connection.

FYI, you might want to read up on locks to familiarise yourself with the whole topic - it can be quite a complex subject to understand but you need to if you want to maintain well-performing databases.

BOL has lots of info - look up "locking" in the index.

--James

 

[Rousseau10]

thanks , I will read up on locking.