SQL 名词解释
1. 事务
1.1 行级锁
行级锁是针对行来锁定的,比如在事务里,进程A执行了一条update语句:
update student set name='xx' where id=13
则行级锁会锁住student表里id=13的记录,不让别的进程对它操作,
只有等事务完成后才解除锁,举个例子,以 SQL SERVER为例,
同时打开两个查询分析器,在第一个查询分析器里写:
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
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)
代码,可以看到第一个查询分析器一直运行,运行了大概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 里新建一个表:
往往会有提示: 表中允许的最大行大小 8060
比如在SQL SERVER2000 里新建一个表:
create table Test(Fld1 char(5000),Fld2 char(5000))
会提示建立失败,原因如:
“创建表 'Test' 失败,因为行大小将为 10021(包括内部开销),而该值超过了表中允许的最大行大小 8060。”
“创建表 'Test' 失败,因为行大小将为 10021(包括内部开销),而该值超过了表中允许的最大行大小 8060。”
为什么它会限制行大小为8060呢?因为在 SQLSERVER2000里,一页的
大小为8K,这8K里包括96字节的页头、36字节的其它信息、8060字节的
数据区,而数据就存储在8060字节的数据区里,
一页能存储多少行呢?这要看行的大小了,比如如果行大小为2000,
则一页能存储4行,注意行大小不包括文本和图象字段,
比如数据库northwind的customers表的行大小为 298,
则一页可以存储 27 行
看看行大小计算的问题:
大小为8K,这8K里包括96字节的页头、36字节的其它信息、8060字节的
数据区,而数据就存储在8060字节的数据区里,
一页能存储多少行呢?这要看行的大小了,比如如果行大小为2000,
则一页能存储4行,注意行大小不包括文本和图象字段,
比如数据库northwind的customers表的行大小为 298,
则一页可以存储 27 行
看看行大小计算的问题:
use northwind
alter table customers add xx char(8000)
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是表的其它开销。
-----------------------------------------
警告: 已创建表 '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))
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')
------------------------------------
服务器: 消息 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的内部管理的,用户看不到,页比较抽象,
对于变长的数据类型,页的分配是随数据的变化而变化的,
请参考数据库相关的资料。
可以看到新插入的记录自动排在了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
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'
select * from customers where customerId='WOLZA'
先运行第一个查询分析器,再运行第二个,两个查询分析器都在等待.
注意customerId='WOLZA'是表的最后一条记录
1.4 阻塞
注意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'的行,比如我这里查看有这么一行:
查看阻塞可以用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 死锁
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 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
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 错误的,
输入 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被阻塞
两个分析器都要等待对方,所以就出现死锁,哪个都不能执行,
先是分析器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
打开两个查询分析器,第一个写:
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
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 uncommitted,
就是允许读别的事务未提交的数据,
你看看第二个的运行结果,找到products列,看到products列已经修改了
如果你修改第二个查询分析器代码为:
set transaction isolation level read committed
select * from products where ProductId=1
同样运行,那第二个也要等待了,因为隔离级别是read committed,
只能读提交后的数据,不能读未提交的修改,这样就防止了
占用读,SQLSERVER2000里默认是read committed
说明,占用读也叫脏读,脏读就是修改了但没提交的数据,
在文本编辑器里也有脏读的概念,就是修改了但未保存的数据
1.7 不可重复读
只能读提交后的数据,不能读未提交的修改,这样就防止了
占用读,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
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
use northwind
update region set regionDescription='xx' where regionId=3
依次运行第一个和第二个分析器,第一个分析器等待10秒,第二个
不用等待立即得到结果,第一个分析器运行结果为:
3 Northern
3 xx
不用等待立即得到结果,第一个分析器运行结果为:
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
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
use northwind
update region set regionDescription='yy' where regionId=3
同样依次运行第一个和第二个,看到第一个在等待,
第二个也在等待,第一个分析器运行结果为:
3 xx
3 xx
第二个也在等待,第一个分析器运行结果为:
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 幻像读
第二次我设置为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
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')
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
1 Eastern
2 Western
3 Northern
4 Southern
5 xx
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
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')
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
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')
insert into region values(5,'yy')
因为插入的记录regionId=5,而6<5,不满足锁定的条件,
所以该插入是允许的,如果别的事务插入记录:
insert into region values(7,'yy')
因为7>6,满足条件,所以插入被阻塞,只能等待锁释放才能插入
不过,到底锁定哪些记录,这比较难说,锁做为SQLSERVER的内部管理,
到底是怎么样的不怎么清楚,我试过有些情况不满足的条件的记录也
被阻塞,不过有点是清楚的,那就是满足条件的一定被阻塞 1.8 隔离级别
所以该插入是允许的,如果别的事务插入记录:
insert into region values(7,'yy')
因为7>6,满足条件,所以插入被阻塞,只能等待锁释放才能插入
不过,到底锁定哪些记录,这比较难说,锁做为SQLSERVER的内部管理,
到底是怎么样的不怎么清楚,我试过有些情况不满足的条件的记录也
被阻塞,不过有点是清楚的,那就是满足条件的一定被阻塞 1.8 隔离级别
前面已经说过了,有四种隔离级别:
read uncommitted
read committed
repeatable read
serializable
read uncommitted
read committed
repeatable read
serializable
read committed是默认的隔离级别,
隔离级别对单个用户有用,比如你设置了隔离级别为serializable,
那只对你自己有用,对别的用户不起作用,设置了隔离级别后,
那一直有效,直到用户退出为止,
锁的作用主要是用来保证数据一致性的,
read uncommitted不会在被读的数据上放置锁,所以它执行
的速度是最快的,也不会造成阻塞和死锁,但因为数据一致
性问题,所以往往不采用,当然可以通过别的技术比如增加
rowverision列等来保证数据一致性,但对于复杂的操作,还
是选择事务比较安全,对于事务我经历过一些教训,
比如一次我在VB里保存数据,大致如:
隔离级别对单个用户有用,比如你设置了隔离级别为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
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不一致的情况,
为什么会不一致呢?我不是已经加了事务了吗?
做一个例子,同样打开两个查询分析器,第一个写:
再将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
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')
insert into region values(11,'aa')
依次执行第一个和第二个分析器,
本来我希望第一个分析器里查询出来的是10,可结果却是11
在这里,唯一的方法是指定表锁,如:
insert into region with(TabLock) values(10,'aa')
本来我希望第一个分析器里查询出来的是10,可结果却是11
在这里,唯一的方法是指定表锁,如:
insert into region with(TabLock) values(10,'aa')
只有指定表锁,让别的事务无法操作它,才能保证数据一致,
当然,如果是自动编号,那可以用 @@identity 来获取刚生成的Id号,
比如:
insert into orders(CustomerId) values('ALFKI')
print @@identity
当然,如果是自动编号,那可以用 @@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 显式锁
---------------------------------------------------------------
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. 数据库设计
2.1 冗余数据
冗余数据就是重复的数据,冗余数据出现的情况很多,这里举一种情况,
就是表之间的冗余,假设我在northwind里建立一个sales表,字段如:
timeId,date,year,month,custId,custName,productId,
QuanPerUnit,productName,regionID,regionName,storeId,
storeName,quantity,price,money,
就是表之间的冗余,假设我在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
from sales where year=2005 group by productId
如果我这样建sales表:
timeId,custId,productId,storeId,regionID
quantity,price,money
timeId,custId,productId,storeId,regionID
quantity,price,money
那查询是怎样的?查询就会链接几个表,sql语句变得复杂一些,
速度也会慢得多,但是按前面的方式建立sales表,会忽略一个问题,
那就是数据冗余的问题,从物理存储上来说,productName已经存在了
products表里,没必要在sales表里再存储一次,这样就可以,
而这里又存储了一次,那就要占用更多的物理空间,
速度也会慢得多,但是按前面的方式建立sales表,会忽略一个问题,
那就是数据冗余的问题,从物理存储上来说,productName已经存在了
products表里,没必要在sales表里再存储一次,这样就可以,
而这里又存储了一次,那就要占用更多的物理空间,
再看一个表内冗余的情况,
如果sales表的字段是这样的:
timeId,date,custId,proId,storeId,price,
northQuan,northMoney,eastQuan,eastMoney,
westQuan,westMoney,southQuan,southMoney
如果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
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
上面的表的后面8个字段都是冗余的,
只要用regionId这个字段就足够了,
表内冗余的情况还有一些,理解冗余应从反面去理解,
就是如何去避免冗余,要避免冗余,那凡是可以通过直接或间接
方式得出来的数据,就不要在表里保存,比如通过表链接得到或
通过计算得到等等的数据,就没必要在表里再保存一次了
只要用regionId这个字段就足够了,
表内冗余的情况还有一些,理解冗余应从反面去理解,
就是如何去避免冗余,要避免冗余,那凡是可以通过直接或间接
方式得出来的数据,就不要在表里保存,比如通过表链接得到或
通过计算得到等等的数据,就没必要在表里再保存一次了