数据库实验报告五--事务管理

备份数据库：
BACKUP DATABASE stu TO DISK=N'D:\Downloads\stu.bak'
BACKUP DATABASE stu TO DISK=N'D:\Downloads\studiff.bak' WITH DIFFERENTIAL
BACKUP LOG stu TO DISK=N'D:\Downloads\stulog.bak'
恢复数据库：
RESTORE DATABASE stu FROM DISK=N'D:\Downloads\stu.bak'
WITH REPLACE, NORECOVERY
RESTORE DATABASE st FROM DISK=N'D:\Downloads\studiff.bak'
WITH REPLACE, NORECOVERY
RESTORE DATABASE st FROM DISK=N'D:\Downloads\stulog.bak'
WITH REPLACE, NORECOVERY
​
定义事务：
事务定义：
insert into student(sno,sname) values('100','zs')
insert into student(sno,sname) values('120',15,'zs')
INSERT 语句中列的数目小于 VALUES 子句中指定的值的数目。VALUES 子句中值的数目必须与 INSERT 语句中指定的列的数目匹配。
begin transaction
insert into student(sno,sname) values('108','zs')
insert into student(sno,sname) values('120',15,'zs')
commit
INSERT 语句中列的数目小于 VALUES 子句中指定的值的数目。VALUES 子句中值的数目必须与 INSERT 语句中指定的列的数目匹配。
事务故障恢复
begin transaction t1
select * from course
insert into course(cno,cname) values ('11','linux')
select * from course
save transaction t1
update course set cname='linux OS' where cno='11'
（linux变为linux OS）
select * from course
rollback transaction t1
（linux OS变为linux）
select * from course
rollback
(表消失)
并发控制：
begin transaction t1
select * from course where cno='1001'
begin transaction t2
select * from course where cno='1001'
（一直在执行查询）
update course set cname='abc' where cno='1001'
select * from course where cno='1001
update course set cname='mon' where cno='1001'
select * from course where cno='1001'
（不可提交，还在执行查询）
select * from course where cno='1001'

多粒度锁
启动事务T1
begin transaction
select sno,sage from student with(updlock) where sno='141530101'
正常执行
启动事务T2
begin transaction
select sno,sage from student with(updlock) where sno='141530101'
等待状态
启动事务T3
begin transaction
select sno,sage from student with(updlock) where sno='141530102'
正常执行
启动事务T4
begin transaction
select sno,sage from student with(tablock) where sno='141530102'
等待状态
修改事务T4并启动
begin transaction
select sno,sage from student with(tablock holdlock)
where sno='141530102'
正常执行
启动事务T5
begin transaction
select sno,sage from student with(nolock) where sno='141530101'
正常执行
启动事务T6
begin transaction
select sno,sage from student with(tablock holdlock)
where sno='141530101’
正常执行，年龄值为22
在T6接着执行
update student set sage=100 where sno = '141530101'
select sno,sage from student where sno='141530101'
正常执行，年龄值为100
在T6接着执行
rollback
select sno,sage from student where sno='141530101'
正常执行，年龄值为22，此时T5读取的即为脏数据

事务的隔离级别
启动事务T1
set transaction isolation level read committed
begin transaction
select sno,sage from stdent
正常执行，read committed是默认隔离级别
启动事务T2
begin transaction
salect sno,sage from student
正常执行
update student set sage +=10 where sno='141530101'
select sno,sage from student
正常执行年龄值为32
在事务T2中重新执行
salect sno,sage from student
在T2接着执行
select sno.sage from stdent
update student set sage +=10 where sno='141530101'
commit
select sno,sage from stdent
正常执行年龄值为42，表示没有丢失T1事物的更新