SqlServe系列(二)

事务

多条sql作为一个整体提交给数据库系统，要么全部执行完成，要么全部取消。是一个不可分割的逻辑单元。

ACID

a) (Atomic )原子性：要么都成功 要么都失败

c) (Consistency)一致性：事务执行完，数据都是正确

i) (Isolation)隔离性：两个事务同时操作一张表（一列/一行），B事务要么是在A事务前完成，要么在A事务完成 后完成(锁表)

d) (Durability)持久性

演示代码：

---开启事务
 begin tran
 --错误扑捉机制，看好啦，这里也有的。并且可以嵌套。
 begin try  
    --语句正确
    insert into [Company] (Name,CreateTime,CreatorId) values ('阿里巴巴集团','2019-03-20',1)
    --CreatorId为int类型，出错
    insert into [Company] (Name,CreateTime,CreatorId) values ('百度科技有限公司','2019-03-20','Test')
    --语句正确
    insert into [Company] (Name,CreateTime,CreatorId) values ('腾讯科技有限公司','2019-03-20',2)
 end try
 begin catch
    select Error_number() as ErrorNumber,  --错误代码
           Error_severity() as ErrorSeverity,  --错误严重级别，级别小于10 try catch 捕获不到
           Error_state() as ErrorState ,  --错误状态码
           Error_Procedure() as ErrorProcedure , --出现错误的存储过程或触发器的名称。
           Error_line() as ErrorLine,  --发生错误的行号
           Error_message() as ErrorMessage  --错误的具体信息
    if(@@trancount>0) --全局变量@@trancount，事务开启此值+1，他用来判断是有开启事务
       rollback tran  ---由于出错，这里回滚到开始，第一条语句也没有插入成功。撤销之后，@@trancount变为0，但是还有其他情况，看下面的补充
 end catch
 if(@@trancount>0)
 commit tran  --如果成功Company表中，将会有3条数据。
 

补充：

事务车撤回后@@trancount变化

 锁

锁机制详解  锁知识以及应用  应用二

为什么要加锁？

为了避免多用户同时访问一个资源造成的问题。

多个事务同时访问同一资源会造成哪些问题？

（1）数据丢失

 两个事务都同时更新一行数据，但是第二个事务却中途失败退出，导致对数据的两个修改都失效了。这是因为系统没有执行任何的锁操作，因此并发事务并没有被隔离开来。因为多个事务对同一资源并发访问的话会造成死锁，数据库会随机取消其中几个事务，导致数据更新不成功，数据丢失

（2）不可重复读取 

一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，被另一个事务修改并提交了。

（3）脏读

 一个事务开始读取了某行数据，但是另外一个事务已经更新了此数据但没有能够及时提交。这是相当危险的，因为很可能所有的操作都被回滚。

事务详解，这个讲的很好

乐观锁

我认为没有并发，所以读取/更新就不加锁了。性能高

如何实现？

（1）更新数据做判断，可以用其中一个字段或者使用全部字段做判断

比如说我要修改公司表中Id为1的数据，将Name更改为"测试"，所以我们可以在原先思路的基础上加and Name='TEST' 判断，TEST是原先的Name值。

update [dbo].[Company]  set Name='测试'  where  id=1  and Name='TEST'

（2）通过时间戳

就是添加timestamp类型的列。

（3）版本号

就是在表中加一列，用来做更新的版本

缺点：因为乐观锁中的这些判断都是在程序中执行的，但是万一有其他渠道下更改了程序中要处理的数据，比如直接在数据库中做更改，很大可能就会出问题，不能完全保证数据的安全。在实际开发中不推荐

乐观锁讲解补充

悲观锁

我认为更改数据的时候，就会有其它渠道来修改数据，一定会有并发

（1）共享锁(S锁)

 允许其他事务来查询数据，但是不允许更改。

 select的时候会自动加上共享锁，该条语句执行完，共享锁立即释放，与事务是否提交没有关系。

 显式通过添加(holdlock)来显式添加共享锁（比如给select语句显式添加共享锁），当在事务里的时候，需要事务结束，该共享锁才能释放。

 同一资源，共享锁和排它锁不能共存，意味着update之前必须等资源上的共享锁释放后才能进行。

 共享锁和共享锁可以共存在一个资源上，意味着同一个资源允许多个线程同时进行select。

（2）排它锁(X锁)

排它锁用于数据修改，当资源上加了排他锁时，其他请求读取或修改这个资源的事务都会被阻塞，直到排他锁被释放为止。

 update(或 insert 或 delete)的时候加自动加上排它锁，该条语句执行完，排它锁立即释放，如果有事务的话，需要事务提交，该排它锁才能释放。

 显式的通过添加(xlock)来显式的添加排它锁（比如给select语句显式添加排它锁），如果有事务的话，需要事务提交，该排它锁才能释放。

 同一资源，共享锁和排它锁不能共存，意味着update之前必须等资源上的共享锁释放后才能进行。

（3）更新锁(U锁)

更新锁是先查询后更新，利用更新锁来解决死锁问题，要比xlock性能高一些，因为加了updlock后，其他线程是可以进行select的，更新锁和共享锁是可以兼容的。

 更新锁只能显式的通过(updlock)来添加，当在事务里的时候，需要事务结束，该更新锁才能释放。

 共享锁和更新锁可以同时在同一个资源上，即加了更新锁，其他线程仍然可以进行select。

 更新锁和更新锁不能共存(同一时间同一资源上不能存在两个更新锁)。

 更新锁和排它锁不兼容。

 利用更新锁来解决死锁问题，要比xlock性能高一些，因为加了updlock后，其他线程是可以进行select的。

（4）意向锁

在SQL SERVER中，准确来说，意向锁并不是一种独立的锁，其主要作用在于获取锁的控制粒度(如，页，表，行等)。

意向锁分为三种：意向共享 (IS)、意向排他 (IX) 和意向排他共享 (SIX)。 意向锁可以提高性能，因为数据库引擎仅在表级检查意向锁来确定事务是否可以安全地获取该表上的锁，而不需要检查表中的每行或每页上的锁以确定事务是否可以锁定整个表.

　T1：select * from table (xlock) where id=10

　T2：select * from table (tablock) 

分析：T1线程执行该语句时，会对该表id=10的这一行加排他锁，同时会对整个表加上意向排它锁(IX)，当T2执行的时候，不需要逐条去检查资源，只需要看到该表已经存在【意向排它锁】，就直接等待。

PS： update table set xx=xx where id=1， 不光会对id=1的这条记录加排它锁，还会对整张表加意向排它锁。

其实锁是保证事务，事务保证的是整个数据要么全部成功，要么全部失败

锁的颗粒：行锁、页锁、表锁

(1). rowlock：行锁---对每一行加锁，然后释放。(对某行加共享锁)

(2). paglock：页锁---1执行时，会先对第一页加锁，读完第一页后，释放锁，再对第二页加锁，依此类推。(对某页加共享锁)

　　假设前10行记录恰好是一页(当然，一般不可能一页只有10行记录），那么T1执行到第一页查询时，并不会阻塞T2的更新。

(3). tablock：表锁---对整个表加锁，然后释放。 (对整张表加共享锁)

注：

　　1. 以上三种锁执行完该语句后即可释放，无须等待事务的提交，与事务是否提交没有关系。

　　2. 以上三种锁划分的角度不同，都是共享锁，所以他们相互之间是可以共存的。

死锁

在高并发项目中，死锁是无法避免的。

如何来降低死锁的概率

（1）严格按照执行顺序 

（2）分表，加索引（提高数据库的性能）

（3）读写分离

（4）设置死锁的时间（已经发现死锁等待，果断放弃一个）

（5）降低事务级别

存储过程

 存储过程是一组为了完成特定功能的SQL语句集合，经过编译之后存储在数据库。所以调用的时候可以直接执行，

优点：

性能相比与普通sql语句稍快。

降低数据库的吞吐量。因为执行存储过程的时候只需要传参数进来直接执行就可以，在程序中处理的话就是可能得调用多次数据库才能完成。

项目需求更新方便，比如一些简单的业务逻辑需要更改的时候可以直接在存储过程中更改，不必必须在项目在更改并重新发布。

缺点：

sql语句过多。

数据库压力大(因为业务逻辑都在数据库，所以现在比较少用)

分页存储过程：

--exec dbo.Pro_GetPageData 'Tencent_Subject_001','*','20','2','','Id','1'
 
--如果遇到有存储过程Pro_GetPageData  就先删除掉
if exists (select * from sysobjects where type = 'P' and name = 'Pro_GetPageData')
    drop procedure dbo.spStockTakingPaperList;
GO
 

 --创建存储过程
CREATE PROCEDURE [dbo].[Pro_GetPageData] 
 ( 
     @TableName        nvarchar(3000),            -- 表名 
     @ReturnFields    nvarchar(3000) = '*',    -- 需要返回的列  
     @PageSize        int = 10,                -- 每页记录数 
     @PageIndex        int = 0,                -- 当前页码 
     @Where            nvarchar(3000) = '',        -- 查询条件 
     @OrderBy        nvarchar(200),            -- 排序字段名 最好为唯一主键 
     @OrderType        int = 1                    -- 排序类型 1:降序 其它为升序 
 ) 
 AS 
     DECLARE @TotalRecord int 
     DECLARE @TotalPage int 
     DECLARE @CurrentPageSize int 
     DECLARE @TotalRecordForPageIndex int 
     declare @CountSql nvarchar(4000)  
   
     if @OrderType = 1 
         BEGIN 
             set @OrderBy = ' Order by ' + REPLACE(@OrderBy,',',' desc,') + ' desc ' 
         END 
     else 
         BEGIN 
             set @OrderBy = ' Order by ' + REPLACE(@OrderBy,',',' asc,') + ' asc '    
         END 
     -- 总记录 
     set @CountSql='SELECT @TotalRecord=Count(*) From '+@TableName+' '+@Where 
     execute sp_executesql @CountSql,N'@TotalRecord int out',@TotalRecord out 
     SET @TotalPage=(@TotalRecord-1)/@PageSize+1 
     -- 查询页数不得大于总页数 
     if(@PageIndex > @TotalPage) 
         set @PageIndex = @TotalPage 
     SET @CurrentPageSize=(@PageIndex-1)*@PageSize
   
     -- 返回记录
 
     set @TotalRecordForPageIndex=@PageIndex*@PageSize
  
     exec    ('SELECT *
 
             FROM (SELECT TOP '+@TotalRecordForPageIndex+' '+@ReturnFields+', ROW_NUMBER() OVER ('+@OrderBy+') AS ROWNUM
 
             FROM '+@TableName+ ' ' + @Where +' ) AS TempTable 
             WHERE TempTable.ROWNUM >  
             '+@CurrentPageSize) 
     -- 返回总页数和总记录 
     SELECT @TotalPage as PageCount,@TotalRecord as RecordCount