--11-1
USE BookShopDb   
GO
--一个客户在销售员编号为的用户处购买了三种书，生成销售记录---
SET NOCOUNT ON   --不显示受影响的行数信息
GO
BEGIN TRANSACTION  --开始事务
DECLARE @err  INT --保存错误编号
PRINT '客户开始买书,事务开始'
SET @err=0
--插入销售主表SalesMaster
INSERT INTO SalesMaster VALUES(GETDATE(),'001')
--声明局部变量保存SalesMasterID
SET @err=@err+@@ERROR
DECLARE @SalesMasterID INT 
--获取最新的SalesMasterID
SELECT @SalesMasterID=@@IDENTITY 
--购买三本书
INSERT INTO SalesDetails
VALUES(@SalesMasterID,'B0001',1,0.9,45)
SET @err=@err+@@ERROR
INSERT INTO SalesDetails
VALUES(@SalesMasterID,'B0003',1,1,118)
SET @err=@err+@@ERROR
INSERT INTO SalesDetails
VALUES(@SalesMasterID,'B0004',2,0.8,28)
SET @err=@err+@@ERROR
--购买完成,根据错误编号决定是否执行成功
IF @err=0
BEGIN 
        PRINT '购买成功，事务结束'
        COMMIT TRANSACTION
END 
ELSE
   BEGIN
        ROLLBACK TRANSACTION
        PRINT '购买失败，事务回滚'      
   END

---11-2
--1.创建表test--
CREATE TABLE test(id INT NOT NULL PRIMARY KEY,info VARCHAR(20) NOT NULL)
GO
--2循环插入数据条测试数据1100000
DECLARE @num INT
SET @num=1
WHILE(@num<=1100000)
BEGIN
   INSERT INTO test VALUES(@num,'test')
   SET @num=@num+1
END
GO
--3. 查询插入后的记录数
SELECT COUNT(*) FROM test
--4.通过删除操作来测试一个delete语句就是一个单独的事务
DELETE FROM test   --因为数据量较大可能需要比较长的时间才能删除成功
--5.点击工具条上的取消按钮取消删除操作如图1

---11-3
--第一个事务(第一个查询窗口)
USE BookShopDb
GO
SET TRANSACTION ISOLATION LEVEL  READ UNCOMMITTED;
GO
BEGIN TRANSACTION
GO
SELECT  * FROM publisher WHERE PublisherID=1
GO
UPDATE Publisher SET PublisherName=PublisherName+'2'
WHERE PublisherID=1
GO
SELECT  * FROM publisher WHERE PublisherID=1
GO
WAITFOR DELAY '00:00:15'
GO
ROLLBACK TRANSACTION;
GO
SELECT  * FROM publisher WHERE PublisherID=1
GO

--11-3 第二个事务（第二个查询窗口）
--第二个事务
USE BookShopDb
GO
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO
BEGIN TRANSACTION
GO
SELECT  * FROM publisher WHERE PublisherID=1
GO
COMMIT;
GO
--11-4(第一个事务，第一个查询窗口）
--第一个事务
USE BookShopDb
GO
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
GO
BEGIN TRANSACTION
GO
SELECT  * FROM publisher WHERE PublisherID=1
GO
WAITFOR DELAY '00:00:15'
GO
SELECT  * FROM publisher WHERE PublisherID=1
GO
COMMIT TRANSACTION;
GO
--11-4第二个事务
---第二个事务
USE BookShopDb
GO
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
GO
BEGIN TRANSACTION
GO
SELECT  * FROM publisher WHERE PublisherID=1
GO
UPDATE Publisher SET PublisherName=PublisherName+'2'
WHERE PublisherID=1
GO
SELECT  * FROM publisher WHERE PublisherID=1
GO
COMMIT TRANSACTION;
SELECT  * FROM publisher WHERE PublisherID=1
GO
--11-5 
--第一个事务（第一个查询窗口）
USE BookShopDb
GO
BEGIN TRANSACTION
UPDATE Publisher SET PublisherName=PublisherName+'publisher'
WHERE PublisherID=1
WAITFOR DELAY '00:00:20'
SELECT * FROM category
ROLLBACK TRANSACTION

--11-5 第二个查询窗口
USE BookShopDb
GO
BEGIN TRANSACTION
UPDATE Category SET CategoryName=CategoryName+'category'
WHERE CategoryID=1
WAITFOR DELAY '00:00:20'
SELECT * FROM Publisher 
ROLLBACK TRANSACTION
