SqlServer性能优化 提高并发性能二（九）

SqlServer性能优化 提高并发性能二（九）

补充上一篇修改用非聚集索引：

update Employee set age=age+1 from Employee with(index=nc_Employee_Age) where age<30

执行计划：

并发访问控制隔离级别：

1.读提交：默认行为，读取时请求S锁

set transaction isolation level read committed select * from Employee where age=34

2.   脏读：读取时不请求S锁，不会受到其他X锁限制

set transaction isolation level read uncommitted select * from Employee

3. 已提交快照读：更新时将老的数据复制到 Tempdb：read_committed_snapshot

alter database HRDB set read_committed_snapshot on

设置成单用户模式：

设置事物：

begin tran update Employee set age=age+1 where age>=30

执行语句：

select * from Employee

这条语句也可以执行：

select * from Employee where age>30

这条可以执行：

update Employee set age=age+1 from Employee with(index=nc_Employee_Age) where age<30

没有复制的还是从索引或基表中读取。所以可以读取出数据

这条不能执行：

update Employee set age=age+1 from Employee with(index=nc_Employee_Age) where age>30

打开数据库的读提交快照。 对记录进行操作时，会把排他锁的数据放到Tempdb数据库中，访问的时候直接读出Tempdb的数据。

4.可重复读：事物结束前，不释放获取的S锁，可能会形成死锁

create table Products(id int identity(1,1),name varchar(500),UnitPrice money) delete from Products where id=2 insert Products values ('p1',13) insert Products values('p2',5) --业务逻辑：单价大于10的优惠10 begin tran declare @UnitPrice money set @UnitPrice=(select @UnitPrice from Products where id=1) --执行等待的时间 waitfor delay '00:00:20' if @UnitPrice>10 update Products set UnitPrice=UnitPrice-10 where id=1 commit tran

第二个人执行打六折的业务：

update Products set UnitPrice=UnitPrice*0.6

结果：

事务中尽量不要放查询语句：13*0.6=7.8      7.8-10=-2.2

实在要查询语句，如何解决呢？

删除上述表：drop table Products   重新创建

1. --在开启事务之前  设置事务的级别  可重复读

--在开启事务之前 设置事务的级别 可重复读 set transaction isolation level repeatable read --业务逻辑：单价大于10的优惠10 begin tran declare @UnitPrice money set @UnitPrice=(select @UnitPrice from Products where id=1) --执行等待的时间 waitfor delay '00:00:20' if @UnitPrice>10 update Products set UnitPrice=UnitPrice-10 where id=1 commit tran

2.打六折：

update Products set UnitPrice=UnitPrice*0.6

结果：

with(updlock) 可重复读的方式 可以保护线程。代码如下：

begin tran declare @UnitPrice money set @UnitPrice=(select @UnitPrice from Products with(updlock) where id=1) --执行等待的时间 waitfor delay '00:00:20' if @UnitPrice>10 update Products set UnitPrice=UnitPrice-10 where id=1 commit tranupdate Products set UnitPrice=UnitPrice*0.6

5. 串行化：访问的行和按顺序下一行放置范围锁，防止不必要操作与插入数据

业务背景：给分组为：‘group1’的员工发奖金，加入了新的员工

create table Employees(id int identity(1,1),name varchar(500),groups varchar(500),salary money) insert Employees values('caojian','grouup1',3000) insert Employees values('ligang','grouup1',1000) insert Employees values('huang','grouup2',1500) insert Employees values('sunliyuan','grouup2',2000)

业务逻辑的事务语句：

begin tran declare @count int set @count=(select COUNT(*) from Employees where groups='grouup1') declare @avgsalary money set @avgsalary=20000/@count waitfor delay '00:00:20' update Employees set salary=salary+@avgsalary where groups='grouup1' commit tran

第二个线程执行的语句：

insert Employees values ('newemployee','grouup1',0)

这种结果是不对的：

--设置串行化

set transaction isolation level serializable begin tran declare @count int set @count=(select COUNT(*) from Employees where groups='grouup1') declare @avgsalary money set @avgsalary=20000/@count waitfor delay '00:00:20' update Employees set salary=salary+@avgsalary where groups='grouup1' commit tran

进行添加：

insert Employees values ('newemployee','grouup1',0)

执行查询语句：

针对group创建索引：

--针对group创建聚集索引 create clustered index c_Employees_group on Employees (groups)

执行事务：

--设置串行化 set transaction isolation level serializable begin tran declare @count int set @count=(select COUNT(*) from Employees where groups='grouup1') declare @avgsalary money set @avgsalary=20000/@count waitfor delay '00:00:20' update Employees set salary=salary+@avgsalary where groups='grouup1' commit tran

执行以下三条语句：

insert Employees values ('newemployee','grouup1',0)insert Employees values ('newemployee','grouup2',0)insert Employees values ('newemployee','grouup3',0)

grouup1有影响，grouup2和grouup3无影响。

6. 快照：比已提交快照读取更严格，试图对修改数据应用X（排他锁），如果已发生改变，事物失败 allow_snapshot_isolation

创建表：

create table SnapShotTB(id int identity(1,1),name varchar(500),age int)insert SnapShotTB values('caojian',33)

给数据库进行配置：

--打开配置数据库的一个选项alter database HRDB--允许快照隔离set allow_snapshot_isolation on

设置事物的隔离级别：

--设置事物的隔离级别为快照set transaction isolation level snapshot begin transaction declare @age int set @age=(select age from SnapShotTB where name='caojian') waitfor delay '00:00:20' update SnapShotTB set age =age+1 where name='caojian'commit tran

第二个线程：

update SnapShotTB set age =age-1 where name='caojian'

报的错误：

减少阻塞与死锁的建议：

1.合适的索引

2.合适的分区

3.调整合适的隔离级别

4.查询条件的有限性

5.相同的顺序操作资源

6.短的事务

.NET 调用的案例：（EF CodeFirst）

1.连上数据库。

2.引入命名空间。

3.在领域层引入命名空间：

using System.Transactions; using System.Data; using System.Linq;

4.代码：

///

/// 奖金处理的业务 ///

public void ProcessSalary() { TransactionOptions option = new TransactionOptions(); //指定的隔离级别(串行化) option.IsolationLevel = System.Transactions.IsolationLevel.Serializable; using (TransactionScope scope=new TransactionScope(TransactionScopeOption.Required,option)) { //连到数据访问的上下文 HRUser dbcontext = new HRUser(); var employees = dbcontext.Set().Where(p => p.groups == "grouup1").ToList(); //取得groupp1组的人数 int count = employees.Count; //把奖金进行employees平分 decimal salary = 20000 / count; //对每个人的值进行跟新 foreach (var emoloyee in employees) { dbcontext.Set().Attach(emoloyee); //状态是可修改的 dbcontext.Entry(emoloyee).State = System.Data.Entity.EntityState.Modified; emoloyee.salary = emoloyee.salary + salary; } dbcontext.SaveChanges(); //事物的完成 scope.Complete(); } }

调用：

protected void Button1_Click(object sender, EventArgs e) { Employees es = new Employees(); es.ProcessSalary(); }

7. 索引对隔离级别的影响、阻塞的监视

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。