SQL学习笔记八：SQL Server陷阱

--SQL_Server学习笔记之八
--SQL_Server之陷阱篇，整理自：http://topic.csdn.net/u/20080610/15/eba066c7-ae6c-47ea-98f4-aa35ebc5ad90.html?40977

--陷阱一：关于NULL值的比较
    
    --例一:
    declare @a int
    if (@a > 0)
        set @a = 1
    else if not (@a > 0)
        set @a = 2
    else
        set @a = 3
    --查询变量@a
    select @a
    --结果
    /*
        3
    */

    --例二:
    declare @a int
    if @a = null
        set @a = 1
    else if @a = null or 1 = 1
        set @a = 2
    else
        set @a = 3
    --查询变量@a
    select @a
    --结果
    /*
        2
    */
    
    --陷阱一结论：NULL值与所有值比较结果都为False


--陷阱之二：SQL批量语句产生错误会自动回滚
    
    --例一：
    

    create table table1(id int primary key)
    begin tran
        insert into table1 values (1)
        insert into table1 values (1)
        insert into table1 values (2)
    commit tran
    --查询
    select * from table1
    --结果
    /*
        id      消息:(1 行受影响)
                消息 2627，级别 14，状态 1，第 5 行
                违反了 PRIMARY KEY 约束 'PK__table1__1881A0DE'。不能在对象 'dbo.table1' 中插入重复键。
                语句已终止。

                (1 行受影响)
         1
         2
    */
    --可见除错误的那一行，两外两行数据已正常添加
    --例二：我们在begin tran 下面加上 set xact_abort on
        create table table1(id int primary key)
        begin tran
            set xact_abort on
            insert into table1 values (1)
            insert into table1 values (1)
            insert into table1 values (2)
        commit tran
    --再次进行查询，则可以看到没有数据，说明发生了事务回滚
    --说明：在不加xact_abort，发生level级别大于16的错误时，当然也会自动回滚，但是Sql server 2000和2005在处理方式上会有差异

    --最后结论：在事务处理时，请加上set xact_abort on已保证数据的一致性(当然也可以用@@error进行逐句判断)
    --比如：
    create table table1(id int primary key)
    begin tran
        insert into table1 values (1)
        if @@error<>0 goto err
            insert into table1 values (1)
        if @@error<>0 goto err
            insert into table1 values (2)
        if @@error<>0 goto err
            commit tran
        return err:
    rollback tran
    --PS:当 SET XACT_ABORT 为 ON 时，如果 Transact-SQL 语句产生运行时错误，整个事务将终止并回滚。为 OFF 时，只回滚产生错误的 Transact-SQL 语句，而事务将继续进行处理。编译错误 
    --（如语法错误）不受 SET XACT_ABORT 的影响。 
    -- 一般批查询中 SET XACT_ABORT 默认为 OFF, 隐式开启的事务如触发器中SET XACT_ABORT 默认为ON 



--陷阱之三：字符串相等与排序规则

    --例一:SQLServer简体中文版默认的排序规则是Chinese_PRC_CI_AS, 这种情况下是不区分大小写及宽字符的. 
    --        'A' 与 'a' 与 'Ａ'是相等的. 
    --        另外字符串比较时尾随空格是被忽略的

    if 'abc' = 'AＢc  '
        select 1
    --结果为1，说明 'abc' = 'AＢc  '

    --例二:Len函数也是忽略尾随空格的
    select len('abc') union all select len('AＢc  ')
    --结果:
    /*
        3
        3
    */
    --说明:可以用函数DATALength解决
    
    select datalength('abc') union all select datalength('AＢc  ')
    --结果
    /*
        3
        6
    */


--陷阱之四:隐性锁 

    -- 默认情况下select会使用共享锁, 在数据读取完后会立即释放; insert, update, delete会对更改的数据使用排它锁并且一直保持到事务结束. 
    -- 共享锁可以与共享锁,更新锁共存. 排它锁与任何锁都不能共存. 

    -- 例1: 新建一张表 create table table1 (id int), 加入一条记录 insert into table1 values(1) 
    -- 在查询分析器中开两个连接 
    -- 连接1: 
        begin tran
        select * from table1


    --连接2: 
        update table1 set id = 1


    --先执行连接1, 再执行连接2, 都是立即完成. 

    --然后连接1改为: 
    while @@trancount > 0
        rollback tran --如果有未提交的事务就回滚
    begin tran
        update table1 set id = 1


    --连接2不变 

    --先执行连接1, 再执行连接2, 会发现连接2被阻塞. 

    --这时在连接1中单独执行commit tran, 连接2会立即结束. 注:在测试过程中可以用select @@spid查看当前连接的spid, 用sp_lock查看锁信息. 

    --如果要改变隐性锁, 可以使用with关键字. 
    --例2: 
    --连接1: 
    while @@trancount > 0
        rollback tran
    begin tran
        select * from table1 with(holdlock)


    --连接2: 
    update table1 set id = 1


    --先执行连接1, 再执行连接2, 连接2会被阻塞. 因为holdlock改变了共享锁的生存期, 让共享锁保持到事务结束, 而共享锁与排它锁是不能共存的. 

    --连接1改为: 
    while @@trancount > 0
        rollback tran
    begin tran
    select * from table1 with(tablockx)


    --先执行连接1, 再执行连接2, 连接2仍会被阻塞. 这里指定select使用表级排它锁, 该锁会保持到事务结束, 所以这里不用加holdlock. 

    --例3: 
    --连接1: 

    while @@trancount > 0
        rollback tran

    insert into table1 values(2)
    begin tran
    update table1 set id = 3 where id = 2


    --连接2: 

    select * from table1


    --先执行连接1, 再执行连接2, 连接2会被阻塞. 

    --连接2: 
    select top 1 * from table1 
    --再执行连接2, 连接2会立即结束. 这里update的过程应该先是查询id=2的记录, 查询时使用共享锁, 不满足id=2的记录共享锁立即释放, 满足id=2的记录把共享锁升级为排它锁并保持到事务 

    --结论: 所以第一次执行连接2会因为无法获取id=2的记录的共享锁而被阻塞, 而第二次执行不会被阻塞.