USE AdventureWorks go CREATE VIEW Person.AddrState WITH SCHEMABINDING AS SELECT a.AddressID, a.AddressLine1, s.StateProvinceCode, s.CountryRegionCode FROM Person.Address a WITH (NOLOCK), Person.StateProvince s WHERE a.StateProvinceID = s.StateProvinceID go SELECT AddressID, AddressLine1, StateProvinceCode, CountryRegionCode FROM Person.AddrState-- WITH (REPEATABLEREAD) --加此项会产生冲突锁提示,因而出错 WHERE StateProvinceCode = 'WA' go drop view Person.AddrState
那么SQL server 2005中READ_COMMITTED_SNAPSHOT隔离级别在存储过程中怎样实现啊?我觉得这个应该可以实现我想要的功能。
SET READ_COMMITTED_SNAPSHOT ON 是基于数据库的,在存储过程中使用会不会影响到其他访问? 如果要设置,是不是这样: 在存储过程开始时: ALTER DATABASE DataBaseName SET READ_COMMITTED_SNAPSHOT ON; 存储过程结束时设置为OFF.
go
CREATE VIEW Person.AddrState WITH SCHEMABINDING AS
SELECT a.AddressID, a.AddressLine1,
s.StateProvinceCode, s.CountryRegionCode
FROM Person.Address a WITH (NOLOCK), Person.StateProvince s
WHERE a.StateProvinceID = s.StateProvinceID
go
SELECT AddressID, AddressLine1, StateProvinceCode, CountryRegionCode
FROM Person.AddrState-- WITH (REPEATABLEREAD) --加此项会产生冲突锁提示,因而出错
WHERE StateProvinceCode = 'WA'
go
drop view Person.AddrState
如果要设置,是不是这样:
在存储过程开始时:
ALTER DATABASE DataBaseName SET READ_COMMITTED_SNAPSHOT ON;
存储过程结束时设置为OFF.