--
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' 与 'A'是相等的.
-- 另外字符串比较时尾随空格是被忽略的
if ' abc ' = ' ABc '
select 1
-- 结果为1,说明 'abc' = 'ABc '
-- 例二:Len函数也是忽略尾随空格的
select len ( ' abc ' ) union all select len ( ' ABc ' )
-- 结果:
/*
3
3
*/
-- 说明:可以用函数DATALength解决
select datalength ( ' abc ' ) union all select datalength ( ' ABc ' )
-- 结果
/*
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的记录的共享锁而被阻塞, 而第二次执行不会被阻塞.
-- 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' 与 'A'是相等的.
-- 另外字符串比较时尾随空格是被忽略的
if ' abc ' = ' ABc '
select 1
-- 结果为1,说明 'abc' = 'ABc '
-- 例二:Len函数也是忽略尾随空格的
select len ( ' abc ' ) union all select len ( ' ABc ' )
-- 结果:
/*
3
3
*/
-- 说明:可以用函数DATALength解决
select datalength ( ' abc ' ) union all select datalength ( ' ABc ' )
-- 结果
/*
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的记录的共享锁而被阻塞, 而第二次执行不会被阻塞.