事务处理

    
一、概念

1、含义：数据库中的事务是一种机制，每个事务时一个独立的工作单元，包含一组数据库操作命令。是一个不可分割的工作逻辑单元

2、属性

  原子性：各个元素不可分割
  一致性：数据保持一致的状态
  隔离性：各个事务之间相互隔离，互不干扰
  持久性：其操作结果对系统的影响是永久的

3、分类：

显示事务：
  开始：begin    transaction
  提交：commit   transaction
  回滚：rollback transaction

隐式事务
  开始：set implicit_transaction on(开启)/off(关闭)

自动提交事务 ：每条单独的语句都是一个事务。

二、事务的使用

1、创建

2、创建原则

  尽可能简短
  事务中访问的数据量尽量最少
  浏览数据时尽量不要打开事务
  事务处理期间尽量不要请求用户输入

3、使用事务的注意事项
 
  每个操作之后都要检查@@error 或@rowcount的值
  当事务结束后，紧跟的T_sql语句会被执行 但出错后就不能回滚事务
  提交完毕的事务将被写入数据库中
  事务执行过程中若发生非人为因素（如 断电）导致事务执行失败，下次重启系统时 事务会自动回滚
  无法回滚的语句不能写在事务中

三、事务的隔离级别

1、read uncommitted：不隔离数据（该事务正在使用数据同时，其他事务也可以使用） 隔离级别最低

2、read committed： 不允许读取没有提交的数据  Sql server默认级别

3、repeatable read：事务中锁定所读取的数据不让别人修改、删除 可保持数据一致性

4、snapshot：快照隔离

5、serializable：将事务所用到的数据表全部锁定 部允许凄然事务增、删、改数据，并发性最低 安全性最高

6、更改事务隔离级别的语法：
  set transaction isolation level read uncommitted/read committed/repeatable read
/snapshot/serializable  --隔离级别
   --T_sql语句

 

 

四、相关习题

1、use master
if DB_ID('studentDB') is not null
 drop database studentDB
 go
create database studentDB
go
use studentDB
go

if OBJECT_ID('classInfo','table') is not null
 drop table classInfo
 go
create table classInfo
(
 classId int primary key identity,
 className varchar(30) unique
)
--插入测试数据
insert into classInfo values('高三八班')
insert into classInfo values('高三二班')
insert into classInfo values('高三三班')
insert into classInfo values('高三四班')
insert into classInfo values('高三五班')
insert into classInfo values('高三六班')
insert into classInfo values('高三七班')

select * from classInfo

if OBJECT_ID('student','table') is not null
 drop table student
 go
create table student
(
 stuId int primary key identity,
 stuName varchar(30),
 stuAge int,
 stuPhone varchar(20) check(stuPhone like  '139%' or stuPhone like '133%'),
 classId int references classInfo(classId),
 stuState int check(stuState=0 or stuState=1 or stuState=2)  --0为在读 1为休学 2为转学
)
insert into student values('张三',18,'13997805814',1,0)
insert into student values('张三',17,'13397805814',1,0)
insert into student values('张三',19,'13977805814',5,0)
insert into student values('张死',20,'13987805814',1,0)
insert into student values('李四',21,'13997805914',2,0)
insert into student values('王五',28,'13997805234',3,0)
insert into student values('周六',38,'13997805845',2,0)
insert into student values('abc',15,'13378923147',1,0)

select * from student

go
--创建存储过程，传入两个个学生ID1，ID2，把学生的电话号码分别改为abc和13945611234
--   如果修改成功，给出修改成功提示！
--   如果修改失败，撤销回修改前的状态，并给出修改失败错误提示！
--   提示：考虑使用事务！
 if OBJECT_ID('proc_update','procedure') is not null
 drop procedure proc_update
 go
create procedure proc_update
 @stuId1 int,
 @stuId2 int
as
 declare @myerror int =0, @mycount int =0
begin

   begin transaction
   
   update student set stuPhone='abc' where stuId=@stuId1
   select @myerror+=@@ERROR ,@mycount+=@@ROWCOUNT
   update student set stuPhone='13945611234' where stuId=@stuId2
   select @myerror+=@@ERROR ,@mycount+=@@ROWCOUNT
  
   if(@mycount<2 or @myerror!=0)
  begin
   print '修改失败'
   rollback transaction
  end
   else
  begin
   print '修改成功'
    commit transaction
  end
  select * from student where stuId=@stuId1
  select * from student where stuId=@stuId2
end;
go
execute proc_update 1,2
go

2、use master
go
--创建数据库
if DB_ID('bankDB') is not null
 drop database bankDB
go
create database bankDB
on
(
 name='bankDB',
 filename='e:\bankDB.mdf',
 filegrowth=15%
)
log on
(
 name='bankDB_log',
 filename='e:\bankDB_log.ldf'
)
go

--使用数据库
use bankDB
go

--创建表
if exists(select * from sysobjects where name='userInfo')
 drop table userInfo
go
create table userInfo
(
 customerID int identity(1,1) not null,
 customerName varchar(20) not null,
 PID varchar(20) not null,
 telephone varchar(20) not null,
 address varchar(50)
)
go

if exists(select * from sysobjects where name='cardInfo')
 drop table cardInfo
go
create table cardInfo
(
 cardID varchar(20) not null,
 curType varchar(20) not null,
 savingType varchar(20) not null,
 openDate datetime not null,
 openMoney money not null,
 balance money not null,
 pass varchar(6) not null,
 IsReportLoss bit not null,
 customerID int not null
)
go

if exists(select * from sysobjects where name='transInfo')
 drop table transInfo
go
create table transInfo
(
 transDate datetime not null,
 cardID varchar(20) not null,
 transType varchar(10) not null,
 transMoney money not null,
 remark varchar(100)
)
go

--添加约束
alter table userInfo
 add constraint ck_PID check(len(PID)=18 or len(PID)=15)
alter table userInfo
 add constraint uq_PID unique(PID)
alter table userInfo
 add constraint ck_telephone check(len(telephone)=11 or
 telephone like '[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' or
 telephone like '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
alter table cardInfo
 add constraint ck_cardID check(cardID like '1010 3576 [0-9][0-9][0-9][0-9] [0-9][0-9][0-9][0-9]')
alter table cardInfo
 add constraint df_curTpye default('RMB') for curType
alter table cardInfo
 add constraint ck_savingType check(savingType in('活期','定活两便','定期'))
alter table cardInfo
 add constraint df_openDate default(getdate()) for openDate
alter table cardInfo
 add constraint ck_openMoney check(openMoney>=1)
alter table cardInfo
 add constraint ck_balance check(balance>=1)
alter table cardInfo
 add constraint ck_pass check(len(pass)=6)
alter table cardInfo
 add constraint df_pass default('888888') for pass
alter table cardInfo
 add constraint ck_IsReportLoss check(IsReportLoss='0' or IsReportLoss='1')
alter table cardInfo
 add constraint df_IsReportLoss default('0') for IsReportLoss
alter table transInfo
 add constraint df_transDate default(getdate()) for transDate
alter table transInfo
 add constraint ck_transType check(transType='存入' or transType='支取')
alter table transInfo
 add constraint ck_transMoney check(transMoney>0)

--添加关系
alter table userInfo
 add constraint pk_UcustomerID primary key(customerID)
alter table cardInfo
 add constraint pk_CcardID primary key(cardID)
alter table cardInfo
 add constraint fk_CcustomerID foreign key(customerID) references userInfo(customerID)
alter table transInfo
 add constraint fk_TcardID foreign key(cardID) references cardInfo(cardID)

--插入测试数据
insert into userInfo values('张三','123456789012345','010-67898978','北京海淀')
insert into cardInfo values('1010 3576 1234 5678',default,'活期',default,1000,1000,default,default,1)
insert into userInfo(customerName,PID,telephone) values('李四','321245678912345678','0478-44443333')
insert into cardInfo values('1010 3576 1212 1134',default,'定期',default,1,1,default,default,2)
insert into transInfo(transDate,cardID,transType,transMoney) values(default,'1010 3576 1234 5678','支取',900)
update cardInfo set balance=balance-900 where cardID='1010 3576 1234 5678'
insert into transInfo(transDate,cardID,transType,transMoney) values(default,'1010 3576 1212 1134','存入',5000)
update cardInfo set balance=balance+5000 where cardID='1010 3576 1212 1134'

select * from userInfo
select * from transInfo
select * from cardInfo

--（1）写存储过程完成存款的业务，使用事务
if OBJECT_ID('proc_get','procedure') is not null
 drop procedure proc_get
 go
create procedure proc_get
 @cardId varchar(20),
 @money money
as
 declare @mycount int =0,@myerror int =0
begin
 if not exists(select * from cardInfo where cardID=@cardId)
  begin
   print '该用户不存在 请检查卡号是否输入正确'
   return
  end
 begin transaction
  update cardInfo set balance=balance+@money where cardId=@cardId
  select @mycount+=@@ROWCOUNT,@myerror+=@@ERROR
  insert into transInfo values(GETDATE(),@cardId,'存入',@money,null)
  select @mycount+=@@ROWCOUNT,@myerror+=@@ERROR
 if(@mycount<2 or @myerror!=0)
  begin
   print '存款失败'
   rollback transaction
  end
 else
  begin
   commit transaction
   select * from cardInfo where cardId=@cardId
  end
end;
go
execute proc_get '1010 3576 1212 1134',10000
select * from userInfo
select * from transInfo
select * from cardInfo

--（2）写存储过程完成取款的业务，使用事务
if OBJECT_ID('proc_save','procedure') is not null
 drop procedure proc_save
 go
create procedure proc_save
 @customerId int,
 @money money
as
 declare @mycount int =0,@myerror int =0
begin
 begin transaction
  update cardInfo set balance=balance-@money where customerID=@customerId
 select @mycount+=@@ROWCOUNT,@myerror+=@@ERROR
  insert into transInfo values(GETDATE(),(select cardID from cardInfo where customerID=@customerId),'支取',@money,null)
 select @mycount+=@@ROWCOUNT,@myerror+=@@ERROR
 if(@mycount<2 or @myerror!=0)
  begin
   print '支取失败'
   rollback transaction
  end
 else
  begin
   commit transaction
   select * from cardInfo where customerID=@customerId
  end
end;
go
execute proc_save 1,10000
go

--（3）写存储过程完成转账的业务，使用事务
--
if OBJECT_ID('proc_give','procedure') is not null
 drop procedure proc_give
 go
create procedure proc_give
 @customerIdG int,
 @customerIdR int,
 @money money
as
declare @mycount int =0,@myerror int =0
begin
 if(((select balance from cardInfo where customerID=@customerIdG)-@money)!>0)
  begin
   print '该用户不可以进行转账业务  卡内余额不足'
   return
  end
 begin transaction
  update cardInfo set balance=balance-@money where customerID=@customerIdG
  select @mycount+=@@ROWCOUNT,@myerror+=@@ERROR
  update cardInfo set balance=balance+@money where customerID=@customerIdR
  select @mycount+=@@ROWCOUNT,@myerror+=@@ERROR
  insert into transInfo values(GETDATE(),(select cardID from cardInfo where customerID=@customerIdG),'支取',@money,null)
  select @mycount+=@@ROWCOUNT,@myerror+=@@ERROR
  insert into transInfo values(GETDATE(),(select cardID from cardInfo where customerID=@customerIdR),'存入',@money,null)
  select @mycount+=@@ROWCOUNT,@myerror+=@@ERROR
  if(@mycount<4 or @myerror!=0)
   begin
    print '转账失败'
    rollback transaction
   end
  else
   begin
    commit transaction
    select * from cardInfo
    select * from transInfo
   end
end;
go
execute proc_give 2,1,15000
go
select * from cardInfo
select * from transInfo
select* from userInfo
print'服务器为:'+@@servicename