小女巫 发表于 2015-1-16 14:19:22

sqlserver 数据库 逝世锁

这类操作的执行过程是，1)按照新的表定义建立一个临时表tmpa，2)将原表数据拷贝到临时表，3)将原始表改名tmpb，4)将tmpa改名为原表名，5)将tmpb删除。--逝世锁
/******************************************************************************************************************************************************
逝世锁指两个以上事件互相堵塞互相守候对方开释它们的锁，SQLServer会经由过程回滚个中一个事件并前往一个毛病来自已办理堵塞成绩，让别的事件完成它们的事情。
收拾人：中国风(Roy)
日期:2008.07.20
******************************************************************************************************************************************************/
setnocounton;
ifobject_id(T1)isnotnull
droptableT1
go
createtableT1(IDintprimarykey,Col1int,Col2nvarchar(20))
insertT1select1,101,A
insertT1select2,102,B
insertT1select3,103,C
go
ifobject_id(T2)isnotnull
droptableT2
go
createtableT2(IDintprimarykey,Col1int,Col2nvarchar(20))
insertT2select1,201,X
insertT2select2,202,Y
insertT2select3,203,Z

go
天生表数据:
/*
T1:
IDCol1Col2
------------------------------------------
1101A
2101B
3101C
T2:
IDCol1Col2
------------------------------------------
1201X
2201Y
3201Z
*/
避免逝世锁：
1、起码化堵塞。堵塞越少，产生逝世锁时机越少
2、在事件中按按次会见表（以上例子：逝世锁2）
3、在毛病处置程序中反省毛病1205并在毛病产生时从头提交事件
4、在毛病处置程序中加一个历程将毛病的具体写进日记
5、索引的公道利用(以上例子：逝世锁1、逝世锁3)
当产生逝世锁时，事件主动提交，可经由过程日记来监督逝世锁

逝世锁1(索引):
--毗连窗口1
--1步:
begintran
updatet1setcol2=col2+Awherecol1=101
--3步:
select*fromt2wherecol1=201
committran

--毗连窗口2
--2步:
begintran
updatet2setcol2=col2+Bwherecol1=203
--4步:
select*fromt1wherecol1=103
committran

--毗连窗口1:收到逝世锁毛病,毗连窗口2失掉了局:
/*
讯息1205，层级13，形态51，行3
买卖(处置序辨认码53)在锁定资本上被另外一个处置序逝世锁并已被选择作为活结的就义者。请从头实行该买卖。
*/
--毗连窗口2:失掉了局
/*
------------------------------------------
3103C
*/
处置办法:
--在t1、t2表的col1前提列建索引
createindexIX_t1_col1ont1(col1)
createindexIX_t2_col1ont2(col1)
go
--毗连窗口1
--1步:
begintran
updatet1setcol2=col2+Awherecol1=101
--3步:
select*fromt2with(index=IX_t2_col1)wherecol1=201--因表数据少，只能指定索引提醒才干确保SQLServer利用索引
committran

--毗连窗口2
--2步:
begintran
updatet2setcol2=col2+Bwherecol1=203

--4步:
select*fromt1with(index=IX_t1_col1)wherecol1=103--因表数据少，只能指定索引提醒才干确保SQLServer利用索引
committran

--毗连窗口1:
/*
IDCol1Col2
------------------------------------------
1201X
(1个数据列遭到影响)
*/
--毗连窗口2
/*
IDCol1Col2
------------------------------------------
3103C
(1个数据列遭到影响)
*/

逝世锁2(会见表按次):
--毗连窗口1:
--1步:
begintran
updatet1setcol1=col1+1whereID=1
--3步:
selectcol1fromt2whereID=1
committran

--毗连窗口2:
--2步:
begintran
updatet2setcol1=col1+1whereID=1
--4步
selectcol1fromt1whereID=1
committran

--毗连窗口1:
/*
col1
-----------
201
(1个数据列遭到影响)
*/
--毗连窗口2:
/*
col1
-----------
讯息1205，层级13，形态51，行1
买卖(处置序辨认码54)在锁定资本上被另外一个处置序逝世锁并已被选择作为活结的就义者。请从头实行该买卖。
*/
处置办法:
--改动会见表的按次
--毗连窗口1:
--1步:
begintran
updatet1setcol1=col1+1whereID=1
--3步:
selectcol1fromt2whereID=1
committran
--毗连窗口2:
--2步:
begintran
selectcol1fromt1whereID=1--会守候毗连窗口1提交
--4步
updatet2setcol1=col1+1whereID=1
committran
逝世锁3(单表):
--毗连窗口1:
while1=1
updateT1setcol1=203-col1whereID=2
--毗连窗口2:
declare@invarchar(20)
while1=1
set@i=(selectcol2fromT1with(index=IX_t1_col1)whereCol1=102);--因表数据少，只能指定索引提醒才干确保SQLServer利用索引
--毗连窗口1
/*
讯息1205，层级13，形态51，行4
买卖(处置序辨认码53)在锁定资本上被另外一个处置序逝世锁并已被选择作为活结的就义者。请从头实行该买卖。
*/

处置办法:
1、删除col1上的非会萃索引,如许影响SELECT速率，不成取.
dropindexIX_t1_col1ont1
2、建一个掩盖索引
A、dropindexIX_t1_col1ont1
B、createindexIX_t1_col1_col2ont1(col1,col2)

经由过程SQLServerProfiler查逝世锁信息:
<p>启动SQLServerProfiler——毗连实例——事务拔取局限——显现一切事务
选择项:
TSQL——SQL:StmtStarting
Locks——Dea对于update操作，event中依次记录旧行，新行的值。

飘灵儿 发表于 2015-1-18 12:53:29

是要和操作系统进行Socket通讯的场景。否则建议慎重！

活着的死人 发表于 2015-2-4 13:21:59

换言之，只有在不断的失败中尝试成功，而关于失败的总结却是很少的

精灵巫婆 发表于 2015-2-9 23:36:57

一直以来个人感觉SQLServer的优化器要比Oracle的聪明。SQL2005的更是比2k聪明了不少。（有次作试验发现有的语句在200万级时还比50万级的相同语句要快show_text的一些提示没有找到解释。一直在奇怪。）

若相依 发表于 2015-2-28 08:54:20

还不是性能有问题！否则面向对象的数据库早就实现了！建议使用CLR的地方一般是和应用的复杂程度或操作系统环境有很高的耦合度的场景。如你想构建复杂的算法，并且用到了大量的指针和高级数据模型。

小女巫 发表于 2015-3-9 22:02:03

groupby子句可以将查询结果分组，并返回行的汇总信息Oracle按照groupby子句中指定的表达式的值分组查询结果。

只想知道 发表于 2015-3-17 02:10:08

原来的计算字段其实和虚拟字段很像。只是管理方面好了而已，性能方面提高不多。但是SQL2005提供了计算字段的持久化，这就提高了查询的性能，但是会加重insert和update的负担。OLTP慎用。OLAP可以大规模使用。

第二个灵魂 发表于 2015-3-23 17:22:53

无法深入到数据库系统层面去了解和探究
页: [1]
查看完整版本: sqlserver 数据库 逝世锁