SQL Server2008 事务和锁详解（MSSQL个人笔记之数据库优化之路 五）
--SQL Server2008 事务和锁详解（MSSQL个人笔记之数据库优化之路 五） /******************************************************************************** *主题：SQL Server2008 事务和锁详解 *说明：本文是个人学习的一些笔记和个人愚见 * 有很多地方你可能觉得有异议，欢迎一起讨论 *作者：Stephenzhou(阿蒙) *日期: 2012.07.31 *Mail:szstephenzhou@163.com *另外：转载请著名出处。 **********************************************************************************/
今天主要讲下锁，但是说到锁肯定就是要说到事务。
说到事务就要说下它的三个特性： 自动性，一致性，独立性，持久性。
事务的分类：自动提交事务，显式事务，隐式事务，批处理级事务。
在这里不做过多的介绍和说明，如有疑问可以留言。。
--事务个数的查询 select @@TRANCOUNT /*-----------0(1 行受影响)*/--save transaction 设置保存点 use Erp_System gobegin transaction InsertTranslsave transaction Adelete consume_Shopid_Range where id='10010xxxxxxx1' and Shopid=2 and GoodsId=6 and Amount =894 and ConsumeDate='2012-07-26 11:55:40.153' and mark='2FA4E2F3-DD8B-4ED3-8D9C-D7AE33BA5D0D' rollback transaction A insert into consume_Shopid_Range (id,Shopid,GoodsId,Amount,ConsumeDate,mark)values ('10010xxxxxxx2',3,1,321,'2012-07-31 11:55:40.153','szstephenzhou') commit transaction InsertTransl go /*(0 行受影响)(1 行受影响)*/--分析下上个结果：--在上面的一个事务中，所限开始了一个事务 InsertTransl--在这个事务中设置了保存的A--然后删除一条记录 --回顾到A点 也就是没有删除--新增了一条记录--commit提交事务InsertTransl---Set xact_abort 语句 /* *格式如下: *SET XACT_ABORT {ON|OFF} *当设置为 setxact_abort on 时，如果SQL语句出现运行错误，则数据库引擎将终止并回滚整个事务， 即使前面能够正确的执行的SQL。 *当设置成set xact_abort off 如果SQL错误的时候，数据库只回滚产生错误的sql语句。并且事务继续 默认的情况是ON*/if OBJECT_ID('tb') is not nulldrop table tbgocreate table tb(id int identity primary key ,name varchar(10)) set xact_abort on begin transaction insetran insert into tb values('stephen'); insert into tb values('mrzhou'); insert into tb values('stephen----------'); insert into tb values('刘德华'); insert into tb values('张学友'); commit transaction insetran go /*消息 208，级别 16，状态 1，第 3 行对象名 'tb' 无效。 */ select * from tb /* id name----------- ----------(0 行受影响) */ set xact_abort off begin transaction insetran insert into tb values('stephen'); insert into tb values('mrzhou'); insert into tb values('stephen----------'); insert into tb values('刘德华'); insert into tb values('张学友'); commit transaction insetran go /*(1 行受影响)(1 行受影响)消息 8152，级别 16，状态 14，第 4 行将截断字符串或二进制数据。语句已终止。(1 行受影响)(1 行受影响)*/select * from tb/*id name----------- ----------1 stephen2 mrzhou4 刘德华5 张学友(4 行受影响)*/--如上所述 当set xact_abort on 如果出现错误就会全部回滚 而设置成off的时候就只跳过错误继续执行。。--但是如果设置成off的时候也是没办法执行跳过。这是为什么呢？？ set xact_abort off begin transaction insetran insert into tb values('stephen'); insert into tb values('mrzhou'); insert into tb values(1,'stephen'); insert into tb values('刘德华'); insert into tb values('张学友'); commit transaction insetran go /* 消息 8101，级别 16，状态 1，第 5 行仅当使用了列列表并且 IDENTITY_INSERT 为 ON 时，才能为表'tb'中的标识列指定显式值。 */ --因为在这里是属于编译错误，在identity属性中不能插入数字。 --在事务中有些语句是不能用的 如下 /* *CREATE DATABASE *ALTER DATABASE *DROP DATABASE *RECONFIGURE *BACKUP *RESTORE *UPDATE STATISTICS */
锁机制
什么是锁，锁到底是个什么东西？有很多人都感觉是懂非懂的样子，了解了，好像是个什么东西。其实很简单 什么到底是什么呢？锁是一种规则。是用来控制同步
数据访问控制的一种机制。
打个比方吧，数据库中的锁就像交通信号灯。而车子就是每个事务。如果没有好好的控制红绿灯的话就会怎么样，当然是堵车了。如果没有控制好锁 同样会堵塞。
锁的定制
大家都知道，数据库的资源只由一个用户使用只要程序没问题，数据就不会出现不一致的情况。如果两个或者多个用户同时修改就有可能出现并发冲突导致如下错误：
更新丢失
更新丢失是指两个用户同时更新一个数据库对象，其中一个用户更新覆盖了之前那个用户的更新从而导致错误
不可重复读
一个用户在一个事务中读取的数据前后不一致，其中可能是有别的用户做了修改
幻读
一个用户读取一个结果集后，其他用户对该结果集进行了插入或者删除，当第一个用户再读这个结果集的时候发现数据多了或者少了。
为了解决这些问题，SQLserver 数据库引入了锁。
1》从数据库系统的角度分为 共享锁S 排它锁X（独占锁） 更新锁 U 意向锁 架构锁和大容量更新锁等
共享锁S：
并发执行对一个数据资源读取操作时，任何其他事务不能修改该资源的数据；读取操作完成后 S锁释放。
排它锁X：
在执行INSERT ,UPDATE,DELETE时，确保不会同时对同一资源进行多重更新操作。修改数据之前，需要执行读取操作获取数据 此时需要申请共享锁S，然后再申请排它锁X
更新锁U：
为了避免死锁的情况而使用的锁模式
两个事务对一个数据资源先读取再更新的操作，使用了S锁和X锁进行操作。X锁一次只有一个X锁在对象上的，也就是说一次只有一个事务可以获取资源的更新锁。如果需要对数据进行修改操作，则需要把更新锁转换为U锁，否则将锁转换成S锁
意向锁I：
需要在层次结构中的某些底层资源上获取S锁或者X锁或者U锁。意向锁可以提高性能，因为数据库引擎不需要检查表中的每行或每页上的S锁就确定是否可以获取到该表上的X锁。
架构锁：
为了防止修改表结构时对表进行的并发访问锁。
大容量更新锁
允许多个线程将数据并发地大容量加载到同一个表中，同时禁止其他与大容量插入数据无关的进程访问该表
2》从并发的手段分为乐观并发和悲观并发
乐观并发：
允许事务在执行过程中不锁定任何资源，只有当事务视图修改数据时，才会对资源检查。如果确定有冲突，应用程序重新读取数据然后修改操作。这是假设冲突不存在，节约了锁的机制。如果遇到并发冲突，再重新执行事务。
悲观并发：
在事务中需要使用不同的锁。如果一个用户的某个操作应用了锁，则只有等释放后其他用与该锁相冲突的操作。
下表显示了最常见的锁模式的兼容性。
现有的授权模式
请求的模式
IS
S
U
IX
SIX
X
意向共享 (IS)
是
是
是
是
是
否
共享 (S)
是
是
是
否
否
否
更新 (U)
是
是
否
否
否
否
意向排他 (IX)
是
否
否
是
否
否
意向排他共享 (SIX)
是
否
否
否
否
否
排他 (X)
否
否
否
否
否
否
意向排他锁（IX 锁）与 IX 锁模式兼容，因为 IX 表示打算只更新部分行而不是所有行。还允许其他事务尝试读取或更新部分行，只要这些行不是其他事务当前更新的行即可。此外，如果两个事务尝试更新同一行，则将在表级和页级上授予这两个事务 IX 锁。但是，将在行级授予一个事务 X 锁。另一个事务必须在行级锁被删除前等待。
锁的粒度
锁粒度指SQL Server锁定数据资源的类型。锁定的粒度越小，数据库的并发就越高。因为锁定越小的数据库资源，不会影响其他用户使用其他资源。
SQL SERVER 支持的粒度包括：
行RID 键 key 页page 区间 extent 堆hobt 表table 文件file 应用程序application 元数据metadata 分配单元 application_unit 数据库database
当然在实际使用过程中不需要考虑粒度。sqlserver数据库引擎采用动态锁策略。
如果一个事务在一个表中很多行放了意向锁，则数据库引擎可以为其分配一个表级锁，然后释放所有低级别的行级锁，从而减少管理锁的开销。
跟踪锁的活动情况
明天继续写、。。。。