SQL Server Cursor - Quick Question
-
I have a question SQL Server cursor question I have a table, “clients”. I want to create a cursor to select and iterate through each client in the database. Obviously, that will lock the “clients” table. Fine. But if I involve another table to do a filter (in the where clause, to reduce the result set) does that lock both tables and just the “Client” table? Thanks in advance, Frank www.TheOpenSourceU.com
-
I have a question SQL Server cursor question I have a table, “clients”. I want to create a cursor to select and iterate through each client in the database. Obviously, that will lock the “clients” table. Fine. But if I involve another table to do a filter (in the where clause, to reduce the result set) does that lock both tables and just the “Client” table? Thanks in advance, Frank www.TheOpenSourceU.com
Also, it is important to note that the table i'd be using is a constantly used table (it touches almost every thing) Thanks again, Frank www.TheOpenSourceU.com
-
I have a question SQL Server cursor question I have a table, “clients”. I want to create a cursor to select and iterate through each client in the database. Obviously, that will lock the “clients” table. Fine. But if I involve another table to do a filter (in the where clause, to reduce the result set) does that lock both tables and just the “Client” table? Thanks in advance, Frank www.TheOpenSourceU.com
Jawz-X wrote:
I want to create a cursor to select and iterate through each client in the database. Obviously, that will lock the “clients” table.
It will not obviously lock the clients table. SQL Server Books Online: Cursor Transaction Isolation Levels The transaction locking behavior of a specific cursor is determined by combining the locking behaviors of the cursor concurrency setting, any locking hints specified in the cursor SELECT, and transaction isolation level options. Microsoft® SQL Server™ 2000 supports these cursor transaction isolation levels: Read Committed SQL Server acquires a share lock while reading a row into a cursor but frees the lock immediately after reading the row. Because shared lock requests are blocked by an exclusive lock, a cursor is prevented from reading a row that another task has updated but not yet committed. Read committed is the default isolation level setting for both SQL Server and ODBC. Read Uncommitted SQL Server requests no locks while reading a row into a cursor and honors no exclusive locks. Cursors can be populated with values that have already been updated but not yet committed. The user is bypassing all of the locking transaction control mechanisms in SQL Server. Repeatable Read or Serializable SQL Server requests a shared lock on each row as it is read into the cursor as in READ COMMITTED, but if the cursor is opened within a transaction, the shared locks are held until the end of the transaction instead of being freed after the row is read. This has the same effect as specifying HOLDLOCK on a SELECT statement. ---------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters
-
Jawz-X wrote:
I want to create a cursor to select and iterate through each client in the database. Obviously, that will lock the “clients” table.
It will not obviously lock the clients table. SQL Server Books Online: Cursor Transaction Isolation Levels The transaction locking behavior of a specific cursor is determined by combining the locking behaviors of the cursor concurrency setting, any locking hints specified in the cursor SELECT, and transaction isolation level options. Microsoft® SQL Server™ 2000 supports these cursor transaction isolation levels: Read Committed SQL Server acquires a share lock while reading a row into a cursor but frees the lock immediately after reading the row. Because shared lock requests are blocked by an exclusive lock, a cursor is prevented from reading a row that another task has updated but not yet committed. Read committed is the default isolation level setting for both SQL Server and ODBC. Read Uncommitted SQL Server requests no locks while reading a row into a cursor and honors no exclusive locks. Cursors can be populated with values that have already been updated but not yet committed. The user is bypassing all of the locking transaction control mechanisms in SQL Server. Repeatable Read or Serializable SQL Server requests a shared lock on each row as it is read into the cursor as in READ COMMITTED, but if the cursor is opened within a transaction, the shared locks are held until the end of the transaction instead of being freed after the row is read. This has the same effect as specifying HOLDLOCK on a SELECT statement. ---------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters
Thank you for that information. So, the answer to my question is that, in fact, neither table will be locked. So, I should probably know this, but please enlighten me and save me some research (I don't use cursors very often, I think I did one once). But why then are cursors to be considered a last resort? Is it simply the amount of resources that they use on the DBMS? Thank you, Frank www.TheOpenSourceU.com P.S. I like your quote.
-
Thank you for that information. So, the answer to my question is that, in fact, neither table will be locked. So, I should probably know this, but please enlighten me and save me some research (I don't use cursors very often, I think I did one once). But why then are cursors to be considered a last resort? Is it simply the amount of resources that they use on the DBMS? Thank you, Frank www.TheOpenSourceU.com P.S. I like your quote.
Jawz-X wrote:
But why then are cursors to be considered a last resort?
Because they are increadibly slow. I once saw a question on this forum complaining that the code was taking too long (it was using cursors). I suggested a better query to use without cursors and the operation took around one second to complete. Some things cannot be solved without cursors which is why they exist. But oftentimes the code can be re-written without cursors.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question." --Charles Babbage (1791-1871) My: Website | Blog