Chapter 09 - Transactions

最新推荐文章于 2024-09-04 18:18:28 发布
最新推荐文章于 2024-09-04 18:18:28 发布
阅读量308 收藏
点赞数
文章标签： transactions session database insert microsoft conflict
 

---------------------------------------------------------------------
-- Microsoft SQL Server 2008 T-SQL Fundamentals
-- Chapter 9 - Transactions
-- ?2008 Itzik Ben-Gan
---------------------------------------------------------------------

---------------------------------------------------------------------
-- Transactions
---------------------------------------------------------------------

-- Transaction Example
USE TSQLFundamentals2008;

-- Start a new transaction
BEGIN TRAN;

  -- Declare a variable
  DECLARE @neworderid AS INT;

  -- Insert a new order into the Sales.Orders table
  INSERT INTO Sales.Orders
      (custid, empid, orderdate, requireddate, shippeddate,
       shipperid, freight, shipname, shipaddress, shipcity,
       shippostalcode, shipcountry)
    VALUES
      (85, 5, '20090212', '20090301', '20090216',
       3, 32.38, N'Ship to 85-B', N'6789 rue de l''Abbaye', N'Reims',
       N'10345', N'France');

  -- Save the new order ID in a variable
  SET @neworderid = SCOPE_IDENTITY();

  -- Return the new order ID
  SELECT @neworderid AS neworderid;

  -- Insert order lines for new order into Sales.OrderDetails
  INSERT INTO Sales.OrderDetails
      (orderid, productid, unitprice, qty, discount)
    VALUES(@neworderid, 11, 14.00, 12, 0.000);
  INSERT INTO Sales.OrderDetails
      (orderid, productid, unitprice, qty, discount)
    VALUES(@neworderid, 42, 9.80, 10, 0.000);
  INSERT INTO Sales.OrderDetails
      (orderid, productid, unitprice, qty, discount)
    VALUES(@neworderid, 72, 34.80, 5, 0.000);

-- Commit the transaction
COMMIT TRAN;

-- Cleanup
DELETE FROM Sales.OrderDetails
WHERE orderid > 11077;

DELETE FROM Sales.Orders
WHERE orderid > 11077;

DBCC CHECKIDENT ('Sales.Orders', RESEED, 11077);

---------------------------------------------------------------------
-- Locks and Blocking
---------------------------------------------------------------------

-- Make sure in all new connections
-- that you are connected to TSQLFundamentals2008
USE TSQLFundamentals2008;

-- Connection 1
BEGIN TRAN;

  UPDATE Production.Products
    SET unitprice = unitprice + 1.00
  WHERE productid = 2;

-- Connection 2
SELECT productid, unitprice
FROM Production.Products
WHERE productid = 2;

-- Connection 3

-- Lock info

SELECT -- use * to explore
  request_session_id            AS spid,
  resource_type                 AS restype,
  resource_database_id          AS dbid,
  DB_NAME(resource_database_id) AS dbname,
  resource_description          AS res,
  resource_associated_entity_id AS resid,
  request_mode                  AS mode,
  request_status                AS status
FROM sys.dm_tran_locks;

-- Connection info
SELECT -- use * to explore
  session_id AS spid,
  connect_time,
  last_read,
  last_write,
  most_recent_sql_handle
FROM sys.dm_exec_connections
WHERE session_id IN(52, 53);

-- SQL text
SELECT session_id, text
FROM sys.dm_exec_connections
  CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS ST
WHERE session_id IN(52, 53);

-- Session info
SELECT -- use * to explore
  session_id AS spid,
  login_time,
  host_name,
  program_name,
  login_name,
  nt_user_name,
  last_request_start_time,
  last_request_end_time
FROM sys.dm_exec_sessions
WHERE session_id IN(52, 53);

-- Blocking
SELECT -- use * to explore
  session_id AS spid,
  blocking_session_id,
  command,
  sql_handle,
  database_id,
  wait_type,
  wait_time,
  wait_resource
FROM sys.dm_exec_requests
WHERE blocking_session_id > 0;

-- Connection 2
-- Stop, then set the LOCK_TIMEOUT, then retry
SET LOCK_TIMEOUT 5000;

SELECT productid, unitprice
FROM Production.Products
WHERE productid = 2;

-- Remove timeout
SET LOCK_TIMEOUT -1;

SELECT productid, unitprice
FROM Production.Products
WHERE productid = 2;

-- Connection 3
KILL 52;

---------------------------------------------------------------------
-- Isolation Levels
---------------------------------------------------------------------

---------------------------------------------------------------------
-- The READ UNCOMMITTED Isolation Level
---------------------------------------------------------------------

-- Connection 1
BEGIN TRAN;

  UPDATE Production.Products
    SET unitprice = unitprice + 1.00
  WHERE productid = 2;

  SELECT productid, unitprice
  FROM Production.Products
  WHERE productid = 2;

-- Connection 2
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT productid, unitprice
FROM Production.Products
WHERE productid = 2;

-- Connection 1
ROLLBACK TRAN;

---------------------------------------------------------------------
-- The READ COMMITTED Isolation Level
---------------------------------------------------------------------

-- Connection 1
BEGIN TRAN;

  UPDATE Production.Products
    SET unitprice = unitprice + 1.00
  WHERE productid = 2;

  SELECT productid, unitprice
  FROM Production.Products
  WHERE productid = 2;

-- Connection 2
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT productid, unitprice
FROM Production.Products
WHERE productid = 2;

-- Connection 1
COMMIT TRAN;

-- Cleanup
UPDATE Production.Products
  SET unitprice = 19.00
WHERE productid = 2;

---------------------------------------------------------------------
-- The REPEATABLE READ Isolation Level
---------------------------------------------------------------------

-- Connection 1
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN TRAN;

  SELECT productid, unitprice
  FROM Production.Products
  WHERE productid = 2;

-- Connection 2
UPDATE Production.Products
  SET unitprice = unitprice + 1.00
WHERE productid = 2;

-- Connection 1
  SELECT productid, unitprice
  FROM Production.Products
  WHERE productid = 2;

COMMIT TRAN;

-- Cleanup
UPDATE Production.Products
  SET unitprice = 19.00
WHERE productid = 2;

---------------------------------------------------------------------
-- The SERIALIZABLE Isolation Level
---------------------------------------------------------------------

-- Connection 1
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRAN

  SELECT productid, productname, categoryid, unitprice
  FROM Production.Products
  WHERE categoryid = 1;

-- Connection 2
INSERT INTO Production.Products
    (productname, supplierid, categoryid,
     unitprice, discontinued)
  VALUES('Product ABCDE', 1, 1, 20.00, 0);

-- Connection 1
  SELECT productid, productname, categoryid, unitprice
  FROM Production.Products
  WHERE categoryid = 1;

COMMIT TRAN;

-- Cleanup
DELETE FROM Production.Products
WHERE productid > 77;

DBCC CHECKIDENT ('Production.Products', RESEED, 77);

-- In all connections issue:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

---------------------------------------------------------------------
-- Snapshot Isolation Levels
---------------------------------------------------------------------

---------------------------------------------------------------------
-- The SNAPSHOT Isolation Level
---------------------------------------------------------------------

-- Allow SNAPSHOT isolation in the database
ALTER DATABASE TSQLFundamentals2008 SET ALLOW_SNAPSHOT_ISOLATION ON;

-- Connection 1
BEGIN TRAN;

  UPDATE Production.Products
    SET unitprice = unitprice + 1.00
  WHERE productid = 2;

  SELECT productid, unitprice
  FROM Production.Products
  WHERE productid = 2;

-- Connection 2
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

BEGIN TRAN;

  SELECT productid, unitprice
  FROM Production.Products
  WHERE productid = 2;

-- Connection 1

COMMIT TRAN;

-- Connection 2

  SELECT productid, unitprice
  FROM Production.Products
  WHERE productid = 2;

COMMIT TRAN;

-- Connection 2
BEGIN TRAN

  SELECT productid, unitprice
  FROM Production.Products
  WHERE productid = 2;

COMMIT TRAN;

-- Cleanup
UPDATE Production.Products
  SET unitprice = 19.00
WHERE productid = 2;

---------------------------------------------------------------------
-- Conflict Detection
---------------------------------------------------------------------

-- Connection 1, Step 1
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

BEGIN TRAN;

  SELECT productid, unitprice
  FROM Production.Products
  WHERE productid = 2;

-- Connection 1, Step 2
  UPDATE Production.Products
    SET unitprice = 20.00
  WHERE productid = 2;
 
COMMIT TRAN;

-- Cleanup
UPDATE Production.Products
  SET unitprice = 19.00
WHERE productid = 2;

-- Connection 1, Step 1
BEGIN TRAN;

  SELECT productid, unitprice
  FROM Production.Products
  WHERE productid = 2;

-- Connection 2, Step 1
UPDATE Production.Products
  SET unitprice = 25.00
WHERE productid = 2;

-- Connection 1, Step 2
  UPDATE Production.Products
    SET unitprice = 20.00
  WHERE productid = 2;

-- Cleanup
UPDATE Production.Products
  SET unitprice = 19.00
WHERE productid = 2;

-- Close all connections

---------------------------------------------------------------------
-- The READ COMMITTED SNAPSHOT Isolation Level
---------------------------------------------------------------------

-- Turn on READ_COMMITTED_SNAPSHOT
ALTER DATABASE TSQLFundamentals2008 SET READ_COMMITTED_SNAPSHOT ON;

-- Connection 1
USE TSQLFundamentals2008;

BEGIN TRAN;

  UPDATE Production.Products
    SET unitprice = unitprice + 1.00
  WHERE productid = 2;

  SELECT productid, unitprice
  FROM Production.Products
  WHERE productid = 2;

-- Connection 2
BEGIN TRAN;

  SELECT productid, unitprice
  FROM Production.Products
  WHERE productid = 2;

-- Connection 1

COMMIT TRAN;

-- Connection 2

  SELECT productid, unitprice
  FROM Production.Products
  WHERE productid = 2;

COMMIT TRAN;

-- Cleanup
UPDATE Production.Products
  SET unitprice = 19.00
WHERE productid = 2;

-- Close all connections

-- Make sure you're back in default mode
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Change database options to default
ALTER DATABASE TSQLFundamentals2008 SET ALLOW_SNAPSHOT_ISOLATION OFF;
ALTER DATABASE TSQLFundamentals2008 SET READ_COMMITTED_SNAPSHOT OFF;

---------------------------------------------------------------------
-- Deadlocks
---------------------------------------------------------------------

---------------------------------------------------------------------
-- Simple Deadlock Example
---------------------------------------------------------------------

-- Connection 1
USE TSQLFundamentals2008;

BEGIN TRAN;

  UPDATE Production.Products
    SET unitprice = unitprice + 1.00
  WHERE productid = 2;

-- Connection 2
BEGIN TRAN;

  UPDATE Sales.OrderDetails
    SET unitprice = unitprice + 1.00
  WHERE productid = 2;

-- Connection 1

  SELECT orderid, productid, unitprice
  FROM Sales.OrderDetails
  WHERE productid = 2;

COMMIT TRAN;

-- Connection 2

  SELECT productid, unitprice
  FROM Production.Products
  WHERE productid = 2;

COMMIT TRAN;

-- Cleanup
UPDATE Production.Products
  SET unitprice = 19.00
WHERE productid = 2;

UPDATE Sales.OrderDetails
  SET unitprice = 19.00
WHERE productid = 2
  AND orderid >= 10500;

UPDATE Sales.OrderDetails
  SET unitprice = 15.20
WHERE productid = 2
  AND orderid < 10500;

scp123321
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
IEEE-Transactions-LaTeX2e-templates-and-instructions模板
01-05
为了确保论文格式的一致性和专业性，国际电气和电子工程师协会（IEEE）提供了官方的LaTeX2e模板，即“IEEE-Transactions-LaTeX2e-templates-and-instructions”。这个模板包旨在帮助作者们遵循IEEE的标准，以便于...
sql server 2012 T-SQl基础教程 源码和示例数据库
04-01
6. **Chapter 09 - Transactions and Concurrency**：这部分讨论了事务管理，包括事务的ACID属性（原子性、一致性、隔离性和持久性），以及并发控制机制，如锁定和死锁检测。 7. **Chapter 10 - Programmable ...
Chapter 09 - Transactions - Exercises
08-19 284
--------------------------------------------------------------------- -- Microsoft SQL Server 2008 T-SQL Fundamentals -- Chapter 9 - Trans
东北大学数据库概论 chapter7-transactions
weixin_45832571的博客
05-10 135
SQL--Chapter8--Working with Triggers and Transactions
ai516001066的博客
12-04 71
Objectives:1.Implement triggers 2.Implement transactions 转载于:https://www.cnblogs.com/Catherinezhilin/p/7979644.html
Chapter 09-Manipulation Data - 02
weixin_30266885的博客
04-17 73
Changing Data in a Table UPDATE Statement Syntax Modify existing values in a table with the UPDATE statement UPDAE table SET column = value [,column = value,...] [WHERE condition]; ...
Chapter 09-Manipulating Data - 01
weixin_30394633的博客
04-16 89
Objectives After completing this lesson,you should be able to do the following: Describe each data manipulation language(DML) statement. Insert rows into a table. Update rows in a table. Delete...
BITCOIN AND CRYPTOCURRENCY TECHNOLOGIES - chapter 1 -Introduction
qq_43482595的博客
04-06 7460
Chapter 1 In fiat currencies, law enforcement is necessary for stopping people from breaking the rules of the system.Howerver,Cryptocurrencies need to be enforced purely technologically and without relying on a central authority. 传统法定货币(fiat currencies)
Chapter 09-Manipulation Data - 03
weixin_30779691的博客
04-17 89
Rolling Back Changers to a Marker Create a marker in the current transaction by using the SAVEPOINT statement. Roll back to that marker by using the ROLLBACK TO SAVEPOINT statement. UPDATE .....
6.824-Distributed Transactions
importpygame的博客
05-09 188
Mit6.824 2020第12个lec要求阅读书籍内容的翻译+个人理解
spring-chapter01-概述开篇
weixin_33713503的博客
08-15 94
简述 将依据 Spring Framework 5.0.8 版本进行一步一步完成深入学习和使用。 下面先从官方的一些介绍开始我们专栏： Spring Framework为现代基于Java的企业应用程序提供了全面的编程和配置模型 - 在任何类型的部署平台上。Spring的一个关键要素是应用程序级别的基础架构支持：Spring专注于企业应用程...
TSQLFundamentals Code.zip
07-17
7. **事务管理（Chapter 09 - Transactions.sql, Chapter 09 - Transactions - Exercises.sql）** 事务是确保数据一致性和完整性的核心概念，涉及到BEGIN TRANSACTION, COMMIT, ROLLBACK等语句。这两个章节提供了...
Beginning Transact-SQL with SQL Server 2000 and 2005 （2 of 5）
09-07
Chapter 9 - Data Transactions Chapter 10 - Advanced Queries and Scripting Chapter 11 - Full-Text Index Queries Chapter 12 - Creating and Managing Database Objects Chapter 13 - Transact-SQL ...
Beginning Transact-SQL with SQL Server 2000 and 2005 (1 of 5)
09-07
Chapter 9 - Data Transactions Chapter 10 - Advanced Queries and Scripting Chapter 11 - Full-Text Index Queries Chapter 12 - Creating and Managing Database Objects Chapter 13 - Transact-SQL ...
常用的文件系统、存储类型小整理
Hehuyi_In的博客
02-07 3406
最近接触到了五花八门的文件系统、存储类型，名词听得头大，趁假期整理学习一番~
如何使用微软的Copilot AI工具将Word文档转换为PowerPoint
09-02 1142
微软的Copilot工具通过将Word文档转换为PowerPoint演示文稿，极大地方便了用户制作有针对性的演示文稿。这一功能尤其适合那些面对空白幻灯片不知从何入手的人。用户只需在PowerPoint中的Copilot框内输入简单的指令，即可在几秒钟内生成专业的演示文稿。这一过程不仅省时高效，还能大幅提升文档转换的质量和效果。此外，Copilot还支持在移动设备上的操作，使得用户即使不在办公室也可以轻松创建文档和演示稿。从智能手机上，你可以快速转换文档、撰写邮件甚至创建生日派对邀请函。
微软发布Phi-3.5 SLM，附免费申请试用
m0_71858447的博客
09-04 1038
此外，Phi-3.5-mini 与更大的开放权重模型（如 Llama-3.1-8B-instruct、Mistral-7B-instruct-v0.3 和 Mistral-Nemo-12B-instruct-2407）相比具有很强的竞争力。此外，该模型在推理能力方面优于更大的模型。该模型已针对以下列出的精选语言进行了训练：阿拉伯语、中文、捷克语、丹麦语、荷兰语、英语、芬兰语、法语、德语、希伯来语、匈牙利语、意大利语、日语、韩语、挪威语、波兰语、葡萄牙语、俄语、西班牙语、瑞典语、泰语、土耳其语和乌克兰语。
河北金融学院在广东2021-2024各专业最低录取分数及位次表.pdf
最新发布
09-07
全国各大学在广东2021-2024各专业最低录取分数及位次表
Chapter 17: Transactions - Practice Exercise Solutions
"Chapter 17 事务 实践习题答案" 在本章的实践习题中，我们关注的是数据库中的事务处理。首先，我们来看第一个问题的答案。 问题17.1 解答： 即使在自动管理事务的系统中，仍然需要数据库管理员介入来处理那些因为...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值