none
UPDLOCK OR SCROLL_LOCKS ?? RRS feed

  • Question

  • Pls lemme know which is better UPDLOCK OR SCROLL_LOCKS.
    I need to migrte below oracle syntax to T-SQL syntax:

    Oracle:
    cursor emp_cur1 is select * from EMPINCOME for update;

    SQLServer:
    Option 1:
    emp_cur1 CURSOR SCROLL_LOCKS FOR * form EMPINCOME

    Option 2:
    emp_cur1 CURSOR SCROLL_LOCKS FOR * form EMPINCOME (UPDLOCK)

    which conversion is better? Any sugguestion will be great help.
    Monday, July 18, 2005 10:20 AM

Answers

  • Scroll locks ensures that you can update the rows via positioned update or delete. You don't need the UPDLOCK hint since it is redundant. The database engine will decide what type of lock to acquire on the table and this can be affected by the type of indexes you have for example or absence of key columns & so on. I would say that you should just translate this to declare cursor statement (either ANSI syntax or TSQL syntax) and try it without scroll_locks hint. ex:

    declare emp_cur1 cursor for select * from EMPINCOME for update;
    Tuesday, July 19, 2005 9:29 PM