SQLServer陷阱

最新推荐文章于 2025-04-17 09:42:30 发布

原创最新推荐文章于 2025-04-17 09:42:30 发布 · 1.1k 阅读

CC 4.0 BY-SA版权

文章标签：

#sqlserver #table #insert #sql #null #delete

本文旨在指出一些在使用SQLServer过程中容易犯的错误, 希望能给您带来帮助. 若没有特殊说明, 本文是指在MS SQLServer 2000简体中文版的默认配置环境中. 一, NULL与布尔数据类型 Transact-SQL中存在Boolean类型, if 后面的表达式的计算结果一般是Boolean类型, 但无法使用 declare 定义Boolean类型的变量. Boolean数据类型有三种取值, TRUE, FALSE, UNKNOWN, 第3种取值通常会被人忽视从而导致逻辑错误. 默认情况下SET ANSI_NULLS为ON, 在逻辑表达式中如果你忽略了NULL的存在, 结果可能会异于你所想. 例1:

SQL code


declare @a int
if (@a > 0)
    set @a = 1
else if not (@a > 0)
    set @a = 2
else
    set @a = 3

结果@a的值应该是3, 因为NULL>0的值为UNKNOWN, NOT UNKNOWN的值还为UNKNOWN. 例2:

SQL code


declare @a int
if @a = null
    set @a = 1
else if @a = null or 1 = 1
    set @a = 2
else
    set @a = 3

结果@a的值应该是2, 因为NULL = 0的值为UNKNOWN, UNKNOWN or TRUE的值为TRUE. 二, 运行时错误与自动回滚事务有些人认为一个批查询在执行中发生了错误, 这个查询就会中止, 其实是错误的. 例1:

SQL code


declare @i int
set @i = 1 / 0
set @i = 1
select @i

结果会先报一个服务器: 消息 8134，级别 16，状态 1，行 2 遇到被零除错误。然后输出结果集 1. 例2:

SQL code


set xact_abort on
declare @i int
set @i = 1 / 0
set @i = 1
select @i

结果只报错, 不会输出结果. 例3: 请在查询分析器中新建连接执行

SQL code


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

第二个insert会产生违反主键约束错误, 但是执行结束后你会发现事务已经提交并且table1中已经有两行记录1与2 例4:

SQL code


set xact_abort on

create table table2(id int primary key)

begin tran
insert into table2 values (1)
insert into table2 values (1)
insert into table2 values (2)
commit tran

执行结束后, table2中没有记录, 说明事务已经回滚. 当 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' 与 'Ａ'是相等的. 另外字符串比较时尾随空格是被忽略的.(顺便提一下len()也是忽略尾随空格的).

SQL code


if 'abc' = 'AＢc  '
    select 1

结果会输出1 宽字符,尾随空格常常被忽视. 四, 隐性锁 select 默认情况下会给表加上共享锁并且在执行完成后就释放, insert, update, delete默认情况下会给表加上排它锁并且一直保持到事务结束. 共享锁可以与共享锁,更新锁共存. 排它锁与任何锁都不能共存. 例1: 新建一张表 create table table1 (id int) 在查询分析器中开两个连接连接1:

SQL code


begin tran
select * from table1

连接2:

SQL code


insert into table1 values(1)

先执行连接1, 再执行连接2, 都是立即完成. 然后连接1改为:

SQL code


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:

SQL code


while @@trancount > 0
    rollback tran
begin tran
select * from table1 with(holdlock)

连接2:

SQL code


insert into table1 values(1)

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

SQL code


while @@trancount > 0
    rollback tran
begin tran
select * from table1 with(tablockx)

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

SQL code


if 'abc' = 'AＢc  '
    select 1

SQL code


begin tran
select * from table1

连接2:

SQL code


insert into table1 values(1)

先执行连接1, 再执行连接2, 都是立即完成. 然后连接1改为:

SQL code


while @@trancount > 0
    rollback tran --如果有未提交的事务就回滚
begin tran
update table1 set id = 1

SQL code


while @@trancount > 0
    rollback tran
begin tran
select * from table1 with(holdlock)

连接2:

SQL code


insert into table1 values(1)

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

SQL code


while @@trancount > 0
    rollback tran
begin tran
select * from table1 with(tablockx)

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

[Quote=引用 42 楼 hisi 的回复:]

SQL code

 
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查看锁信息. [/Quote] 连接2没有阻塞？还是lock的时间太短呢？没有明显反应啊~~~~~ 不过按照BOOK ONLINE的说明楼主说的很不错 “通常，读操作获取共享锁，写操作获取排它锁。在更新操作的初始阶段读取数据时，会获取更新锁。更新锁与共享锁兼容。此后，如果更改了数据，更新锁会提升为排它锁。有时在更改数据时，会在获取排它锁之前暂时获取更新锁。此后，该更新锁会自动提升为排它锁。”

感谢 gino_tone, Iam_robin 第四, 隐性锁, 存在问题, 因为insert的时候并不需要获取已存在数据的排它锁, 所以例1中并不会阻塞. 为了使锁定的成本减至最少,SQL Server 自动将资源锁定在适合任务的级别. 这里会使用行级锁. ---------------------------------------------------------------------------------- 下面的内容已修正. 若您发现其中有不正确的地方, 请回贴指出, 谢谢! 四, 隐性锁默认情况下select会使用共享锁, 在数据读取完后会立即释放; insert, update, delete会对更改的数据使用排它锁并且一直保持到事务结束. 共享锁可以与共享锁,更新锁共存. 排它锁与任何锁都不能共存. 例1: 新建一张表 create table table1 (id int), 加入一条记录 insert into table1 values(1) 在查询分析器中开两个连接连接1:

SQL code

begin tran
select * from table1

连接2:

SQL code


update table1 set id = 1

先执行连接1, 再执行连接2, 都是立即完成. 然后连接1改为:

SQL code


while @@trancount > 0
    rollback tran --如果有未提交的事务就回滚
begin tran
update table1 set id = 1

SQL code


while @@trancount > 0
    rollback tran
begin tran
select * from table1 with(holdlock)

连接2:

SQL code


update table1 set id = 1

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

SQL code


while @@trancount > 0
    rollback tran
begin tran
select * from table1 with(tablockx)

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

SQL code


while @@trancount > 0
    rollback tran

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

连接2:

SQL code


select * from table1

先执行连接1, 再执行连接2, 连接2会被阻塞. 连接2: select top 1 * from table1 再执行连接2, 连接2会立即结束. 这里update的过程应该先是查询id=2的记录, 查询时使用共享锁, 不满足id=2的记录共享锁立即释放, 满足id=2的记录把共享锁升级为排它锁并保持到事务结束. 所以第一次执行连接2会因为无法获取id=2的记录的共享锁而被阻塞, 而第二次执行不会被阻塞.