Set Transaction Isolation Level for UPDATE or DELETE statements

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Performance Tuning » Set Transaction Isolation Level for UPDATE or...

Set Transaction Isolation Level for UPDATE or DELETE statements

Rate Topic

Display Mode

Topic Options

Author

Message

Ivan Parra

Posted Monday, November 05, 2007 6:32 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, April 01, 2011 3:49 PM
Points: 6, Visits: 27

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)?

Post #418823

Ivan Parra

Posted Thursday, November 08, 2007 1:27 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, April 01, 2011 3:49 PM
Points: 6, Visits: 27

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

Post #420253

« Prev Topic | Next Topic »

Permissions