SQL中并发带来的问题以及锁的类型和用法

一、锁

为甚么引入？

 

为了提高资源利用率，事务采用并发执行，但也因此带来如下问题：

1.  会产生多个事务同时存取同一数据的情况
2. 可能会存取和存储不正确的数据，破坏事务一致性和数据库的一致性

 

不一致性的体现：

1）丢失修改：两个事务T1和T2读入同一数据并修改，T2的提交结果破坏了T1提交的

                 结果，导致T1的修改被丢失

 

 

2）不可重复读：指事务T1读取数据后，事务T2 执行更新操作，使T1无法再现前一次

                    读取结果。

 

 

例如：

 

 

 

 

3）

 

 

 

 

二、处理并发问题可采用封锁

 

1）封锁就是事务T在对某个数据对象（例如表、记录等）操作之前，先向系统发出请

     求，对其加锁。

 

2）加锁后事务T就对该数据对象有了一定的控制，在事务T释放它的锁之前，其它的

      事务不能更新此数据对象。

 

3）一个事务对某个数据对象加锁后究竟拥有什么样的控制由封锁的类型决定。

 

三、基本封锁类型

 1）从数据库系统的角度大致分为：分为独占锁（即排它锁），共享锁和更新锁 

 

 2）而为了提高对某个数据对象加锁时系统的检查效率，又引进意向锁。

 

 3）根据锁的粒度（后面有讲何为粒度），又有：

 

5）此外，特殊锁模式

     包含三种额外的锁模式：架构稳定锁、架构修改锁和大容量更新锁。当查询被编译时，架构稳定锁会防止其他

进程获取架构修改锁（在表结构被修改时获得）。在执行BULK INSERT 命令或者使用 bcp 实用工具导入数据时会

获取大容量更新锁。（注：BULK INSERT 为SQL Server中批量导入数据的命令，点击查看）

 

四、锁的粒度

 

五、各种乱七八糟的锁的解释以及用法

 

  共享锁：（holdlock）数据被读取时有SQL Server 自动获得(SELECT)。可在表、页

                 面、索引键或单个行上持有共享锁。通常，共享锁只要数据读取完毕就

                   释放，但这会可以通过使用查询提示或不同的事务隔离级别而改变。

 

独占锁：(排他锁, xlock) 当数据 INSERT、UPDATE 或 DELTE 操作修改时，SQL 

                  Server 自动获得数据上的独占锁。在一个特定的数据源上，同一时刻只有

                 一个进程可以获得独占锁；如果一个进程独占地锁定了请求的数据源，

                 那么别的进程无法再此数据源上获得任何种类的锁。独占锁一直持有到事                   务结束。

 

用法，假设有事务T1，T2

//共享锁

T1:
begin tran
select * from table(holdlock) 

T2:
begin tran
select * from table(holdlock) 

//写锁

T1；
 begin tran 
select * from sc(xlock);//此处不能设置为共享锁，不然会死锁，因为共享锁在在读取后就会释放，这样T1的更新操作和T2的更新操作就起冲突了
waitfor  Delay '00:01:00';
 update sc set 
score=60
 where sno='00001' and cno='001'
commit Tran;

T2；
 begin tran 
select * from sc(xlock);
waitfor  Delay '00:01:00';
 update sc
 set score=60
 where sno='00002' and cno='001'
commit Tran;

 注：数据库规定同一资源上不能同时共存共享锁和排他锁。

 

 

 更新锁(updlock):更新锁实际上并不是一种独立的锁，而是共享锁和独占锁的混合。当

                    SQL Server 执行数据修改操作却首先需要搜索表以找到需要修改的资源

                    时会获得更新锁。不要被名字误导，更新锁并不只是用于 UPDATE 操

                    作。对任何需要在进行实际修改之前搜索数据的修改操作使用更新锁。

                   这样操作包括，首先更新及删除，带有聚集索引的表上进行插入操作。

                   一个事务只能有一个更新锁活的此资格。 

//更新锁 

T1；
 begin tran 
select * 
from student(updlock);
 update student 
set sage=sage+1
 where sno='00001'

T2；
begin tran 
select * 
from student;
update student 
set sage=sage+1
 where sno='00002'

  意向锁：意向锁的含义是如果对一个结点加意向锁，则说明该结点的下层结点正在

           被加锁；对任一结点加锁时，必须先对它的上层结点加意向锁。例如，对任

           一元组加锁时，必须先对它所在的关系加意向锁。于是，事务T要对关系R1

           加 X锁时，系统只要检查根结点数据库和关系R1是否己加了不相容的锁，

           而不再需要搜索和检查尺中的每一个元组是否加了X锁。三种常用的意向

           锁：意向共享锁（Intent Share Lock，简称IS锁）；意向排它（Intent 

           Exclusive Lock，简称IX锁）；共享意向排它锁（Share Intent Exclusive 

           Lock，简称SIX锁）。

（关于更详细的意向锁，点击打开链接）

 

//行锁

SELECT * 
FROM student ROWLOCK 
WHERE sno = '00001' 

//表锁

SELECT * 
FROM student TABLELOCK 
WHERE sno = '00001' 

注意: 锁定数据库的一个表的区别 

SELECT * FROM table WITH (HOLDLOCK) 其他事务可以读取表，但不能更新删除 

SELECT * FROM table WITH (TABLOCKX) 其他事务不能读取表,更新和删除

六、查看锁

 

1. EXEC SP_LOCK 

 2.查询分析器中按Ctrl+2可以看到锁的信息