sql 锁

SQL 名词解释

1. 事务

1.1 行级锁

行级锁是针对行来锁定的，比如在事务里，进程A执行了一条update语句:
update student set name='xx' where id=13
则行级锁会锁住student表里id=13的记录，不让别的进程对它操作，
只有等事务完成后才解除锁,举个例子，以 SQL SERVER为例,
同时打开两个查询分析器，在第一个查询分析器里写:

use northwind
select * from suppliers
begin transaction
   update suppliers set CompanyName='xx' where SupplierID=3
   waitfor delay '00:00:20'
commit transaction

在第二个查询分析器里写:

select * from suppliers

然后先运行第一个查询分析器里的代码，再运行第二个查询分析器里的
代码，可以看到第一个查询分析器一直运行，运行了大概20秒后执行
完毕，第二个查询分析器也一样，运行了大概20秒才停止，
这说明执行 select * from suppliers 时在等待，如果不运行第一个
查询分析器里的代码，直接运行第二个查询分析器里的代码，那几乎不
用等待就可以看到结果了；
修改第二个查询分析器的代码为: 
select * from suppliers where SupplierID<>3
然后先运行第1个查询分析器的代码，再运行第二个查询分析器的代码，
可以看到第二个查询分析器一运行马上就出结果了，没有等待，
再修改代码为:
select * from suppliers where SupplierID=3
重复前面的操作，可以看到需要等待，等待约20秒后才看到结果
这很明显的告诉我们，行级锁会锁住修改的行，让别的进程无法操作
那些行，只有事务完成后别的进程才可以操作，而没有修改的行，别的
进程就可以任意操作，不会有限制
1.2 页级锁(1)

先理解页这个概念，在SQL SERVER里建表时，如果字段大小比较大时，
往往会有提示: 表中允许的最大行大小 8060
比如在SQL SERVER2000 里新建一个表:

create table Test(Fld1 char(5000),Fld2 char(5000))

会提示建立失败，原因如:
“创建表 'Test' 失败，因为行大小将为 10021（包括内部开销），而该值超过了表中允许的最大行大小 8060。”

为什么它会限制行大小为8060呢？因为在 SQLSERVER2000里，一页的
大小为8K，这8K里包括96字节的页头、36字节的其它信息、8060字节的
数据区，而数据就存储在8060字节的数据区里，
一页能存储多少行呢？这要看行的大小了，比如如果行大小为2000，
则一页能存储4行，注意行大小不包括文本和图象字段，
比如数据库northwind的customers表的行大小为 298，
则一页可以存储 27 行
 看看行大小计算的问题:

use northwind
alter table customers add xx char(8000)

运行结果有警告:
-----------------------------------------
 警告: 已创建表 'customers'，但其最大行大小（8578）超过了每行的最大字节数（8060）。如果结果行长度超过 8060 字节，则此表中行的 INSERT 或 UPDATE 将失败
-----------------------------------------
它提示行大小为8578，则修改前的customers的行大小为578，
可为什么我将各个字段的大小加起来才268呢，
这有两个原因，一方面，数据库用两个字节存储一个nvarchar类型的字符
nchar也一样，而customers的字段类型为nchar和nvarchar，所以实际大小为 268*2=536 ，那还有42呢？42是表的其它开销。

从行与页的关系可以看出，行的大小越小，则一页能存储的行数越多，
数据库查询时，从一页读到另一页，比只读一页的记录要慢得多，
所以要减少跨页读取的次数，
 
比较下面的两个语句:

create table x1(a char(5000),b char(5000))
create table x2(a varchar(5000),b char(5000))

运行结果为:
------------------------------------
服务器: 消息 1701，级别 16，状态 2，行 2
创建表 'x1' 失败，因为行大小将为 10021（包括内部开销），而该值超过了表中允许的最大行大小 8060。
警告: 已创建表 'x2'，但其最大行大小（10023）超过了每行的最大字节数（8060）。如果结果行长度超过 8060 字节，则此表中行的 INSERT 或 UPDATE 将失败。
------------------------------------
x1创建失败，x2创建成功，但有警告，为什么呢？
这要比较char和varchar的区别了，当创建x1时，最大行大小10023就是
实际的行大小，因为char是定长的，大小总是10023，而x2不同，
varchar是变长的，虽然最大行大小是10023，而实际行大小却不一定的，
实际行大小随字段a的值的大小的变化而变化，
所以，每页能存储的行数，如果是定长的，那在建表时就可以确定了，
如果是变长的，那要根据表中的数据来确定，当然，SQL SERVER存储记录
时，对于页的选择还会考虑一些问题，也并不完全是这样看看Northwind数据库的Customers表吧，
Customers的主键字段为CustomerID，主键是聚集索引的，
主键的顺序代表了行的实际存储顺序，
比如你往Customers里插入一条记录:
insert into Customers(CustomerId,CompanyName) values('cvcvc','ff')

然后用select * from customers查看数据，
可以看到新插入的记录自动排在了CustomerId等于CONSH的后面，
看起来就和 select * from customers order by customerId
查出来的数据一样，聚集索引就是这样，记录的物理存储顺序与
聚集索引的顺序是一样的.
 
看看Customers表，打开三个查询分析器，在第一个表写：
begin transaction 
 update customers with(PagLock) set Address=Address
 where customerId='ALFKI'
 waitfor delay '00:00:30' 
commit transaction 
在第二个查询分析器里写:
select * from customers where customerId='GREAL'
在第三个查询分析器里写:
select * from customers where customerId='GROSR'
先运行第一个查询分析器，然后运行第二个，再运行第三个，
可以看到，第一和第二个查询分析器等待执行了20秒，
而第三个查询分析器没有等待立即就显示运行结果了，
我更新的是'ALFKI'，因为是页锁，所以它锁住了一页的数据，
从'ALFKI'到'GREAL'的行都锁住了，这也说明，
'ALFKI'到'GREAL'之间的共34行都是属于同一页的，
你可以将第一个查询分析器的'ALFKI'换成'DRACD'，可以看到运行
结果是一样的，如果换成'HANAR',那结果就变了，
变成第一个和第三个查询分析器在等待，而第二个查询分析器不用等待，
因为'HANAR'和'GROSR'属于同一页，而'GREAL'在其它的页,
页锁概念是比较简单的，但页的概念却比较复杂，
页是在SQLSERVER的内部管理的，用户看不到，页比较抽象，
对于变长的数据类型，页的分配是随数据的变化而变化的，
请参考数据库相关的资料。

1.3 表锁

在第一个查询分析器写:
begin transaction tran1
 update customers with(TabLock) set City=City 
 where CustomerId='ALFKI'
 waitfor delay '00:00:20'
commit transaction tran1

在第二个查询分析器写:
select * from customers where customerId='WOLZA'

先运行第一个查询分析器，再运行第二个，两个查询分析器都在等待.
注意customerId='WOLZA'是表的最后一条记录 
 
 
1.4 阻塞

前面的例子里一个事务未提交，导致别的事务必须等待，这就是阻塞，
查看阻塞可以用sp_lock，打开三个查询分析器，
第一个写:
begin transaction tran1
 update products set productName=productName+'A'
 where ProductId=1
 waitfor delay '00:00:30'
commit transaction tran1
第二个写:
select * from products
第三个写:
sp_lock
依次运行第一个、第二个、第三个，
然后查看第三个分析器，看看Status列，
看是否有Status='Wait'的行，比如我这里查看有这么一行:

53 6 117575457 1 KEY (010086470766) S WAIT

其中ObjId=117575457

然后运行:
use northwind
select object_name(117575457)
可以看到对应的表为 Products
1.5 死锁

同时打开两个查询分析器，
第一个写：
begin transaction tran2
 update products set productName=productName+'A'
 where ProductId=2
 waitfor delay '00:00:10'
 update products set productName=productName+'A'
 where ProductId=1
commit transaction tran2

第二个写:
begin transaction tran1
 update products set productName=productName+'A'
 where ProductId=1
 waitfor delay '00:00:10'
 update products set productName=productName+'A'
 where ProductId=2
commit transaction tran1

先运行第一个，再运行第二个
然后等待它们执行，等待大概十多秒，
检查运行结果，可以看到其中一个出错，错误提示如:

服务器: 消息 1205，级别 13，状态 50，行 1

在查询分析器里按F1打开帮助，在帮助里选择索引选项卡，
输入 1205 ，你仔细查看帮助文档是如何描述 1205 错误的，

为什么会死锁呢？看看执行过程,为了简单，我将productId简写为id
先是分析器1更新id=2的记录，并锁住id=2的记录，那别的进程都无法
操作id=2的记录，
然后分析器2更新id=1的记录，并锁住id=1的记录，同样别的进程无法
操作id=1的记录，
然后分析器1更新id=1的记录，因为id=1的记录被分析器2锁住了，
所以必须等待，分析器1被阻塞
同样分析器2更新id=2的记录，因为id=2的记录被分析器1锁住了，
所以也要等待，分析器2被阻塞
两个分析器都要等待对方，所以就出现死锁，哪个都不能执行，

当然，SQLSERVER2000为了解决死锁问题，它会干掉其中一个进程
来结束死锁。

1.6 占用读

占用读指可以读别的进程未提交的数据，
打开两个查询分析器，第一个写:
begin transaction tran1
 update products set productName=productName+'C'
 where ProductId=1
 waitfor delay '00:00:15'
commit transaction tran1

第二个写:
set transaction isolation level read uncommitted
select * from products where ProductId=1

依次运行第一个和第二个，
可以看到第一个在等待，而第二个不用等待，
因为我在第二个里设置了隔离级别为read uncommitted，
就是允许读别的事务未提交的数据，
你看看第二个的运行结果，找到products列,看到products列已经修改了
如果你修改第二个查询分析器代码为:
set transaction isolation level read committed
select * from products where ProductId=1

同样运行，那第二个也要等待了，因为隔离级别是read committed，
只能读提交后的数据，不能读未提交的修改，这样就防止了
占用读，SQLSERVER2000里默认是read committed
说明，占用读也叫脏读，脏读就是修改了但没提交的数据，
在文本编辑器里也有脏读的概念，就是修改了但未保存的数据
1.7 不可重复读

事务里执行两次相同的查询时，查询出来的结果不相同，
说明是不可重复读，打开两个查询分析器，
第一个写:
use northwind
set transaction isolation level read committed
begin transaction tran1
 select * from region where regionId=3
 waitfor delay '00:00:10'
 select * from region where regionId=3
commit transaction tran1

第二个写:
use northwind
update region set regionDescription='xx' where regionId=3

依次运行第一个和第二个分析器，第一个分析器等待10秒，第二个
不用等待立即得到结果，第一个分析器运行结果为:
3 Northern
3 xx

两次读得的值不相同，
修改第一个查询分析器代码为:
use northwind
set transaction isolation level repeatable read
begin transaction tran1
 select * from region where regionId=3
 waitfor delay '00:00:10'
 select * from region where regionId=3
commit transaction tran1

第二个修改为:
use northwind
update region set regionDescription='yy' where regionId=3

同样依次运行第一个和第二个，看到第一个在等待，
第二个也在等待，第一个分析器运行结果为:
3 xx
3 xx

看看两次的区别，第一次我设置隔离级别为read committed,
第二次我设置为repeatable read,
repeatable read 会锁住读的数据，
read committed 会锁住修改的数据,
repeatable read会锁住insert、update、delete、select操作的数据
read committed只锁insert 、update、delete, 不锁select查询的数据
1.8 幻像读

打开两个查询分析器，第一个写: 
use northwind
set transaction isolation level repeatable read
begin transaction tran1
 select * from region
 waitfor delay '00:00:10'
 select * from region
commit transaction tran1

第二个写: 
use northwind
insert into region values(5,'xx')

依次运行第一个和第二个分析器，第一个分析器等待10秒，第二个 
不用等待立即得到结果，第一个分析器运行结果为: 
1 Eastern 
2 Western 
3 Northern 
4 Southern 

1 Eastern 
2 Western 
3 Northern 
4 Southern 
5 xx 

比较两次查询的结果，第二次查询多了一行，
修改第一个分析器的代码为:
use northwind
set transaction isolation level serializable
begin transaction tran1
 select * from region
 waitfor delay '00:00:10'
 select * from region
commit transaction tran1

修改第二个分析器代码为:
use northwind
insert into region values(6,'yy')

再依次运行第一个和第二个分析器，
可以看到两个分析器都要等待，第一个的运行结果是：
两次查询返回的行数是相同的。

理解占用读、不可重复读、幻像读要从数据库如何操作来避免他们上
来理解，如果只从概念上去理解，概念往往很抽象，比较晦涩难懂，
而且概念往往只说到其中一个方面，应该弄清楚各种级别的琐是如何
避免出现占用读、不可重复读、幻像读的。
read uncommitted不设置锁，
read commmitted会锁住update、insert、delete
repeatable read会锁住update、insert、delete、select
seriablizable会锁住update、insert、delete、select

repeatable read和seriablizable的区别在于:
repeatable read锁住时别的事务不能update、delete锁住的数据，
但别的事务能够插入，
seriablizable锁住时别的事务不能update、delete、insert 说明：
repeatable read 和 seriablizable 对 select 的锁定采用范围的方式
要锁哪些行，主要是受where语句的限制，另外还受行锁、页锁、表锁方式
的限制，对于update、insert、delete的锁定范围比较明确，
repeatable read隔离级别对select的锁定也比较明确，
而seriablizable对select的锁定，当别的事务insert时，
哪些时候不能插入呢？这个范围如何确定？
因为锁的概念往往是针对已有的数据，而insert插入的数据是原来表里
没有的，原来表里没有，那又如何锁定呢？
比如:
set transaction isolation level seriablizable 
select * from region
则锁住表的所有行，比如开始有四行，则锁住四行，
那insert一行呢，这里有个范围，那就是select的范围，
它会判断insert的行是否在锁定的范围之内，比如:
use northwind
set transaction isolation level serializable
begin transaction tran1
 select * from region where regionId>6
 waitfor delay '00:00:10'
commit transaction tran1

如果别的事务插入记录:
insert into region values(5,'yy')

因为插入的记录regionId=5，而6<5，不满足锁定的条件，
所以该插入是允许的，如果别的事务插入记录:
insert into region values(7,'yy')
因为7>6,满足条件，所以插入被阻塞，只能等待锁释放才能插入
不过，到底锁定哪些记录，这比较难说，锁做为SQLSERVER的内部管理，
到底是怎么样的不怎么清楚，我试过有些情况不满足的条件的记录也
被阻塞，不过有点是清楚的，那就是满足条件的一定被阻塞 1.8 隔离级别

前面已经说过了，有四种隔离级别:
read uncommitted
read committed
repeatable read
serializable

read committed是默认的隔离级别，
隔离级别对单个用户有用，比如你设置了隔离级别为serializable，
那只对你自己有用，对别的用户不起作用，设置了隔离级别后，
那一直有效，直到用户退出为止，
锁的作用主要是用来保证数据一致性的，
read uncommitted不会在被读的数据上放置锁，所以它执行
的速度是最快的，也不会造成阻塞和死锁，但因为数据一致
性问题，所以往往不采用，当然可以通过别的技术比如增加
rowverision列等来保证数据一致性，但对于复杂的操作，还
是选择事务比较安全,对于事务我经历过一些教训，
比如一次我在VB里保存数据，大致如:

begin transaction
 declare @id as int
 insert into A(...) values(...)
 select @id=max(id) from t1
 insert into B(AId...) values(@id...)
commit transation

其中A表的id字段是自动编号的，我先在A表插入一条记录，
再将A表刚插入的记录的Id插入到B表，必须保证@id是前面insert
生成的那个id，
但测试时，因为客户有很多电脑同时录，所以导致一些id不一致的情况，
为什么会不一致呢？我不是已经加了事务了吗？
做一个例子，同样打开两个查询分析器，第一个写:

use northwind
set transaction isolation level serializable
begin transaction tran1
 insert into region values(10,'aa')
 waitfor delay '00:00:10'
 select max(regionId) from region
commit transaction tran1

第二个写:
 insert into region values(11,'aa')

依次执行第一个和第二个分析器，
本来我希望第一个分析器里查询出来的是10，可结果却是11
在这里，唯一的方法是指定表锁，如:
insert into region with(TabLock) values(10,'aa')

只有指定表锁，让别的事务无法操作它，才能保证数据一致，
当然，如果是自动编号，那可以用 @@identity 来获取刚生成的Id号，
比如:
insert into orders(CustomerId) values('ALFKI')
print @@identity

这个技巧在别的数据库驱动程序里可能无效，事务才是普遍支持的 补充（表来源: SQL SERVER7.0 系统管理指南)

隔离级别　　　　阻塞风险　防止占用读　防止不可重复读　防止幻像读
---------------------------------------------------------------
read uncommitted 最低　　　NO　　　　　　NO　　　　　　　NO
read committed　 较低　　　Yes　　　　　 NO　　　　　　　NO
repeatable read　较高　　　Yes　　　　　 Yes　　　　　　 NO
serializable　　 最高　　　Yes　　　　　 Yes　　　　　　 Yes
1.9 显式锁

显式锁是在select、insert、delete、update语句里指定锁的类型，
如:

select * from authors with(RowLock) where au_id <='555-55-5555'

如果不用显式锁，那就是:

select * from authors where au_id <='555-55-5555'

如果不用显式锁，那采用那种锁是由SQL SERVER内部来决定的， 
 
 
2.　数据库设计

2.1 冗余数据

冗余数据就是重复的数据，冗余数据出现的情况很多，这里举一种情况,
就是表之间的冗余，假设我在northwind里建立一个sales表,字段如:
timeId，date，year，month，custId，custName，productId,
QuanPerUnit，productName，regionID，regionName,storeId，
storeName，quantity，price，money，

现在我要查看2005年各商品的销售情况，那可以用:

select productName,QuanPerUnit,sum(quantity),sum(money) 
from sales where year=2005 group by productId

如果我这样建sales表:
timeId，custId，productId,storeId，regionID
quantity，price，money

那查询是怎样的？查询就会链接几个表，sql语句变得复杂一些，
速度也会慢得多，但是按前面的方式建立sales表，会忽略一个问题，
那就是数据冗余的问题，从物理存储上来说，productName已经存在了
products表里，没必要在sales表里再存储一次，这样就可以，
而这里又存储了一次，那就要占用更多的物理空间，

再看一个表内冗余的情况，
如果sales表的字段是这样的:
timeId，date，custId，proId,storeId，price，
northQuan，northMoney，eastQuan，eastMoney，
westQuan，westMoney，southQuan，southMoney

那如果要查2005年各区域产品的销售情况，就可以用:
select
 (select productName from products where productId=proId) [产品]
 sum(northMoney) as [北部],
 sum(eastMoney) as [东部],
 sum(westMoney) as [西部],
 sum(southMoney) as [南部]
from sales where year(date)=2005
group by proId

上面的表的后面８个字段都是冗余的，
只要用regionId这个字段就足够了，
表内冗余的情况还有一些，理解冗余应从反面去理解，
就是如何去避免冗余，要避免冗余，那凡是可以通过直接或间接
方式得出来的数据，就不要在表里保存，比如通过表链接得到或
通过计算得到等等的数据，就没必要在表里再保存一次了