Post reply

Set Transaction Isolation Level for UPDATE or DELETE statements

  • November 5, 2007 at 6:32 pm

    #199630

    I know if I use the "Set Transaction Isolation Level" (using Read Uncommitted, or Read Committed, or whatever), l don't need to write lock hints in every table used in every SELECT statement.

    I read in a page that it works only for SELECT statements, but an UPDATE statement can have joined several tables and I want to avoid of using Lock hints in every joined table

    Check the following example:

    Set Transaction Isolation Level Read Uncommitted

    Update EmployeeSalaries WITH (RowLock,ReadCommitted) Set

    DailySalary = DailySalary * 1.15

    From EmployeeSalaries

    Inner Join Employees On Employees.EmpID = EmployeeSalaries.EmpID

    Inner Join Departments On Departments.DepartmentID = Employees.DepartmentID

    Where Employees.DepartmentName = 'HR'

    Using the example, I want to know if the Set Transaction Isolation Level READ UNCOMMITTED works for all the tables joined in this UPDATE statement (Employees and Departments)?

  • November 8, 2007 at 1:27 pm

    #749628

    The answer is YES, the Set Transaction Isolation Level works for the joined tables in the UPDATE statement, I couldn't found an answer in books or pages in internet, I had to prove myself

    Also, It works for the joined tables in DELETE and INSERT statements, even Subqueries used in any of these statements

    I hope this be helpful

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply