• 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 tran
    
    update 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.代码:

    /// <summary>
            /// 奖金处理的业务
            /// </summary>
            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<Employees>().Where(p => p.groups == "grouup1").ToList();
                    //取得groupp1组的人数
                    int count = employees.Count;
                    //把奖金进行employees平分
                    decimal salary = 20000 / count;
                    //对每个人的值进行跟新
                    foreach (var emoloyee in employees)
                    {
                        dbcontext.Set<Employees>().Attach(emoloyee);
                        //状态是可修改的
                        dbcontext.Entry<Employees>(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();
            }
    

     点击button数据库更新成功。

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

  • 相关阅读:
    mq概念
    Mac Xampp 安装redis 及 安装php-redis扩展
    rabbitmq死信队列(延迟队列)demo
    rabbitmq生产与消费测试
    RabbitMQ各方法详解
    Mac git old mode 100644 new mode 100755 mac目录权限问题
    mac安装redis
    msql创建用户并授权
    mac apache php 访问失败
    Kubernetes入门学习--在Ubuntu16.0.4安装配置Minikube
  • 原文地址:https://www.cnblogs.com/sunliyuan/p/6241451.html
Copyright © 2020-2023  润新知