if exists (select 1
from sysobjects
where id = object_id('tA2')
and type = 'U')
drop table tA2
go
begin transaction tA2
LOCK TABLE tA2 IN SHARE MODE
commit transaction tA2
if exists (select 1
from sysobjects
where id = object_id('tB1')
and type = 'U')
drop table tB1
go
/*==============================================================*/
/* Table: tA */
/*==============================================================*/
create table tA2 (
name char(20) null,
price int default 0 null,
)
go
/*==============================================================*/
/* Table: tB */
/*==============================================================*/
create table tB2 (
name char(20) null,
saleprice int default 0 null,
)
go
ALTER TABLE tA1 LOCK DATAROWS
ALTER TABLE tB1 LOCK DATAROWS
go
insert into tA2(name, price) values ('book',20)
insert into tA1(name, price) values ('book1',20)
insert into tB1(name, saleprice) values ('book',10)
insert into tB1(name, saleprice) values ('book1',10)
select * from tA2
select * from tB1
begin tran
insert into tA1(name, price) values ('book2',20)
waitfor delay '00:00:05'
insert into tB1(name, saleprice) values ('book2',10)
commit tran
begin tran
update tB set saleprice=saleprice+10000 where name='book'
update tA set price=price+20000 where name='book'
commit tran
-- test
begin tran
update tA2 set price=price+100 where name='book'
waitfor delay '00:00:05'
update tB2 set saleprice=saleprice+100 where name='book45'
commit tran
begin tran
insert into tB1(name, saleprice) values ('book3',10)
waitfor delay '00:00:05'
insert into tA1(name, price) values ('book3',20)
commit tran
drop procedure P1
select * from tA2
insert into tA2(name, price) values ('book44',20)
drop procedure P1
select * from tA2
select * from tB2
exec P1
create procedure P1
as
begin
begin transaction V1
insert into tA2(name, price) values ('book45',20)
exec P2
rollback transaction V1
end
drop procedure P2
create procedure P2
as
begin
begin transaction V2
insert into tB2(name, saleprice) values ('book45',20)
commit transaction V2
end
数据库版本:sybase_win_12.5.2
sql执行程序:sqladv
1、先建2个测试表tA,tB;插入测试数据
from sysobjects
where id = object_id('tA')
and type = 'U')
drop table tA
go
if exists (select 1
from sysobjects
where id = object_id('tB')
and type = 'U')
drop table tB
go
/*==============================================================*/
/* Table: tA */
/*==============================================================*/
create table tA (
name char(20) null,
price int default 0 null,
)
go
/*==============================================================*/
/* Table: tB */
/*==============================================================*/
create table tB (
name char(20) null,
saleprice int default 0 null,
)
go
ALTER TABLE tB LOCK DATAROWS
go
insert into tB(name, saleprice) values ('book',10)
go
select * from tA
select * from tB
---- -----------
book 20
---- -----------
book 10
事务和别的sql发生并发的情况1:
测试代码:
事务代码tran1:
update tA set price=price+100 where name='book'
waitfor delay '00:00:05'
update tB set saleprice=saleprice+100 where name='book'
commit tran
update tA set price=price+20000 where name='book'
执行tran1,然后马上执行并发代码code1
tran1执行完成后code1也执行完成了。
tran1:
(1 row affected)
(1 row affected)
(1 row affected)
name price
---- -----------
book 20120
---- -----------
book 110
测试用例2:
事务和别的sql发生并发的情况2:
测试代码:
事务代码tran1:
update tA set price=price+100 where name='book'
waitfor delay '00:00:05'
update tB set saleprice=saleprice+100 where name='book'
commit tran
update tB set saleprice=saleprice+10000 where name='book'
执行tran1,然后马上执行并发代码code2
code2马上能成功,tran1也能按时执行成功。
tran1:
(1 row affected)
(1 row affected)
(1 row affected)
name price
---- -----------
book 120
---- -----------
book 10110
这就要注意一个问题,在事务中,前面查询的数据,在后面该值可能已经改变了。
事务和事务发生并发的情况1:
测试代码:
事务代码tran1:
update tA set price=price+100 where name='book'
waitfor delay '00:00:05'
update tB set saleprice=saleprice+100 where name='book'
commit tran
update tB set saleprice=saleprice+10000 where name='book'
update tA set price=price+20000 where name='book'
commit tran
执行tran1,然后马上执行tran2
tran1执行完成后tran2也执行完成了,但tran2出错了。
tran1:
(1 row affected)
(1 row affected)
(1 row affected)
Server Message: Number 1205, Severity 13
Server 'ONLINEDB', Line 5:
Your server command (family id #0, process id #17) encountered a deadlock situation. Please re-run your command.
name price
---- -----------
book 120
---- -----------
book 110
事务和事务发生并发的情况1:
测试代码:
事务代码tran1:
update tA set price=price+100 where name='book'
waitfor delay '00:00:05'
update tB set saleprice=saleprice+100 where name='book'
commit tran
update tA set price=price+20000 where name='book'
update tB set saleprice=saleprice+10000 where name='book'
commit tran
执行tran1,然后马上执行tran3
tran1执行完成后tran3也执行完成了。
tran1:
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
name price
---- -----------
book 20120
---- -----------
book 10110
事务和事务发生并发的情况2:
测试代码:
事务代码tran1:
update tA set price=price+100 where name='book'
waitfor delay '00:00:05'
update tB set saleprice=saleprice+100 where name='book'
commit tran
update tB set saleprice=saleprice+10000 where name='book'
update tA set price=price+20000 where name='book'
commit tran
执行tran1,然后马上执行tran4
tran1执行完成后tran4也执行完成了,但tran1完成时间是12秒。
tran1:
(1 row affected)
(1 row affected)
(1 row affected)
Server Message: Number 1205, Severity 13
Server 'ONLINEDB', Line 3:
Your server command (family id #0, process id #17) encountered a deadlock situation. Please re-run your command.
name price
---- -----------
book 120
---- -----------
book 110
事务和事务发生并发的情况3:
测试代码:
事务代码tran1:
update tA set price=price+100 where name='book'
waitfor delay '00:00:05'
update tB set saleprice=saleprice+100 where name='book'
commit tran
update tB set saleprice=saleprice+10000 where name='book'
waitfor delay '00:00:10'
update tA set price=price+20000 where name='book'
commit tran
测试步骤:
执行tran1,然后马上执行tran5
tran1执行完成后tran5也执行完成了,但tran1完成时间是12秒。
tran1:
(1 row affected)
Server Message: Number 1205, Severity 13
Server 'ONLINEDB', Line 5:
Your server command (family id #0, process id #15) encountered a deadlock situation. Please re-run your command.
(1 row affected)
(1 row affected)
name price
---- -----------
book 20020
---- -----------
book 10010
事务和事务发生并发的情况4:
测试代码:
事务代码tran1:
update tA set price=price+100 where name='book'
waitfor delay '00:00:05'
update tB set saleprice=saleprice+100 where name='book'
commit tran
update tA set price=price+20000 where name='book'
waitfor delay '00:00:10'
update tB set saleprice=saleprice+10000 where name='book'
commit tran
测试步骤:
执行tran1,然后马上执行tran6
tran1执行5秒后完成,tran6执行15秒才完成。
tran1:
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
name price
---- -----------
book 20120
---- -----------
book 10110