《上海交通大学高级数据库课件陆朝俊tranexperiment教程》由会员分享，可在线阅读，更多相关《上海交通大学高级数据库课件陆朝俊tranexperiment教程（11页珍藏版）》请在金锄头文库上搜索。
1、MS SQL Server Transaction,自动提交事务:一条语句自成一事务 create table test (a int primary key, b varchar(20); insert into test values(10,apple); insert into test values(20,banana); insert into test values(30,pear); insert into test values(40,watermelon); go 四个事务,MS SQL Server Transaction,显式事务:显式定义开始和结束 begin tran
2、saction update test set a = a + 1; update test set a = a - 1; commit C/S连接不可靠且要做数据量大的更新时使用,MS SQL Server Transaction,隐式事务:隐式开始,显式结束 首先设置会话为隐式事务模式 set implicit_transaction on alter table, create, delete, drop, fetch, grant, insert, open, revoke, select, truncate, update自动开始事务 要适时结束事务 set implicit_tra
3、nsaction on update test set a = a + 1 begin tran -不起作用 update test set a = a - 1; commit,隔离级别实验(1),连接1: begin tran update test set a=a+10 go /未提交！ 连接2: set transaction isolation level read uncommitted begin tran select * from test go /看到更新了！,隔离级别实验(2),连接2: set transaction isolation level read commit
4、ted begin tran select * from test go 等待 连接1: commit 则连接2不再等待,隔离级别实验(3),连接2: select * from test where a 30 go 连接1: update test set a = a + 10 where a 30 go 连接2: select * from test where a 30 go /可重复读？,隔离级别实验(4),连接2: Set transaction isolation level repeatable read begin tran select * from test where a
5、 30 go 连接1: update test set a = a + 30 where a 30 等待 连接2: select * from test where a 30 go /可重复读？,隔离级别实验(5),关闭并重启连接1: update test set a = a + 30 where a = 30 不用等待,因为不在连接2的查询范围 insert into test values(10,orange) go 同样不用等待 连接2: select * from test where a 30,锁,SQL Server可以锁的对象 RID:记录 Key:已设置为索引的字段 Page
6、:数据页或索引页 Extent:8个连续页 Table:整个表,包括其上索引 DB:整个数据库,死锁实验(1),Use northwind Go Create table t1(a int) Create table t2(a int) Insert into t1 values(10) Insert into t2 values(20) Go,死锁实验(2),会话1: begin tran Update t1 set a = 50 会话2: Begin tran Update t2 set a = 60 会话1: Update t2 set a = 70 会话2: Update t2 set a = 80,
