
--
=========================================================================================
--
2007/06/06 SQL学习-- 事务
--
=========================================================================================
use
test
go

--
--
******************************************************************************************
--
===================================================================================================
--
事务与锁
--
事务(Transaction)的作用是保证一系列的数据操作可以全部正确完成,不会造成数据操作到一半未完成,
--
而导致数据的完整性出错。
--
锁(Lock)的作用是保证数据在操作的过程中不会受到任何的其他影响。
--
===================================================================================================
--
事务是作为单个逻辑工作单元执行的一系列工作。必须具备的四个特性:
--
1)原子性
--
2)一致性
--
3)隔离性:由并发事务所作的修改必须与其他并发事务所作的修改隔离。
--
4)持久性:在事务完成后,其操作结果对于系统的影响应该是永久的。
--
--
===================================================================================================
select
*
from
authers
select
*
from
books
delete
authers
where
author_id
=
4

delete
books
where
book_id
=
8
--
--
开始事务
begin
tran
declare
@bid
int
--
添加一本书
insert
books
values
(
8
,
'
ADO.NET
'
,
4
,
300
)
if
@@error
>
0
goto
tranrollback
set
@bid
=
@@identity
--
添加一个作者
insert
authers
(author_id,lastname,firstname,company)
values
(
4
,
'
bin
'
,
'
song
'
,
'
21CNIT
'
)
--
print '添加成功 !'
if
@@error
>
0
goto
tranrollback
--
tranrollback:
if
@@error
>
0
or
@@rowcount
<>
1
rollback
tran
--
如果发生错误则回滚事务
else
commit
tran
--
如果没有发生错误则提交事务
go



--
--
事务的执行方式分为三种:
--
1)显式事务
--
2)自动提交事务(默认的方式)
--
每一个T-SQL语句都是一个自动提交的事务
--
3)隐式事务
--
当执行 set implicit_transactions on 语句后,进入隐式模式
--
隐式事务产生的是一个连续的事务链
--
=================================================================================================
--
编写事务的原则:
--
1)事务要尽可能简短
--
2)在事务中访问的数据量要尽量最少
--
3)浏览数据时尽量不要打开事务
--
4)在事务处理期间尽量不要请求用户输入

--
编写事务
--
select
*
from
books
--
事务开始
begin
tran
--
insert
books
(book_id,title)
values
(
10
,
'
wo
'
)
if
@@error
>
0
or
@@rowcount
<>
1
goto
tranrollback
--
insert
books
(book_id,title)
values
(
11
,
'
me
'
)
if
@@error
>
0
or
@@rowcount
<>
1
goto
tranrollback
--
select
*
from
books
--
delete
books
where
book_id
=
15
if
@@error
>
0
or
@@rowcount
<>
1
begin
tranrollback:
rollback
tran
--
回滚事务
end
else
commit
tran
--
事务结束
--
查看事务结束后的数据集
select
*
from
books



--
嵌套事务
--
嵌套事务里并不是将嵌套的语句完全执行完毕,嵌套事务会将内层事务的commit语句忽略,只提交最外层事务的commit语句
--
在内层事务里,rollback语句仍然有效,但只是回滚到外层事务开始之前的状态
--
begin
tran
print
'
当前事务嵌套层次为:
'
+
cast
(
@@trancount
as
nvarchar
(
5
))
insert
books
(book_id,title)
values
(
20
,
'
qiantao
'
)
if
@@error
>
0
or
@@rowcount
<>
1
goto
tranrollback
--
嵌套开始
begin
tran
print
'
当前事务嵌套层次为:
'
+
cast
(
@@trancount
as
nvarchar
(
5
))
insert
books
(book_id,title)
values
(
25
,
'
qiantao1
'
)
if
@@error
>
0
or
@@rowcount
<>
1
rollback
tran
else
commit
tran
--
嵌套结束
print
'
当前事务嵌套层次为:
'
+
cast
(
@@trancount
as
nvarchar
(
5
))
if
@@error
>
1
begin
tranrollback:
rollback
tran
end
else
commit
tran
go
--
select
*
from
books


--
事务保存点
save
tran
保存点名称
--
设置好事务保存点之后,可以让事务回滚到该保存点时的状态
--
在一个事务中可以设置多个事务保存点,可以回滚到任一个事务保存点所保存的状态


--
事务的隔离级别由低到高分为5个级别:
--
1)Read uncommitted 不隔离数据
--
2) Read committed 不允许读取没有提交的数据,这是SQL Server默认的隔离级别
--
3) Repeatable Read 在事务中锁定所读取的数据不别人修改和删除,如此可以保证在事务中每次读取到的数据都是一致的
--
4) Snapshot 快速隔离,
--
5) Serializable 将事务所要用到的数据表全部锁定
set
tran
isolation
level
{
read
uncommitted
|
read
committed
|
Repeatable
Read
|
Snapshot
|
Serializable
}
[
;
]




--
=======================================================================================================
--
锁
--
锁的作用是将数据临时锁定只提供给一个进程或程序使用,并防止其他的进程或程序修改或读取
--
锁是由并发性控制来决定的
--
并发性就是多个进程或程序同时对数据进行处理的性质
--
乐观并发性控制,悲观并发性控制
--
锁定模式:
--
1)共享锁
--
2)更新锁
--
3)排他锁
--
4)意向锁
--
5)架构锁
--
6)大容量更新锁
--
7)键范围锁
--
死锁(Deadlock)
--
在两个或多个任务中,如果每个任务都锁定了自己的资源,却又在等待其他事务释放资源,此时就会造成死锁。
--
=======================================================================================================
--
事务的示例:
--
用事务在类别表里插入两条记录,然后将插入的最后一条记录删除,看看成功提交事务的结果
select
*
from
Categories
order
by
Categoryid
desc
--
begin
tran
--
insert
Categories(CategoryName)
values
(N
'
午饭
'
)
if
@@error
>
0
or
@@rowcount
<>
1
goto
rb
--
insert
Categories(CategoryName)
values
(N
'
睡觉
'
)
if
@@error
>
0
or
@@rowcount
<>
1
goto
rb
--
select
*
from
Categories
order
by
Categoryid
desc
--
delete
Categories
where
CategoryID
=
(
select
max
(CategoryID)
from
Categories)
if
@@error
>
0
or
@@rowcount
<>
1
begin
rb:
rollback
tran
end
else
commit
tran

select
*
from
Categories
order
by
Categoryid
desc




--
error tran
--
select
*
from
Categories
order
by
Categoryid
desc
--
begin
tran
--
insert
Categories(CategoryName)
values
(N
'
午饭1
'
)
if
@@error
>
0
or
@@rowcount
<>
1
goto
rb
--
insert
Categories(CategoryName)
values
(N
'
睡觉1
'
)
if
@@error
>
0
or
@@rowcount
<>
1
goto
rb
--
select
*
from
Categories
order
by
Categoryid
desc
--
delete
Categories
where
CategoryID
=
15
if
@@error
>
0
or
@@rowcount
<>
1
begin
rb:
rollback
tran
end
else
commit
tran

select
*
from
Categories
order
by
Categoryid
desc
--


--
嵌套的事务
--
begin
tran
print
N
'
当前事务的层次为:
'
+
cast
(
@@trancount
as
nvarchar
(
5
))
insert
Products (ProductName,CategoryID)
values
(
'
西瓜汁
'
,
1
)
if
@@error
>
0
or
@@rowcount
<>
1
goto
rb
insert
Products (ProductName,CategoryID)
values
(
'
草莓汁
'
,
1
)
if
@@error
>
0
or
@@rowcount
<>
1
goto
rb
--
开始嵌套
begin
tran
print
N
'
当前事务的层次为:
'
+
cast
(
@@trancount
as
nvarchar
(
5
))
insert
Products (ProductName,CategoryID)
values
(
'
菠萝汁
'
,
1
)
if
@@error
>
0
or
@@rowcount
<>
1
goto
rb
else
commit
tran
--
嵌套结束
print
N
'
当前事务的层次为:
'
+
cast
(
@@trancount
as
nvarchar
(
5
))
if
@@error
>
0
begin
rb:
rollback
tran
end
else
commit
tran

--
select
*
from
products
--
--
事务保存点
--
save tran
--
select
*
from
products
--
begin
tran
insert
Products (ProductName,CategoryID)
values
(N
'
西瓜汁1
'
,
1
)
if
@@error
>
0
or
@@rowcount
<>
1
goto
rb
select
*
from
products
save
tran
savebyP
insert
Products (ProductName,CategoryID)
values
(N
'
草莓汁2
'
,
1
)
if
@@error
>
0
or
@@rowcount
<>
1
goto
rb
select
*
from
products
save
tran
savebyP1
--
开始嵌套
begin
tran
update
products
set
ProductName
=
'
OK!
'
where
ProductID
=
115151
if
@@error
>
0
or
@@rowcount
<>
1
rollback
tran
savebyP
else
commit
tran
savebyP
--
嵌套结束
--
select
*
from
products
--
insert
Products (ProductName,CategoryID)
values
(N
'
冰镇可乐1
'
,
1
)
if
@@error
>
0
or
@@rowcount
<>
1
rollback
tran
else
commit
tran

--
select
*
from
products
if
@@error
>
0
begin
rb:
rollback
tran
end
else
commit
tran

--
select
*
from
products


/**/
/*
当 SET XACT_ABORT 为 ON 时,如果执行 Transact-SQL 语句产生运行时错误,则整个事务将终止并回滚。 
当 SET XACT_ABORT 为 OFF 时,有时只回滚产生错误的 Transact-SQL 语句,而事务将继续进行处理。如果错误很严重,
那么即使 SET XACT_ABORT 为 OFF,也可能回滚整个事务。
编译错误(如语法错误)不受 SET XACT_ABORT 的影响
*/
--
select
*
from
products
set
xact_abort
on
--
begin
tran
a
insert
Products (ProductName,CategoryID)
values
(
'
ok1
'
,
1
)
insert
Products (ProductName,CategoryID)
values
(
'
SQL1
'
,
1
)
select
*
from
products
insert
Products (ProductName,CategoryID)
values
(
'
SQL3
'
,
111
)
select
*
from
products
commit
tran
a
select
*
from
products
--
===================================================================================================
--
Trigger
--
===================================================================================================
--
insert trigger
create
trigger
CategoriesbyInsert
on
Categories
after
insert
as
begin
print
N
'
类别表里新增了一条记录!
'
end
go

--
create
trigger
CategoriesbyInsert_1
on
Categories
after
insert
as
begin
print
N
'
测试顺序!
'
end
go
--
insert
into
Categories
(CategoryName)
values
(N
'
测试触发器4
'
)
--
--
sp_settriggerorder
exec
sp_settriggerorder
'
CategoriesbyInsert_1
'
,
'
first
'
,
'
insert
'
exec
sp_settriggerorder
'
CategoriesbyInsert
'
,
'
last
'
,
'
insert
'

--
create
trigger
Categoriesbyupdate
on
Categories
after
update
as
begin
print
N
'
类别表里更新了一条记录!
'
end
go
--
select
*
from
Categories
update
Categories
set
CategoryName
=
N
'
测试触发器2
'
where
CategoryID
=
17
--
create
trigger
Categoriesbydelete
on
Categories
after
delete
as
begin
print
N
'
类别表里新删除了一条记录!
'
end
go



--
select
*
from
[
Order Details
]
select
*
from
orders
--
create
trigger
odbyinsert
on
[
Order Details
]
after
insert
as
begin
if
(
select
discount
from
inserted)
>
0.7
begin
print
N
'
折扣不能大于70%
'
rollback
tran
end
end
go

insert
[
Order Details
]
values
(
10252
,
1
,$
20
,
50
,
0.8
)



--
触发器的嵌套
--
create
table
testlog
(
id
int
identity
(
1
,
1
)
not
null
,
tablename
nvarchar
(
50
)
not
null
,
opsentence
nvarchar
(
2000
)
not
null
,
opcontent
nvarchar
(
2000
)
not
null
,
optime
datetime
not
null
constraint
df_testlog_optime
default
(
getdate
()),
constraint
pk_testlog
primary
key
clustered
(
id
asc
)
with
(ignore_dup_key
=
off
)
on
[
primary
]
)
on
[
primary
]
go
--
create
trigger
testloginsert
on
testlog
after
insert
as
begin
print
'
测试时,表记录有所变动
'
end
go
--
create
trigger
bookinsert3
on
books
after
insert
as
begin
declare
@name
nvarchar
(
20
),
@page
nvarchar
(
max
)
set
@name
=
(
select
title
from
inserted)
set
@page
=
(
select
pages
from
inserted)
insert
into
testlog(tablename,opsentence,opcontent)
values
(
'
books表
'
,
'
插入记录
'
,
'
书名 :
'
+
@name
+
'
页数:
'
+
@page
)
end
go
--
测试
insert
books
(book_id,title,pages)
values
(
8
,
'
HTML
'
,
200
)
select
*
from
testlog


--
instead of 触发器
create
trigger
od_insert
on
[
Order Details
]
instead
of
insert
as
begin
declare
@oid
int
,
@pid
int
,
@price
money
,
@q
smallint
,
@d
real

set
@oid
=
(
select
orderid
from
inserted)
set
@pid
=
(
select
productid
from
inserted)
set
@price
=
(
select
UnitPrice
from
inserted)
set
@q
=
(
select
Quantity
from
inserted)
set
@d
=
(
select
Discount
from
inserted)
if
(
@d
)
>
0.6
print
N
'
折扣不能大于0.6
'
else
insert
into
[
Order Details
]
values
(
@oid
,
@pid
,
@price
,
@q
,
@d
)
end
go

--
drop
trigger
od_insert
insert
into
[
Order Details
]
values
(
10252
,
1
,$
20
,
50
,
0.8
)
select
*
from
[
Order Details
]
where
Discount
>
0.5

exec
sp_helptext od_insert
--
禁用一个触发器
--
alter
table
[
Order Details
]
disable
trigger
od_insert
--
启用
alter
table
[
Order Details
]
enable
trigger
od_insert

--
create
trigger
test_table
on
database
for
drop_table ,alter_table
as
begin
print
N
'
对不起,你不能对数据表进行操作
'
rollback
;
end

drop
table
[
Order Details
]
本文深入探讨SQL的高级用法,包括高效插入数据的INSERT策略、书籍管理的查询技巧、安全删除数据的DELETE操作以及灵活管理表结构的方法。通过实例解析,助你提升SQL技能。

被折叠的 条评论
为什么被折叠?



