Transactions and locking Tables [Changed subject]

arcuza · April 11th, 2005, 10:12 AM

I have a database in MSSQL Server which I'd like to query from ASP.

I have written a query in Query Analyzer, it works fine returning a single cell of data.

When I run the code through InterDev I get an error:

Error Type:
ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal.

Obviously, the recordset is already closed when I try to echo it to the screen. If I choose a simpler SQL-query

SQL = "SELECT * FROM Licensnycklar"

The recordset remains open until I close it manually.

Why does the recordset close itself when running sertain queries?

_______ The code _______

<%
set c = Server.CreateObject("ADODB.Connection")
c.Open "DSN=database", "username", "password"

dim SQL

SQL = "DECLARE @UsedIndex CHAR(5);" &_
      "DECLARE @LicenseKey CHAR(21);" &_
      "BEGIN TRAN;" &_
      "SELECT @UsedIndex = MIN([index]) FROM Licensnycklar WHERE forbrukad != 'Yes' or forbrukad is null;" &_
      "UPDATE Licensnycklar SET forbrukad = 'Yes' WHERE [index] = @UsedIndex;" &_
      "SELECT @LicenseKey = nyckel FROM Licensnycklar WHERE [index] = @UsedIndex;" &_
      "COMMIT TRAN;" &_
      "SELECT @LicenseKey;"

dim R
set R = c.Execute(SQL)
%>

<html>
<head>
</head>
<body>
<%=R(0)%>
</body>
</html>

<%
R.Close()
set R = Nothing
c.Close()
set c = Nothing
%>

pgtips · April 12th, 2005, 03:58 AM

Add "SET NOCOUNT ON" to the start of your SQL. You're probably getting back closed recordsets which just contain counts of affected records. Alternatively keep doing NextRecordset() until you get to one with a .state = adStateOpen

Also, wouldn't this SQL be better in a stored procedure?

hth
Phil

arcuza · April 13th, 2005, 01:53 AM

I am using InterDev for this project. There's a setting in the menu "View -> Property pages -> Enable transactions" that is not checked. Might that be the reason for the error?

Also, I need to lock the table when doing this SQL-query so that the collection of queries are run as one. Any suggestion on how to lock the table, and which part of the table?

pgtips · April 13th, 2005, 04:07 AM

Quote:

quote:I am using InterDev for this project. There's a setting in the menu "View -> Property pages -> Enable transactions" that is not checked. Might that be the reason for the error?

Did you read my earlier post?

Quote:

quote:Also, I need to lock the table when doing this SQL-query so that the collection of queries are run as one. Any suggestion on how to lock the table, and which part of the table?

Looks like you need an UPDLOCK here. That will guarantee that the data you read won't change before you get a chance to update it, but it will not block others from reading.
Change "SELECT @UsedIndex = MIN([index]) FROM Licensnycklar WHERE forbrukad != 'Yes' or forbrukad is null;" &_
To
"SELECT @UsedIndex = MIN([index]) FROM Licensnycklar WITH (UPDLOCK)WHERE forbrukad != 'Yes' or forbrukad is null;" &_

hth
Phil

arcuza · April 14th, 2005, 04:22 AM

"Did you read my earlier post?"

Yes I did, and it works. Could you explain why I recieve different results in SQL Query Analyzer and in InterDev?

"Looks like you need an UPDLOCK here ... it will not block others from reading."

It is crucial that no more than one user recieves the same value for MIN([index]). Since [index] = {1,2,3,4,5,6 ...} and enumerates the records, every record has to be unique to every request.

pgtips · April 14th, 2005, 04:53 AM

The difference is because of the database driver which Interdev has to use. I would guess you're using the SQLOLEDB driver which is why you get a recordset for each SQL statement, whereas in Query Analyser you get a line printed in the output 'n records affected' for each SQL statement.

I don't understand what you're trying to say about records being unique to requests. There is a lot of info in SQL Server Books On Line about locking hints and transaction isolation levels - take a look and see which combination you think suits your circumstances best.

hth
Phil

arcuza · April 14th, 2005, 06:04 AM

Thank you!

I'll take a look in the online library.

Just to make it clear:

I have three columns; Index, License and Used.

Index just enumerates the records.
License is a column containing a product license code in every record.
Used is a boolean flag that tells you if the license is used (downloaded).

I want a SQL query that finds the first record where used is false, get the license code and set the "used"-flag to true.

Since the license codes must be unique, two requests cannot recieve the same record (or license code).

Therefore I have to lock the table while finding, setting and recieving.

Thats about it.

Thanx again.

pgtips · April 14th, 2005, 06:53 AM

OK I understand.

An UPDLOCK should be enough for you. it guarantees that once you have read the index with your first select statement then no other user can update it until your transaction is finished. Also other users will have to wait for their own UPDLOCK so you will not get 2 users trying to grab the same index.

You might also want to consider setting the lock timeout period (the default is to wait forever for a lock!) using the SET LOCK_TIMEOUT statement. If you do this you will also need to handle lock timeouts in your code, something like this:

Code:

    Dim nRetryCount
    Const cErrDeadlock = &H80004005
    Const cErrTimeout = &H80040E31

    ' your code here

    If nRetryCount < 3 And (Err.Number = cErrDeadlock Or Err.Number = cErrTimeout) Then
        nRetryCount = nRetryCount + 1
        Resume
    Else
        ' check for other errors
    End If

Please take some time to read up on these lock and isolation levels, don't just take my word for it :D Ultimately you're the one who understands your data and application best, so you should make the important decisions.

regards
Phil

arcuza · April 14th, 2005, 07:26 AM

I'll do so, thanks Phil.