Granularity question

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

I'm discussing some deadlocking issues with MSS support, and was told
something that I'm hoping is wrong.

Background info:
A classic cause for deadlocks is two connections that do the following steps
in this sequence:

Connection A gets share lock on Resource 1
Connection B gets exclusive lock on Resource 2
Connection A attempts to get share lock on Resource 2; is blocked.
Connection A attempts to get exclusive lock on Resource 1; is blocked.
Deadlock occurs.



Our problem:
We had a case where all connection A did was issue a single SELECT
statement. It was a 3 table join.
Since there was only ONE statement in the entire transaction, I was sure it
would be impossible for it to be involved in a deadlock; either it would be
blocked and wait for the other connection, or it would go through and any
other connections would have to wait.
The support person said that the select statement could have gotten some of
the required share locks, but not been able to finish getting all of them,
and that's why the deadlock occurred.
For example, Connection A got share locks on rows/pages 1 and 2, then
connection B got some locks, then Connection A tried but failed to get the
remaining locks.

I would have thought that MSS would grant all locks required for a single
statement at once, and not allow other connections to get in the middle of
this.

Anyone know what the case really is? Thanks.


Dale


.

Relevant Pages

  • Re: Unlocking Record
    ... Assuming you refer to locking as in exclusive locks, ... Only the connection having a log on a resource can un-lock it. ... > Is it possible to unlock a record from directly inside the SQL Server MMC? ...
    (microsoft.public.sqlserver.server)
  • Re: Locking up & IE wont work
    ... Diagnose Connection button it comes back and says the ... was previous IE working before installing IE8? ... I've reset all IE settings back to the default settings. ... It also locks up once in a while when being used. ...
    (microsoft.public.windowsxp.help_and_support)
  • [opensuse] opensuse 11.1 on a Lenovo Thinkpad T61p
    ... -The kernel shipped with 11.1 locks up on this machine. ... -the machine will not suspend to ram or disk. ... Connection was not provided by any settings service ...
    (SuSE)
  • Re: Blocking problems
    ... This locks the connections collection, but does not show if an existing ... connection in a send loop also locks "this._connections" in its loop. ... > private SoapTcpConnection GetConnection ...
    (microsoft.public.dotnet.framework.webservices.enhancements)
  • Re: Locking a SQL Server Record from ASP + Batch Transactions
    ... people trying to edit the same record at the same time. ... I would expect that if someone locks a record and goes away for lunch, ... database locking may not work at all since the connection actually drops the ... you solution of setting up a reservation field to kludge it has ...
    (microsoft.public.dotnet.framework.aspnet)