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

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

Whatever lock type you specify from the client is treated merely as a
suggestion by the server in the case where it needs to apply an
exclusive lock, as it does for the ALTER TABLE statement. Any time you
modify data, SQL Server locks the affected rows so that you cannot
even select data. When you are making schema changes, SQLS locks the
entire table, so yes, any SELECT statements will fail while the table
is still locked. This is expected behavior if you are running under
the default isolation level of READ COMMITTED, and not a bug. For more
information, see "Isolation Levels" in SQL Server Books Online.

--Mary

On Mon, 21 Aug 2006 08:13:34 +0200, "Gustavo Echevarria"
<g.echevarria@#REMOVETHIS#lantek.es> wrote:

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.
Best regards

Gustavo Echevarria

---------------------------------------------------------------------------------------------------------

Option Explicit

Private objOConnection As ADODB.Connection
Private blnResult As Boolean
Private blnTrans As Boolean

Public Sub StartConnection()
Set objOConnection = New ADODB.Connection
On Error GoTo errHandle
objOConnection.ConnectionTimeout = 120
objOConnection.Open "Provider=SQLOLEDB;Server=.;Database=250000;User
ID=sa;Password="
objOConnection.CommandTimeout = 600
objOConnection.IsolationLevel =
ADODB.IsolationLevelEnum.adXactReadCommitted
Exit Sub
errHandle:
End Sub

Public Sub EndConnection()
On Error Resume Next
If (Not objOConnection Is Nothing) Then
objOConnection.Close
Set objOConnection = Nothing
End If
End Sub

Public Sub BeginTransaction()
objOConnection.BeginTrans
blnTrans = True
End Sub

Public Sub EndTransaction()
If (Not blnTrans) Then Exit Sub
blnTrans = False
If (blnResult) Then
objOConnection.CommitTrans
Else
objOConnection.RollbackTrans
End If
End Sub

Public Function ExecuteSelectSQL(ByVal strSQLQuery As String) As
ADODB.Recordset
Set ExecuteSelectSQL = Nothing
If (strSQLQuery = "") Then Exit Function
Dim objRstSel As ADODB.Recordset
Set objRstSel = New ADODB.Recordset
On Error GoTo errHandle
objRstSel.CursorLocation = ADODB.CursorLocationEnum.adUseClient
objRstSel.CursorType = ADODB.CursorTypeEnum.adOpenStatic
objRstSel.LockType = ADODB.LockTypeEnum.adLockBatchOptimistic
Set objRstSel.ActiveConnection = objOConnection
objRstSel.Open strSQLQuery, , , , -1
Set objRstSel.ActiveConnection = Nothing
Set ExecuteSelectSQL = objRstSel
Set objRstSel = Nothing
Exit Function
errHandle:
End Function

Public Sub ExecuteBooleanSQL(ByVal strSQLQuery As String)
If (strSQLQuery = "") Then Exit Sub
On Error GoTo errHandle
objOConnection.Execute strSQLQuery, , ADODB.CommandTypeEnum.adCmdText +
ADODB.ExecuteOptionEnum.adExecuteNoRecords
Exit Sub
errHandle:
End Sub

Private Sub Main()
StartConnection
BeginTransaction
ExecuteBooleanSQL "alter table TABLE1 add FIELD2 nvarchar(80) not null
default '' '<- SCH-M lock over TABLE1
Dim objRst As ADODB.Recordset
Set objRst = ExecuteSelectSQL("select max(FIELD1) as 'DBMAX' from
TABLE1") '<- Here comes the hung up, SCH-S lock over TABLE1 with WAIT state
Debug.Print objRst("DBMAX").Value
blnResult = False
EndTransaction
EndConnection
End Sub

.

Relevant Pages