synbase 中的十五

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