|
|
本文旨在指出一些在使用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被阻塞. 这时在连接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会被阻塞. 因为holdlock改变了共享锁的生存期,让共享锁保持到事务结束,而共享锁与排它锁是不能共存的. 连接1改为:
SQL code
while
@@trancount
>
0
rollback
tran
begin
tran
select
*
from
table1
with
(tablockx)
先执行连接1,连接2仍会被阻塞. 这里指定select使用表级排它锁,该锁会保持到事务结束,所以这里不用加holdlock. 未完...
?
?
继续. 三,这种情况下是不区分大小写及宽字符的. 'A' 与 'a' 与 'Ａ'是相等的. 另外字符串比较时尾随空格是被忽略的.(顺便提一下len()也是忽略尾随空格的).
SQL code
if
'
abc
'
=
'
AＢc
'
select
1
结果会输出1 宽字符,更新锁共存. 排它锁与任何锁都不能共存. 例1: 新建一张表 create table table1 (id int) 在查询分析器中开两个连接 连接1:
SQL code
begin
tran
select
*
from
table1
连接2:
SQL code
insert
into
table1
values
(
1
)
先执行连接1,都是立即完成. 然后连接1改为:
SQL code
while
@@trancount
>
0
rollback
tran
--
如果有未提交的事务就回滚
begin
tran
update
table1
set
id
=
1
连接2不变 先执行连接1,可以使用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,而共享锁与排它锁是不能共存的. 连接1改为:
SQL code
while
@@trancount
>
0
rollback
tran
begin
tran
select
*
from
table1
with
(tablockx)
先执行连接1,所以这里不用加holdlock. 未完...
?
?
[Quote=引用 42 楼 hisi 的回复:] ?
SQL code
while
@@trancount
>
0
rollback
tran
--
如果有未提交的事务就回滚
begin
tran
update
table1
set
id
=
1
连接2不变 先执行连接1,用sp_lock查看锁信息. [/Quote] 连接2没有阻塞？还是lock的时间太短呢？ 没有明显反应啊~~~~~ 不过按照BOOK ONLINE的说明楼主说的很不错 “通常，读操作获取共享锁，写操作获取排它锁。在更新操作的初始阶段读取数据时，会获取更新锁。更新锁与共享锁兼容。此后，如果更改了数据，更新锁会提升为排它锁。有时在更改数据时，会在获取排它锁之前暂时获取更新锁。此后，该更新锁会自动提升为排它锁。”
感谢 gino_tone,Iam_robin 第四,隐性锁,存在问题,因为insert的时候并不需要获取已存在数据的排它锁,所以例1中并不会阻塞. 为了使锁定的成本减至最少,SQL Server 自动将资源锁定在适合任务的级别. 这里会使用行级锁. ---------------------------------------------------------------------------------- 下面的内容已修正. 若您发现其中有不正确的地方,请回贴指出,谢谢! 四,隐性锁 默认情况下select会使用共享锁,在数据读取完后会立即释放; insert,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,可以使用with关键字. 例2: 连接1:
SQL code
while
@@trancount
>
0
rollback
tran
begin
tran
select
*
from
table1
with
(
holdlock
)
连接2:
SQL code
update
table1
set
id
=
1
先执行连接1,所以这里不用加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: select top 1 * from table1 再执行连接2,连接2会立即结束. 这里update的过程应该先是查询id=2的记录,查询时使用共享锁,不满足id=2的记录共享锁立即释放,满足id=2的记录把共享锁升级为排它锁并保持到事务 结束. 所以第一次执行连接2会因为无法获取id=2的记录的共享锁而被阻塞,而第二次执行不会被阻塞.
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
