August 14, 2005 at 11:15 pm
Hi,
I have a database where locks seem to persist after running a select statement for no reason, but later drop off once the program that created them terminates.
Basically, running the select statement (select column from table where id = 8744), leaves a row visible in "exec sp_lock2":
LockMode: PAG, LockType: GRANT, lastwaittype: WRITELOG, cmd: AWAITING COMMAND, etc
The problem is that it only occurs for some records, and it is consistently the same ones. At the moment I have 2 records in this table that consistently have this effect, yet none of the other 6000 do.
This does not seem to leave the row locked. I can update that explicitly in Query Analyser, but I can't update the table - that update is blocked by the program.
This has been reproduced using a basic C++ program. No explicit transactions are being used, just the SQL_AUTOCOMMIT on connection.
This is running under SQL 2000 SP3. (Which I think had a fix for a similar issue).
Sample Program, (nothing fancy):
----
#include
#include
#include
int WINAPI WinMain(HINSTANCE hInstance, HINSTANCE hPrevInstance, LPSTR lpCmdLine, int nCmdShow)
{
SQLHENV henv = (SQLHENV)0xFEEDABBA; // Environment handle
SQLHDBC hdbc = (SQLHDBC)0xDEAFBABE; // Connection handle
SQLHSTMT hstmt = (SQLHSTMT)0xABCD0000; // Statement handle
int retval;
retval = SQLAllocHandle(SQL_HANDLE_ENV, NULL, &henv);
retval = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, SQL_IS_UINTEGER);
retval = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
retval = SQLConnect(hdbc, (SQLCHAR *)"user_name", SQL_NTS, (SQLCHAR *)"database", SQL_NTS, (SQLCHAR *)"password", SQL_NTS);
retval = SQLSetConnectOption(hdbc, SQL_AUTOCOMMIT, SQL_FALSE);
SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
retval = SQLExecDirect(hstmt, (SQLCHAR*)"SELECT order.company FROM order WITH (ROWLOCK) WHERE (order.id=8744)", SQL_NTS);
return 0;
}
----
I noticed that the table did not have a clustered index. So I just added one and the locking has stopped on those records. I then removed it, and they still DONT lock. Is this purely a coincidence, or part of the justification for clustered indexes?
I see this same locking behaviour in other copies of this database in other environments.
Any suggestions/comments would be greatly appreciated.
David
August 16, 2005 at 12:03 am
Look in the Books Online about "Isolation Levels". Then take a look at the READ UNCOMMITED. That may help in your locking issue.
This helped when there were several instances of the program trying to retrieve a large dataset. One query would time out from the second instance because the first instance hasn't finished retrieving the data from the server.
August 16, 2005 at 9:52 am
Also take a look at connection pooling and releasing your connection. If you don't release the connection after doing the select, SQL will leave it out there so the next time the program runs it will do less work re-connecting to the database. That coupled with the (ROWLOCK) option in your select is what I would guess is causing the problem. If you aren't going to update the row later on in the program, don't use the ROWLOCK hint.
August 16, 2005 at 6:49 pm
Thanks for the 2 suggestions, however..
chauchnet - I don't think we can really make this READ UNCOMMITED, as this would potentially impact integrity from a user perspective if we caught the image mid transaction. (Yes it did suppress the lock in this example). Although I'll look closer into whether this is likely, and whether we can do it for some of our selects, but not others. But always safest to read committed if possible.
DeanGroovy - Removing the rowlock option still left the lock on the record. The system running this query has high query rates, so I don't think we can release these for each request/transaction.
It still bothers me though that it is only certain records. I can run the select against 8000 records, and only 2 will leave this lock in place.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply