34,593
社区成员
发帖
与我相关
我的任务
分享
--出至微软亚太
第三,事务处理和锁定语句。表变量不能参与事务处理和锁定,以下示例说明了这一点
-- create a source table
create table
tv_source(c1 int, c2 char(100))
go
declare @i int
select @i = 0
while (@i < 100)
begin
insert into tv_source values (@i, replicate ('a', 100))
select @i = @i + 1
end
-- using #table
create table #tv_target (c11 int, c22 char(100))
go
BEGIN TRAN
INSERT INTO #tv_target (c11, c22)
SELECT c1, c2
FROM
tv_source
--
using table variable
DECLARE @tv_target TABLE (c11 int, c22 char(100))
BEGIN TRAN
INSERT INTO @tv_target (c11, c22)
SELECT c1, c2
FROM tv_source
-- Now if I look at the locks, you will see that
only
-- #table takes locks. Here is the query that
used
-- to check the locks
select
t1.request_session_id as spid,
t1.resource_type as type,
t1.resource_database_id as dbid,
(case
resource_type
WHEN 'OBJECT' then object_name(t1.resource_associated_entity_id)
WHEN 'DATABASE' then ' '
ELSE (select object_name(object_id)
from sys.partitions
where hobt_id=resource_associated_entity_id)
END) as objname,
t1.resource_description as description,
t1.request_mode as mode,
t1.request_status as status,
t2.blocking_session_id
from sys.dm_tran_locks as t1 left outer join sys.dm_os_waiting_tasks as t2
ON t1.lock_owner_address = t2.resource_address
另一个有趣的现象是,如果回滚的事务里涉及表变量,表变量的数据不会被回滚。
Rollback
-- this
query will return 100 for table variable but 0 for #table.
SELECT COUNT(*) FROM @tv_target
• 第四,表变量上的操作不被日志文件记录。请看下面这个例子:
--
create a table variable, insert bunch of rows and update
DECLARE @tv_target TABLE (c11 int, c22 char(100))
INSERT INTO @tv_target (c11, c22)
SELECT c1, c2
FROM tv_source
-- update all the rows
update @tv_target set c22 = replicate ('b', 100)
-- look at the top 10 log records. I get no
records for this case
select top 10 operation,context, [log record fixed length], [log record length],
AllocUnitId, AllocUnitName
from fn_dblog(null, null)
where AllocUnitName like '%tv_target%'
order by [Log Record Length] Desc
-- create a local temptable
drop table #tv_target
go
create table #tv_target (c11 int, c22 char(100))
go
INSERT INTO #tv_target (c11, c22)
SELECT c1, c2
FROM tv_source
--
update all the rows
update #tv_target set c22 = replicate ('b', 100)
-- look
at the log records. Here I get 100 log records for update
select
operation,context, [log
record fixed length], [log record length], AllocUnitName
from fn_dblog(null, null)
where AllocUnitName like '%tv_target%'
order by [Log
Record Length] Desc