MSSQL锁定-1.Isolation level (myBased)
各个Isolation Level的测试
MSSQL锁定-1.隔离级别 Isolation level
MSSQL锁定-2.Transaction
MSSQL锁定-3.死锁与阻塞
--Ref
- http://www.es-ivision.com/Channel-4-10-108-0.html
- http://tech.ccidnet.com/art/1105/20050602/261573_1.html
- http://www.blogjava.net/zhengtengfeng/archive/2007/04/23/113025.html
- 并发的影响：http://technet.microsoft.com/zh-cn/library/ms190805.aspx
该文章列出了并发引起的四种影响：丢失更新、脏读(未提交的依赖关系)、不可重复读(不一致的分析)、幻读 - 并发控制类型：http://technet.microsoft.com/zh-cn/library/ms189132.aspx
当许多人试图同时修改数据库中的数据时，必须实现一个控制系统，使一个人所做的修改不会对他人所做的修改产生负面影响。这称为并发控制。并发控制类型分为两大类：乐观并发控制和悲观并发控制
--数据库事务
事务是作为单个逻辑工作单元执行的一系列操作.可以是一条SQL语句也可以是多条SQL语句.一个支持事务 Transaction的数据库系统,必需要具有这四种特性,以保证保证数据的正确性
- 原子性 Atomicity: 一个操作一样不被打断 BEGIN...END. The transaction is either performed entirely or not performed at all
- 一致性Consistency: If the database was consistent before the execution of the transaction .It should remain consistent after the complete execution of that transaction.
- 隔离性 Isolation: The transaction should not be interfered by any other transaction executing concurrently.
- 持久性 Durability: The changes made by the transaction should be permanently committed in the database.
（另外的描述：多个事务同时进行，它们之间应该互不干扰.应该防止一个事务处理其他事务也要修改的数据时，不合理的存取和不完整的读取数据)
数据库在多任务交互的时候,需要用以下4中机制来保护数据库的正确运行,他们是:transactions, locking mechanisms, isolation levels and wait mode
启动事务：使用 API 函数和 Transact-SQL 语句，可以按显式、自动提交或隐式的方式来启动事务。结束事务：您可以使用 COMMIT（成功） 或 ROLLBACK（失败） 语句，或者通过 API 函数来结束事务。创建事务的原则：尽可能使事务保持简短很重要，当事务启动后，数据库管理系统 (DBMS) 必须在事务结束之前保留很多资源、以保证事务的正确安全执行。特别是在大量并发的系统中， 保持事务简短以减少并发 资源锁定争夺，将先得更为重要。
- 事务处理，禁止与用户交互，在事务开始前完成用户输入
- 在浏览数据时，尽量不要打开事务
- 尽可能使事务保持简短
- 考虑为只读查询使用快照隔离，以减少阻塞
- 灵活地使用更低的事务隔离级别
- 灵活地使用更低的游标并发选项，例如开放式并发选项
- 在事务中尽量使访问的数据量最小
--事务的隔离级别
隔离级别与并发性是互为矛盾的：隔离程度越高，数据库的并发性越差；隔离程度越低，数据库的并发性越好。ANSI/ISO SQ92标准定义了一些数据库操作的隔离级别：
隔离级别 脏读（Dirty Read） 不可重复读（NonRepeatable Read） 幻读（Phantom Read）
读未提交（Read uncommitted） 可能 可能 可能
读已提交（Read committed） 不可能 可能 可能
可重复读（Repeatable read） 不可能 不可能 可能
可串行化（Serializable ） 不可能 不可能 不可能
先看横向的:
- 脏读dirty reads：当事务读取还未被提交的数据时，就会发生这种事件。举例来说：Transaction1修改了一行数据，然后Transaction2在 Transaction1还未提交修改操作之前读取了被修改的行。如果Transaction1回滚了修改操作，那么Transaction2读取的数据就可以看作是从未存在过的。
- 不可重复的读non-repeatable reads：当事务两次读取同一行数据，但每次得到的数据都不一样时，就会发生这种事件。举例来说：Transaction1读取一行数据，然后 Transaction2修改或删除该行并提交修改操作。当Transaction1试图重新读取该行时，它就会得到不同的数据值（如果该行被更新）或发现该行不再存在（如果该行被删除）。
- 虚读phantom read：如果符合搜索条件的一行数据在后面的读取操作中出现，但该行数据却不属于最初的数据，就会发生这种事件。举例来说Transactio1读取满足某种搜索条件的一些行，然后Transaction2插入了符合Transaction1的搜索条件的一个新行。如果Transaction1重新执行产生原来那些行的查询，就会得到不同的行。
再看纵向的:以下4种隔离级别都会以创建事务的测试步骤来进行测试
设置事务级别：SET TRANSACTION ISOLATION LEVEL
开始事务：begin tran
提交事务：COMMIT
回滚事务：ROLLBACK
创建事务保存点：SAVE TRANSACTION savepoint_name
回滚到事务点:ROLLBACK TRANSACTION savepoint_name
- Read Uncommitted (Oracle不支持)：最低等级的事务隔离，仅仅保证了读取过程中不会读取到非法数据。上诉4种不确定情况均有可能发生。
与READ COMMITTED 隔离级相反它允许读取已经被其它用户修改但尚未提交确定的数据限制级别最小,脏读在这个隔离级别下是非Consistent Reads的 - Read Committed (SQL Server, Oracle默认)：大多数主流数据库的默认事务等级,保证了一个事务不会读到另一个并行事务已修改但未提交的数据,避免了"脏读取",该级别适用于大多数系统.
SQL Server> SET TRANSACTION ISOLATION LEVEL
{READ COMMITTED | READ UNCOMMITTED | REPEATABLE READ | SERIALIZABLE}
和S锁类似,在此隔离级下SELECT 命令不会返回尚未提交的数据,也不能返回脏数据.
MySQL中类似select ... for update, select ... lock in share mode
Oracle> SET TRANSACTION ISOLATION LEVEL {READ COMMITTED | READ ONLY | SERIALIZABLE};
Oracle> ATER SESSION SET ISOLATION_LEVE {READ COMMITTED | SERIALIZABLE};
事务只能读取已经提交的数据
-- updated 2009-09-15 from《Expert Oracle Database Architecture》P233例子
第一个查询事务
SET TRANSACTION ISOLATION LEVEL Read Committed
begin tran
update Cate SET Sname=Sname+'b' where ID=1
SELECT * FROM cate where ID=1
waitfor delay '00:00:6'
rollback tran --回滚事务
select Getdate()
SELECT * FROM cate where ID=1
第二个查询事务
SET TRANSACTION ISOLATION LEVEL Read Committed
--把committed换成Read uncommitted可看到“脏读取”的示例。
SELECT * FROM cate where ID=1
select Getdate()
--可以看到使用 Read Committed 成功的避免了“脏读取”. - Repeatable Read (MySQL默认,Oracle不支持)：保证了一个事务不会修改已经由另一个事务读取但未提交（回滚）的数据。避免了“脏读取”和“不可重复读取”的情况，但是带来了更多的性能损失。
例子mysql> SELECT @@global.tx_isolation;
mysql> set { global | session } transaction isolation level read committed;
mysql> set autocommit = 0;
在此隔离级下用SELECT 命令读取的数据在整个命令执行过程中不会被更改，其他事务不能执行Update和Delete，
但是可以Insert。此选项会影响系统的效能，非必要情况最好不用此隔离级；
第一个查询事务
SET TRANSACTION ISOLATION LEVEL Repeatable Read
--把Repeatable Read换成Read committed可以看到“不可重复读取”的示例
begin tran
SELECT * FROM cate where ID=33 --第一次读取数据
waitfor delay '00:00:6'
SELECT * FROM cate where ID=33 --第二次读取数据，不可重复读取
commit
第二个查询事务
SET TRANSACTION ISOLATION LEVEL Read Committed
update cate set Sname=Sname+'JD' where ID=33
SELECT * FROM cate where ID>30 - Serializable (Oracle中Read only和这个很相似，唯一区别是Read only事务不允许修改，因此不会遇到ORA-08177错误)：
- 最高等级的事务隔离，上面3种不确定情况都将被规避。这个级别将模拟事务的串行执行。-- updated 2009-09-15 from《Expert Oracle Database Architecture》P239
这是最大的限制，和X锁类似，不允许其他事务进行任何写访问。如非必要，不要使用这个选项。在事务的开始使用这个命令即可，该隔离级别一直对该SQL Server连接（不是本事务）有效，直到下一次使用本命令设置了新的隔离级别为止。
在第一个查询窗口执行
SET TRANSACTION ISOLATION LEVEL Serializable
-- 把Serializable换成Repeatable Read 可看到“幻像读”的示例
begin tran
SELECT * FROM cate where ID>30 --第一次读取数据，“幻像读”的示例
waitfor delay '00:00:6' --延迟6秒读取
SELECT * FROM cate where ID>30 --第一次读取数据
commit
第二个查询事务
SET TRANSACTION ISOLATION LEVEL Read Committed
Delete from cate where ID>33
SELECT * FROM cate where ID>30 - 例如，如果将事务隔离级别设置为 SERIALIZABLE，并且在 SELECT 语句中使用表级锁定提示 NOLOCK，则键范围锁通常用于维护不采用可串行事务。
USE pubs
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
SELECT au_lname FROM authors WITH (NOLOCK)
GO
--生成的锁是：
EXEC sp_lock
spid dbid ObjId IndId Type Resource Mode Status
1 1 0 0 DB S GRANT
6 1 0 0 DB S GRANT
7 1 0 0 DB S GRANT
8 4 0 0 DB S GRANT
8 4 0 0 DB S GRANT
8 4 117575457 0 TAB Sch-S GRANT
9 4 0 0 DB S GRANT
9 1 21575115 0 TAB IS GRANT
SELECT object_name(117575457)
GO
-----------------------------
authors
--引用 authors 唯一采用的锁是架构稳定性 (Sch-S) 锁。在这种情况下不能保证可串行性。
--问题
- 问题1:
为什么?create table test (id int identity, c varchar(10));
--使用默认隔离机制
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
--session 1:
begin tran
insert into test values (1);
--session 2:
select * from test
--在SQL Server05内session 1提交之前，此查询被block住
--在Oracle内session 1提交之前，此查询不会被block住 - 问题2:第三个session 不停的查询，偶尔会发现这样的现象：读取时发现自增列有间断，比如第N次读，读到了id=1001的记录但未读到 id＝1000的记录，但下次读时读到了这条记录。这是怎么回事？ 难道是因为id=1001的记录在第N次读时已经提交 而id=1000的未提交？
但按照问题1中的现象当有未提交事务时第N次读应该block住，要等待id=1000的事务提交后才能执行啊，这时应该能读到id=100
答:就是sql的读一致性和oracle不同， 无论查询执行多常时间、查询如何多的记录，oracle能够保证读一致性，即读到的所有记录都是在select查询开始执行那一刻的状态（snapshot）,读不到之后的修改（即使提交）；sql server则不是这样，它的select能够读到在它开始执行之后提交的事务修改
1，insert/update产生的锁会block住select(如查询这行的话)，这和oracle不一样；oracle是不会block select的；
2，就是sql的读一致性和oracle不同，无论查询执行多常时间、查询如何多的记录，oracle能够保证读一致性，即读到的所有记录都是在select查询开始执行那一刻的状态(snapshot),读不到之后的修改(即使提交)；sql server则不是这样，它的select能够读到在它开始执行之后提交的事务修改2000的时候，读写会堵塞的,2005的时候，学oracle了，增强了多版本控制和快照
xxd:可是我测试了一下,2005下还是阻塞
作者：Buro#79xxd
出处：http://www.cnblogs.com/buro79xxd/
文章版权归本人所有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。
