Re: Hung up using a SELECT MAX when an alter database has been execute in a SQL Server database

Tech-Archive recommends: Fix windows errors by optimizing your registry


"Mark J. McGinty" <mmcginty@xxxxxxxxxxxxxxx> escribió en el mensaje
news:%23Qllw1kvGHA.324@xxxxxxxxxxxxxxxxxxxxxxx

"Gustavo Echevarria" <g.echevarria@#REMOVETHIS#lantek.es> wrote in message
news:u1hXI$EvGHA.1436@xxxxxxxxxxxxxxxxxxxxxxx
Hi all

i've reach a very strange sql server hung up when executing the following
code. I modified a table with an alter table, the sql server locks the
table with SCH-M and it hung up whe using a locktype greater than
ADODB.LockTypeEnum.adLockReadOnly when making a select to that table with
a select max of any field.
The conflict seems to be a SCH-S lock on the same table that waits
(deadlock?) the release of the SCH-M lock?
How can the same connection make a deadlock with itself? Is SQL Server
bugged with this to kind of locks?
Why the SELECT MAX blocks with a SCH-S and a SELECT TOP 1 FIELD ORDER BY
FIELD DESC didn't?
If someone has the same problem or know a issue that can help me
understanding why this happens all help will be appreciatted.

It is probably because you are trying to do it all within the same
transaction. Commit the schema changes first, then run your select
statement.

-Mark

But if i use a SELECT TOP 1 FIELD ORDER BY FIELD DESC it didn't hung the
transacction, which is the difference between one SELECT statement and
another?
This is the problem, i do not know why one hungs the transacction with a
SCH-M and SCH-S deadlock in the same transaction, and the other do not.

Gustavo Echevarria


.

Relevant Pages

  • Re: Lock confusion
    ... SQL Server has to decide on a deadlock victim. ... DEADLOCK_PRIORITY option in your transaction to designate the victim ... In the 2005 studio manager the "Locks by Object" for the Color table ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: TOP @@identity order by clause desc
    ... The reason is that locking in SQL Server is first come, ... transaction ends. ... > In the meantime if user 2 in a different session does> ... At the same time I see page> locks on table1 during this lockout period. ...
    (microsoft.public.sqlserver.programming)
  • Re: More than pessimistic record locking needed...
    ... they both get the same unvalidated record, and both see its not in the ... concept of a transaction enough... ... Frankly speaking, I do not know how to lock the table in MS SQL Server, ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Use replication + DTS - Referred to Hilary Cotter or Paul Ibis
    ... locks appearing. ... Is there security settings preventing this transaction? ... taking so much of your time but am new to these functions of SQL server. ... > try a distributed transaction using similar syntax: ...
    (microsoft.public.sqlserver.replication)
  • Re: row vs page locking...
    ... so they automatically escalate to page level locks. ... 'Lock Escalation' - see Books Online. ... SQL Server 2005 is going to have a Snapshot ... Good old fashioned locking is less sexy, but I find, more productive! ...
    (microsoft.public.sqlserver.server)