| 订阅 | 在线投稿
分享
 
 
当前位置: 王朝网络 >> oracle >> Oracle、SQL Server zhong ru he suo ding DB he jie suo DB
 

Oracle、SQL Server zhong ru he suo ding DB he jie suo DB

2008-08-07 07:35:02 编辑來源:互联网 评论
 
本文为【Oracle、SQL Server中如何锁定DB和解锁DB】的拼音翻译版
  jin tian you tong shi wen dao guan yu duo ren tong shi geng xin he cao zuo DB shi , ru he fang zhi shu ju chong tu de wen ti , zheng li le yi pian wen dang , sui ran bu shi yuan chuang , dan dui yu li jie shu ju ku de suo ding ,DB jie suo deng dou you yi ding de bang zhu 。
  yi . wei shen me yao yin ru suo
  duo ge yong hu tong shi dui shu ju ku de bing fa cao zuo shi hui dai lai yi xia shu ju bu yi zhi de wen ti :
  diu shi geng xin
  A,B liang ge yong hu du tong yi shu ju bing jin xing xiu gai , qi zhong yi ge yong hu de xiu gai jie guo po huai le ling yi ge xiu gai de jie guo , bi ru ding piao xi tong
  zang du
  A yong hu xiu gai le shu ju , sui hou B yong hu you du chu gai shu ju , dan A yong hu yin wei mou xie yuan yin qu xiao le dui shu ju de xiu gai , shu ju hui fu yuan zhi , ci shi B de dao de shu ju jiu yu shu ju ku nei de shu ju chan sheng le bu yi zhi
  bu ke chong fu du
  A yong hu du qu shu ju , sui hou B yong hu du chu gai shu ju bing xiu gai , ci shi A yong hu zai du qu shu ju shi fa xian qian hou liang ci de zhi bu yi zhi
  bing fa kong zhi de zhu yao fang fa shi feng suo , suo jiu shi zai yi duan shi jian nei jin zhi yong hu zuo mou xie cao zuo yi bi mian chan sheng shu ju bu yi zhi
  er suo de fen lei
  suo de lei bie you liang zhong fen fa :
  1. cong shu ju ku xi tong de jiao du lai kan : fen wei du zhan suo ( ji pai ta suo ), gong xiang suo he geng xin suo
  MS-SQL Server shi yong yi xia zi yuan suo mo shi 。
  suo mo shi miao shu
  gong xiang (S) yong yu bu geng gai huo bu geng xin shu ju de cao zuo ( zhi du cao zuo ), ru SELECT yu ju 。
  geng xin (U) yong yu ke geng xin de zi yuan zhong 。 fang zhi dang duo ge hui hua zai du qu 、 suo ding yi ji sui hou ke neng jin xing de zi yuan geng xin shi fa sheng chang jian xing shi de si suo 。
  pai ta (X) yong yu shu ju xiu gai cao zuo , li ru INSERT、UPDATE huo DELETE。 que bao bu hui tong shi tong yi zi yuan jin xing duo chong geng xin 。
  yi xiang suo yong yu jian li suo de ceng ci jie gou 。 yi xiang suo de lei xing wei : yi xiang gong xiang (IS)、 yi xiang pai ta (IX) yi ji yu yi xiang pai ta gong xiang (SIX)。
  jia gou suo zai zhi xing yi lai yu biao jia gou de cao zuo shi shi yong 。 jia gou suo de lei xing wei : jia gou xiu gai (Sch-M) he jia gou wen ding xing (Sch-S)。
  da rong liang geng xin (BU) xiang biao zhong da rong liang fu zhi shu ju bing zhi ding le TABLOCK ti shi shi shi yong 。
  gong xiang suo
  gong xiang (S) suo yuan xu bing fa shi wu du qu (SELECT) yi ge zi yuan 。 zi yuan shang cun zai gong xiang (S) suo shi , ren he qi ta shi wu dou bu neng xiu gai shu ju 。 yi dan yi jing du qu shu ju , bian li ji shi fang zi yuan shang de gong xiang (S) suo , chu fei jiang shi wu ge li ji bie she zhi wei ke chong fu du huo geng gao ji bie , huo zhe zai shi wu sheng cun zhou qi nei yong suo ding ti shi bao liu gong xiang (S) suo 。
  geng xin suo
  geng xin (U) suo ke yi fang zhi tong chang xing shi de si suo 。 yi ban geng xin mo shi you yi ge shi wu zu cheng , ci shi wu du qu ji lu , huo qu zi yuan ( ye huo xing ) de gong xiang (S) suo , ran hou xiu gai xing , ci cao zuo yao qiu suo zhuan huan wei pai ta (X) suo 。 ru guo liang ge shi wu huo de le zi yuan shang de gong xiang mo shi suo , ran hou shi tu tong shi geng xin shu ju , ze yi ge shi wu chang shi jiang suo zhuan huan wei pai ta (X) suo 。 gong xiang mo shi dao pai ta suo de zhuan huan bi xu deng dai yi duan shi jian , yin wei yi ge shi wu de pai ta suo yu qi ta shi wu de gong xiang mo shi suo bu jian rong ; fa sheng suo deng dai 。 di er ge shi wu shi tu huo qu pai ta (X) suo yi jin xing geng xin 。 you yu liang ge shi wu dou yao zhuan huan wei pai ta (X) suo , bing qie mei ge shi wu dou deng dai ling yi ge shi wu shi fang gong xiang mo shi suo , yin ci fa sheng si suo 。
  ruo yao bi mian zhe zhong qian zai de si suo wen ti , qing shi yong geng xin (U) suo 。 yi ci zhi you yi ge shi wu ke yi huo de zi yuan de geng xin (U) suo 。 ru guo shi wu xiu gai zi yuan , ze geng xin (U) suo zhuan huan wei pai ta (X) suo 。 fou ze , suo zhuan huan wei gong xiang suo 。
  pai ta suo
  pai ta (X) suo ke yi fang zhi bing fa shi wu dui zi yuan jin xing fang wen 。 qi ta shi wu bu neng du qu huo xiu gai pai ta (X) suo suo ding de shu ju 。
  yi xiang suo
  yi xiang suo biao shi SQL Server xu yao zai ceng ci jie gou zhong de mou xie di ceng zi yuan shang huo qu gong xiang (S) suo huo pai ta (X) suo 。 li ru , fang zhi zai biao ji de gong xiang yi xiang suo biao shi shi wu da suan zai biao zhong de ye huo xing shang fang zhi gong xiang (S) suo 。 zai biao ji she zhi yi xiang suo ke fang zhi ling yi ge shi wu sui hou zai bao han na yi ye de biao shang huo qu pai ta (X) suo 。 yi xiang suo ke yi ti gao xing neng , yin wei SQL Server jin zai biao ji jian cha yi xiang suo lai que ding shi wu shi fou ke yi an quan di huo qu gai biao shang de suo 。 er wu xu jian cha biao zhong de mei xing huo mei ye shang de suo yi que ding shi wu shi fou ke yi suo ding zheng ge biao 。
  yi xiang suo bao kuo yi xiang gong xiang (IS)、 yi xiang pai ta (IX) yi ji yu yi xiang pai ta gong xiang (SIX)。
  suo mo shi miao shu
  yi xiang gong xiang (IS) tong guo zai ge zi yuan shang fang zhi S suo , biao ming shi wu de yi xiang shi du qu ceng ci jie gou zhong de bu fen ( er bu shi quan bu ) di ceng zi yuan 。
  yi xiang pai ta (IX) tong guo zai ge zi yuan shang fang zhi X suo , biao ming shi wu de yi xiang shi xiu gai ceng ci jie gou zhong de bu fen ( er bu shi quan bu ) di ceng zi yuan 。IX shi IS de chao ji 。
  yu yi xiang pai ta gong xiang (SIX) tong guo zai ge zi yuan shang fang zhi IX suo , biao ming shi wu de yi xiang shi du qu ceng ci jie gou zhong de quan bu di ceng zi yuan bing xiu gai bu fen ( er bu shi quan bu ) di ceng zi yuan 。 yuan xu ding ceng zi yuan shang de bing fa IS suo 。 li ru , biao de SIX suo zai biao shang fang zhi yi ge SIX suo ( yuan xu bing fa IS suo ), zai dang qian suo xiu gai ye shang fang zhi IX suo ( zai yi xiu gai xing shang fang zhi X suo )。 sui ran mei ge zi yuan zai yi duan shi jian nei zhi neng you yi ge SIX suo , yi fang zhi qi ta shi wu dui zi yuan jin xing geng xin , dan shi qi ta shi wu ke yi tong guo huo qu biao ji de IS suo lai du qu ceng ci jie gou zhong de di ceng zi yuan 。
  du zhan suo : zhi yuan xu jin xing suo ding cao zuo de cheng xu shi yong , qi ta ren he dui ta de cao zuo yun bu hui bei jie shou 。 zhi xing shu ju geng xin ming ling shi ,SQL Server hui zi dong shi yong du zhan suo 。 dang dui xiang shang you qi ta suo cun zai shi , wu fa dui qi jia du zhan suo 。
  gong xiang suo : gong xiang suo suo ding de zi yuan ke yi bei qi ta yong hu du qu , dan qi ta yong hu wu fa xiu gai ta , zai zhi xing Select shi ,SQL Server hui dui dui xiang jia gong xiang suo 。
  geng xin suo : dang SQL Server zhun bei geng xin shu ju shi , ta shou xian dui shu ju dui xiang zuo geng xin suo suo ding , zhe yang shu ju jiang bu neng bei xiu gai , dan ke yi du qu 。 deng dao SQL Server que ding yao jin xing geng xin shu ju cao zuo shi , ta hui zi dong jiang geng xin suo huan wei du zhan suo , dang dui xiang shang you qi ta suo cun zai shi , wu fa dui qi jia geng xin suo 。
  2. cong cheng xu yuan de jiao du kan : fen wei le guan suo he bei guan suo
  le guan suo : wan quan yi kao shu ju ku lai guan li suo de gong zuo 。
  bei guan suo : cheng xu yuan zi ji guan li shu ju huo dui xiang shang de suo chu li 。
  MS-SQLSERVER shi yong suo zai duo ge tong shi zai shu ju ku nei zhi xing xiu gai de yong hu jian shi xian bei guan bing fa kong zhi
  san suo de li du
  suo li du shi bei feng suo mu biao de da xiao , feng suo li du xiao ze bing fa xing gao , dan kai xiao da , feng suo li du da ze bing fa xing di dan kai xiao xiao
  SQL Server zhi chi de suo li du ke yi fen wei wei xing 、 ye 、 jian 、 jian fan wei 、 suo yin 、 biao huo shu ju ku huo qu suo
  zi yuan miao shu
  RID xing biao shi fu 。 yong yu dan du suo ding biao zhong de yi xing 。
  jian suo yin zhong de xing suo 。 yong yu bao hu ke chuan xing shi wu zhong de jian fan wei 。
  ye 8 qian zi jie (KB) de shu ju ye huo suo yin ye 。
  kuo zhan pan qu xiang lin de ba ge shu ju ye huo suo yin ye gou cheng de yi zu 。
  biao bao kuo suo you shu ju he suo yin zai nei de zheng ge biao 。
  DB shu ju ku 。
  si suo ding shi jian de chang duan
  suo bao chi de shi jian chang du wei bao hu suo qing qiu ji bie shang de zi yuan suo xu de shi jian chang du 。
  yong yu bao hu du qu cao zuo de gong xiang suo de bao chi shi jian qu jue yu shi wu ge li ji bie 。 cai yong READ COMMITTED de mo ren shi wu ge li ji bie shi , zhi zai du qu ye de qi jian nei kong zhi gong xiang suo 。 zai sao miao zhong , zhi dao zai sao miao nei de xia yi ye shang huo qu suo shi cai shi fang suo 。 ru guo zhi ding HOLDLOCK ti shi huo zhe jiang shi wu ge li ji bie she zhi wei REPEATABLE READ huo SERIALIZABLE, ze zhi dao shi wu jie shu cai shi fang suo 。
  gen ju wei you biao she zhi de bing fa xuan xiang , you biao ke yi huo qu gong xiang mo shi de gun dong suo yi bao hu ti qu 。 dang xu yao gun dong suo shi , zhi dao xia yi ci ti qu huo guan bi you biao ( yi xian fa sheng zhe wei zhun ) shi cai shi fang gun dong suo 。 dan shi , ru guo zhi ding HOLDLOCK, ze zhi dao shi wu jie shu cai shi fang gun dong suo 。
  yong yu bao hu geng xin de pai ta suo jiang zhi dao shi wu jie shu cai shi fang 。
  ru guo yi ge lian jie shi tu huo qu yi ge suo , er gai suo yu ling yi ge lian jie suo kong zhi de suo chong tu , ze shi tu huo qu suo de lian jie jiang yi zhi zu sai dao :
  jiang chong tu suo shi fang er qie lian jie huo qu le suo qing qiu de suo 。
  lian jie de chao shi jian ge yi dao qi 。 mo ren qing kuang xia mei you chao shi jian ge , dan shi yi xie ying yong cheng xu she zhi chao shi jian ge yi fang zhi wu xian qi deng dai
  wu SQL Server zhong suo de zi ding yi
  1 chu li si suo he she zhi si suo you xian ji
  si suo jiu shi duo ge yong hu shen qing bu tong feng suo , you yu shen qing zhe yun yong you yi bu fen feng suo quan er you deng dai qi ta yong hu yong you de bu fen feng suo er yin qi de wu xiu zhi de deng dai
  ke yi shi yong SET DEADLOCK_PRIORITY kong zhi zai fa sheng si suo qing kuang shi hui hua de fan ying fang shi 。 ru guo liang ge jin cheng dou suo ding shu ju , bing qie zhi dao qi ta jin cheng shi fang zi ji de suo shi , mei ge jin cheng cai neng shi fang zi ji de suo , ji fa sheng si suo qing kuang 。
  2 chu li chao shi he she zhi suo chao shi chi xu shi jian
  @@LOCK_TIMEOUT fan hui dang qian hui hua de dang qian suo chao shi she zhi , dan wei wei hao miao
  SET LOCK_TIMEOUT she zhi yuan xu ying yong cheng xu she zhi yu ju deng dai zu sai zi yuan de zui chang shi jian 。 dang yu ju deng dai de shi jian da yu LOCK_TIMEOUT she zhi shi , xi tong jiang zi dong qu xiao zu sai de yu ju , bing gei ying yong cheng xu fan hui " yi chao guo le suo qing qiu chao shi shi duan " de 1222 hao cuo wu xin xi
  shi li
  xia li jiang suo chao shi qi xian she zhi wei 1,800 hao miao 。
  SET LOCK_TIMEOUT 1800
  3) she zhi shi wu ge li ji bie 。
  4 ) dui SELECT、INSERT、UPDATE he DELETE yu ju shi yong biao ji suo ding ti shi 。
  5) pei zhi suo yin de suo ding li du
  ke yi shi yong sp_indexoption xi tong cun chu guo cheng lai she zhi yong yu suo yin de suo ding li du
  liu cha kan suo de xin xi
  1 zhi xing EXEC SP_LOCK bao gao you guan suo de xin xi
  2 cha xun fen xi qi zhong an Ctrl+2 ke yi kan dao suo de xin xi
  qi shi yong zhu yi shi xiang
  ru he bi mian si suo
  1 shi yong shi wu shi , jin liang suo duan shi wu de luo ji chu li guo cheng , ji zao ti jiao huo hui gun shi wu ;
  2 she zhi si suo chao shi shen shu wei he li fan wei , ru :3 fen zhong -10 fen zhong ; chao guo shi jian , zi dong fang qi ben ci cao zuo , bi mian jin cheng xuan gua ;
  3 you hua cheng xu , jian cha bing bi mian si suo xian xiang chu xian ;
  4 . dui suo you de jiao ben he SP dou yao zai xi ce shi , zai zheng shi ban ben zhi qian 。
  5 suo you de SP dou yao you cuo wu chu li ( tong guo @error)
  6 yi ban bu yao xiu gai SQL SERVER shi wu de mo ren ji bie 。 bu tui jian qiang xing jia suo
  jie jue wen ti ru he dui xing biao shu ju ku jia suo
  ba ji ge you guan suo de wen ti
  1 ru he suo yi ge biao de mou yi xing
  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
  SELECT * FROM table ROWLOCK WHERE id = 1
  2 suo ding shu ju ku de yi ge biao
  SELECT * FROM table WITH (HOLDLOCK)
  jia suo yu ju :
  sybase:
  update biao set col1=col1 where 1=0 ;
  MSSQL:
  select col1 from biao (tablockx) where 1=0 ;
  oracle:
  LOCK TABLE biao IN EXCLUSIVE MODE ;
  jia suo hou qi ta ren bu ke cao zuo , zhi dao jia suo yong hu jie suo , yong commit huo rollback jie suo
  ji ge li zi bang zhu da jia jia shen yin xiang
  she table1(A,B,C)
  A B C
  a1 b1 c1
  a2 b2 c2
  a3 b3 c3
  1) pai ta suo
  xin jian liang ge lian jie
  zai di yi ge lian jie zhong zhi xing yi xia yu ju
  begin tran
  update table1
  set A='aa'
  where B='b2'
  waitfor delay '00:00:30' -- deng dai 30 miao
  commit tran
  zai di er ge lian jie zhong zhi xing yi xia yu ju
  begin tran
  select * from table1
  where B='b2'
  commit tran
  ruo tong shi zhi xing shang shu liang ge yu ju , ze select cha xun bi xu deng dai update zhi xing wan bi cai neng zhi xing ji yao deng dai 30 miao
  2) gong xiang suo
  zai di yi ge lian jie zhong zhi xing yi xia yu ju
  begin tran
  select * from table1 holdlock -holdlock ren wei jia suo
  where B='b2'
  waitfor delay '00:00:30' -- deng dai 30 miao
  commit tran
  zai di er ge lian jie zhong zhi xing yi xia yu ju
  begin tran
  select A,C from table1
  where B='b2'
  update table1
  set A='aa'
  where B='b2'
  commit tran
  ruo tong shi zhi xing shang shu liang ge yu ju , ze di er ge lian jie zhong de select cha xun ke yi zhi xing
  er update bi xu deng dai di yi ge shi wu shi fang gong xiang suo zhuan wei pai ta suo hou cai neng zhi xing ji yao deng dai 30 miao
  3) si suo
  zeng she table2(D,E)
  D E
  d1 e1
  d2 e2
  zai di yi ge lian jie zhong zhi xing yi xia yu ju
  begin tran
  update table1
  set A='aa'
  where B='b2'
  waitfor delay '00:00:30'
  update table2
  set D='d5'
  where E='e1'
  commit tran
  zai di er ge lian jie zhong zhi xing yi xia yu ju
  begin tran
  update table2
  set D='d5'
  where E='e1'
  waitfor delay '00:00:10'
  update table1
  set A='aa'
  where B='b2'
  commit tran
  tong shi zhi xing , xi tong hui jian ce chu si suo , bing zhong zhi jin cheng
  bu chong yi dian :
  Sql Server2000 zhi chi de biao ji suo ding ti shi
  HOLDLOCK chi you gong xiang suo , zhi dao zheng ge shi wu wan cheng , ying gai zai bei suo dui xiang bu xu yao shi li ji shi fang , deng yu SERIALIZABLE shi wu ge li ji bie
  NOLOCK yu ju zhi xing shi bu fa chu gong xiang suo , yuan xu zang du , deng yu READ UNCOMMITTED shi wu ge li ji bie
  PAGLOCK zai shi yong yi ge biao suo de di fang yong duo ge ye suo
  READPAST rang sql server tiao guo ren he suo ding xing , zhi xing shi wu , shi yong yu READ UNCOMMITTED shi wu ge li ji bie zhi tiao guo RID suo , bu tiao guo ye , qu yu he biao suo
  ROWLOCK qiang zhi shi yong xing suo
  TABLOCKX qiang zhi shi yong du zhan biao ji suo , zhe ge suo zai shi wu qi jian zu zhi ren he qi ta shi wu shi yong zhe ge biao
  UPLOCK qiang zhi zai du biao shi shi yong geng xin er bu yong gong xiang suo
  ying yong cheng xu suo :
  ying yong cheng xu suo jiu shi ke hu duan dai ma sheng cheng de suo , er bu shi sql server ben shen sheng cheng de suo
  chu li ying yong cheng xu suo de liang ge guo cheng
  sp_getapplock suo ding ying yong cheng xu zi yuan
  sp_releaseapplock wei ying yong cheng xu zi yuan jie suo
  zhu yi : suo ding shu ju ku de yi ge biao de qu bie
  SELECT * FROM table WITH (HOLDLOCK) qi ta shi wu ke yi du qu biao , dan bu neng geng xin shan chu
  SELECT * FROM table WITH (TABLOCKX) qi ta shi wu bu neng du qu biao , geng xin he shan chu【原文】【汉音对照
 
 
 
 
 
 
 
 
日版宠物情人插曲《Winding Road》歌词

日版宠物情人2017的插曲,很带节奏感,日语的,女生唱的。 最后听见是在第8集的时候女主手割伤了,然后男主用嘴帮她吸了一下,插曲就出来了。 歌手:Def...

兄弟共妻,我成了他们夜里的美食

老钟家的两个儿子很特别,就是跟其他的人不太一样,魔一般的执着。兄弟俩都到了要结婚的年龄了,不管自家老爹怎么磨破嘴皮子,兄弟俩说不娶就不娶,老父母为兄弟两操碎了心...

如何磨出破洞牛仔裤?牛仔裤怎么剪破洞?

把牛仔裤磨出有线的破洞 1、具体工具就是磨脚石,下面垫一个硬物,然后用磨脚石一直磨一直磨,到把那块磨薄了,用手撕开就好了。出来的洞啊很自然的。需要猫须的话调几...

我就是扫描下图得到了敬业福和爱国福

先来看下敬业福和爱国福 今年春节,支付宝再次推出了“五福红包”活动,表示要“把欠大家的敬业福都还给大家”。 今天该活动正式启动,和去年一样,需要收集“五福”...

冰箱异味产生的原因和臭味去除的方法

有时候我们打开冰箱就会闻到一股异味,冰箱里的这种异味是因为一些物质发出的气味的混合体,闻起来让人恶心。 产生这些异味的主要原因有以下几点。 1、很多人有这种习...

 
 
jin tian you tong shi wen dao guan yu duo ren tong shi geng xin he cao zuo DB shi , ru he fang zhi shu ju chong tu de wen ti , zheng li le yi pian wen dang , sui ran bu shi yuan chuang , dan dui yu li jie shu ju ku de suo ding ,DB jie suo deng dou you yi ding de bang zhu 。 yi . wei shen me yao yin ru suo duo ge yong hu tong shi dui shu ju ku de bing fa cao zuo shi hui dai lai yi xia shu ju bu yi zhi de wen ti : diu shi geng xin A,B liang ge yong hu du tong yi shu ju bing jin xing xiu gai , qi zhong yi ge yong hu de xiu gai jie guo po huai le ling yi ge xiu gai de jie guo , bi ru ding piao xi tong zang du A yong hu xiu gai le shu ju , sui hou B yong hu you du chu gai shu ju , dan A yong hu yin wei mou xie yuan yin qu xiao le dui shu ju de xiu gai , shu ju hui fu yuan zhi , ci shi B de dao de shu ju jiu yu shu ju ku nei de shu ju chan sheng le bu yi zhi bu ke chong fu du A yong hu du qu shu ju , sui hou B yong hu du chu gai shu ju bing xiu gai , ci shi A yong hu zai du qu shu ju shi fa xian qian hou liang ci de zhi bu yi zhi bing fa kong zhi de zhu yao fang fa shi feng suo , suo jiu shi zai yi duan shi jian nei jin zhi yong hu zuo mou xie cao zuo yi bi mian chan sheng shu ju bu yi zhi er suo de fen lei suo de lei bie you liang zhong fen fa : 1. cong shu ju ku xi tong de jiao du lai kan : fen wei du zhan suo ( ji pai ta suo ), gong xiang suo he geng xin suo MS-SQL Server shi yong yi xia zi yuan suo mo shi 。 suo mo shi miao shu gong xiang (S) yong yu bu geng gai huo bu geng xin shu ju de cao zuo ( zhi du cao zuo ), ru SELECT yu ju 。 geng xin (U) yong yu ke geng xin de zi yuan zhong 。 fang zhi dang duo ge hui hua zai du qu 、 suo ding yi ji sui hou ke neng jin xing de zi yuan geng xin shi fa sheng chang jian xing shi de si suo 。 pai ta (X) yong yu shu ju xiu gai cao zuo , li ru INSERT、UPDATE huo DELETE。 que bao bu hui tong shi tong yi zi yuan jin xing duo chong geng xin 。 yi xiang suo yong yu jian li suo de ceng ci jie gou 。 yi xiang suo de lei xing wei : yi xiang gong xiang (IS)、 yi xiang pai ta (IX) yi ji yu yi xiang pai ta gong xiang (SIX)。 jia gou suo zai zhi xing yi lai yu biao jia gou de cao zuo shi shi yong 。 jia gou suo de lei xing wei : jia gou xiu gai (Sch-M) he jia gou wen ding xing (Sch-S)。 da rong liang geng xin (BU) xiang biao zhong da rong liang fu zhi shu ju bing zhi ding le TABLOCK ti shi shi shi yong 。 gong xiang suo gong xiang (S) suo yuan xu bing fa shi wu du qu (SELECT) yi ge zi yuan 。 zi yuan shang cun zai gong xiang (S) suo shi , ren he qi ta shi wu dou bu neng xiu gai shu ju 。 yi dan yi jing du qu shu ju , bian li ji shi fang zi yuan shang de gong xiang (S) suo , chu fei jiang shi wu ge li ji bie she zhi wei ke chong fu du huo geng gao ji bie , huo zhe zai shi wu sheng cun zhou qi nei yong suo ding ti shi bao liu gong xiang (S) suo 。 geng xin suo geng xin (U) suo ke yi fang zhi tong chang xing shi de si suo 。 yi ban geng xin mo shi you yi ge shi wu zu cheng , ci shi wu du qu ji lu , huo qu zi yuan ( ye huo xing ) de gong xiang (S) suo , ran hou xiu gai xing , ci cao zuo yao qiu suo zhuan huan wei pai ta (X) suo 。 ru guo liang ge shi wu huo de le zi yuan shang de gong xiang mo shi suo , ran hou shi tu tong shi geng xin shu ju , ze yi ge shi wu chang shi jiang suo zhuan huan wei pai ta (X) suo 。 gong xiang mo shi dao pai ta suo de zhuan huan bi xu deng dai yi duan shi jian , yin wei yi ge shi wu de pai ta suo yu qi ta shi wu de gong xiang mo shi suo bu jian rong ; fa sheng suo deng dai 。 di er ge shi wu shi tu huo qu pai ta (X) suo yi jin xing geng xin 。 you yu liang ge shi wu dou yao zhuan huan wei pai ta (X) suo , bing qie mei ge shi wu dou deng dai ling yi ge shi wu shi fang gong xiang mo shi suo , yin ci fa sheng si suo 。 ruo yao bi mian zhe zhong qian zai de si suo wen ti , qing shi yong geng xin (U) suo 。 yi ci zhi you yi ge shi wu ke yi huo de zi yuan de geng xin (U) suo 。 ru guo shi wu xiu gai zi yuan , ze geng xin (U) suo zhuan huan wei pai ta (X) suo 。 fou ze , suo zhuan huan wei gong xiang suo 。 pai ta suo pai ta (X) suo ke yi fang zhi bing fa shi wu dui zi yuan jin xing fang wen 。 qi ta shi wu bu neng du qu huo xiu gai pai ta (X) suo suo ding de shu ju 。 yi xiang suo yi xiang suo biao shi SQL Server xu yao zai ceng ci jie gou zhong de mou xie di ceng zi yuan shang huo qu gong xiang (S) suo huo pai ta (X) suo 。 li ru , fang zhi zai biao ji de gong xiang yi xiang suo biao shi shi wu da suan zai biao zhong de ye huo xing shang fang zhi gong xiang (S) suo 。 zai biao ji she zhi yi xiang suo ke fang zhi ling yi ge shi wu sui hou zai bao han na yi ye de biao shang huo qu pai ta (X) suo 。 yi xiang suo ke yi ti gao xing neng , yin wei SQL Server jin zai biao ji jian cha yi xiang suo lai que ding shi wu shi fou ke yi an quan di huo qu gai biao shang de suo 。 er wu xu jian cha biao zhong de mei xing huo mei ye shang de suo yi que ding shi wu shi fou ke yi suo ding zheng ge biao 。 yi xiang suo bao kuo yi xiang gong xiang (IS)、 yi xiang pai ta (IX) yi ji yu yi xiang pai ta gong xiang (SIX)。 suo mo shi miao shu yi xiang gong xiang (IS) tong guo zai ge zi yuan shang fang zhi S suo , biao ming shi wu de yi xiang shi du qu ceng ci jie gou zhong de bu fen ( er bu shi quan bu ) di ceng zi yuan 。 yi xiang pai ta (IX) tong guo zai ge zi yuan shang fang zhi X suo , biao ming shi wu de yi xiang shi xiu gai ceng ci jie gou zhong de bu fen ( er bu shi quan bu ) di ceng zi yuan 。IX shi IS de chao ji 。 yu yi xiang pai ta gong xiang (SIX) tong guo zai ge zi yuan shang fang zhi IX suo , biao ming shi wu de yi xiang shi du qu ceng ci jie gou zhong de quan bu di ceng zi yuan bing xiu gai bu fen ( er bu shi quan bu ) di ceng zi yuan 。 yuan xu ding ceng zi yuan shang de bing fa IS suo 。 li ru , biao de SIX suo zai biao shang fang zhi yi ge SIX suo ( yuan xu bing fa IS suo ), zai dang qian suo xiu gai ye shang fang zhi IX suo ( zai yi xiu gai xing shang fang zhi X suo )。 sui ran mei ge zi yuan zai yi duan shi jian nei zhi neng you yi ge SIX suo , yi fang zhi qi ta shi wu dui zi yuan jin xing geng xin , dan shi qi ta shi wu ke yi tong guo huo qu biao ji de IS suo lai du qu ceng ci jie gou zhong de di ceng zi yuan 。 du zhan suo : zhi yuan xu jin xing suo ding cao zuo de cheng xu shi yong , qi ta ren he dui ta de cao zuo yun bu hui bei jie shou 。 zhi xing shu ju geng xin ming ling shi ,SQL Server hui zi dong shi yong du zhan suo 。 dang dui xiang shang you qi ta suo cun zai shi , wu fa dui qi jia du zhan suo 。 gong xiang suo : gong xiang suo suo ding de zi yuan ke yi bei qi ta yong hu du qu , dan qi ta yong hu wu fa xiu gai ta , zai zhi xing Select shi ,SQL Server hui dui dui xiang jia gong xiang suo 。 geng xin suo : dang SQL Server zhun bei geng xin shu ju shi , ta shou xian dui shu ju dui xiang zuo geng xin suo suo ding , zhe yang shu ju jiang bu neng bei xiu gai , dan ke yi du qu 。 deng dao SQL Server que ding yao jin xing geng xin shu ju cao zuo shi , ta hui zi dong jiang geng xin suo huan wei du zhan suo , dang dui xiang shang you qi ta suo cun zai shi , wu fa dui qi jia geng xin suo 。 2. cong cheng xu yuan de jiao du kan : fen wei le guan suo he bei guan suo le guan suo : wan quan yi kao shu ju ku lai guan li suo de gong zuo 。 bei guan suo : cheng xu yuan zi ji guan li shu ju huo dui xiang shang de suo chu li 。 MS-SQLSERVER shi yong suo zai duo ge tong shi zai shu ju ku nei zhi xing xiu gai de yong hu jian shi xian bei guan bing fa kong zhi san suo de li du suo li du shi bei feng suo mu biao de da xiao , feng suo li du xiao ze bing fa xing gao , dan kai xiao da , feng suo li du da ze bing fa xing di dan kai xiao xiao SQL Server zhi chi de suo li du ke yi fen wei wei xing 、 ye 、 jian 、 jian fan wei 、 suo yin 、 biao huo shu ju ku huo qu suo zi yuan miao shu RID xing biao shi fu 。 yong yu dan du suo ding biao zhong de yi xing 。 jian suo yin zhong de xing suo 。 yong yu bao hu ke chuan xing shi wu zhong de jian fan wei 。 ye 8 qian zi jie (KB) de shu ju ye huo suo yin ye 。 kuo zhan pan qu xiang lin de ba ge shu ju ye huo suo yin ye gou cheng de yi zu 。 biao bao kuo suo you shu ju he suo yin zai nei de zheng ge biao 。 DB shu ju ku 。 si suo ding shi jian de chang duan suo bao chi de shi jian chang du wei bao hu suo qing qiu ji bie shang de zi yuan suo xu de shi jian chang du 。 yong yu bao hu du qu cao zuo de gong xiang suo de bao chi shi jian qu jue yu shi wu ge li ji bie 。 cai yong READ COMMITTED de mo ren shi wu ge li ji bie shi , zhi zai du qu ye de qi jian nei kong zhi gong xiang suo 。 zai sao miao zhong , zhi dao zai sao miao nei de xia yi ye shang huo qu suo shi cai shi fang suo 。 ru guo zhi ding HOLDLOCK ti shi huo zhe jiang shi wu ge li ji bie she zhi wei REPEATABLE READ huo SERIALIZABLE, ze zhi dao shi wu jie shu cai shi fang suo 。 gen ju wei you biao she zhi de bing fa xuan xiang , you biao ke yi huo qu gong xiang mo shi de gun dong suo yi bao hu ti qu 。 dang xu yao gun dong suo shi , zhi dao xia yi ci ti qu huo guan bi you biao ( yi xian fa sheng zhe wei zhun ) shi cai shi fang gun dong suo 。 dan shi , ru guo zhi ding HOLDLOCK, ze zhi dao shi wu jie shu cai shi fang gun dong suo 。 yong yu bao hu geng xin de pai ta suo jiang zhi dao shi wu jie shu cai shi fang 。 ru guo yi ge lian jie shi tu huo qu yi ge suo , er gai suo yu ling yi ge lian jie suo kong zhi de suo chong tu , ze shi tu huo qu suo de lian jie jiang yi zhi zu sai dao : jiang chong tu suo shi fang er qie lian jie huo qu le suo qing qiu de suo 。 lian jie de chao shi jian ge yi dao qi 。 mo ren qing kuang xia mei you chao shi jian ge , dan shi yi xie ying yong cheng xu she zhi chao shi jian ge yi fang zhi wu xian qi deng dai wu SQL Server zhong suo de zi ding yi 1 chu li si suo he she zhi si suo you xian ji si suo jiu shi duo ge yong hu shen qing bu tong feng suo , you yu shen qing zhe yun yong you yi bu fen feng suo quan er you deng dai qi ta yong hu yong you de bu fen feng suo er yin qi de wu xiu zhi de deng dai ke yi shi yong SET DEADLOCK_PRIORITY kong zhi zai fa sheng si suo qing kuang shi hui hua de fan ying fang shi 。 ru guo liang ge jin cheng dou suo ding shu ju , bing qie zhi dao qi ta jin cheng shi fang zi ji de suo shi , mei ge jin cheng cai neng shi fang zi ji de suo , ji fa sheng si suo qing kuang 。 2 chu li chao shi he she zhi suo chao shi chi xu shi jian @@LOCK_TIMEOUT fan hui dang qian hui hua de dang qian suo chao shi she zhi , dan wei wei hao miao SET LOCK_TIMEOUT she zhi yuan xu ying yong cheng xu she zhi yu ju deng dai zu sai zi yuan de zui chang shi jian 。 dang yu ju deng dai de shi jian da yu LOCK_TIMEOUT she zhi shi , xi tong jiang zi dong qu xiao zu sai de yu ju , bing gei ying yong cheng xu fan hui " yi chao guo le suo qing qiu chao shi shi duan " de 1222 hao cuo wu xin xi shi li xia li jiang suo chao shi qi xian she zhi wei 1,800 hao miao 。 SET LOCK_TIMEOUT 1800 3) she zhi shi wu ge li ji bie 。 4 ) dui SELECT、INSERT、UPDATE he DELETE yu ju shi yong biao ji suo ding ti shi 。 5) pei zhi suo yin de suo ding li du ke yi shi yong sp_indexoption xi tong cun chu guo cheng lai she zhi yong yu suo yin de suo ding li du liu cha kan suo de xin xi 1 zhi xing EXEC SP_LOCK bao gao you guan suo de xin xi 2 cha xun fen xi qi zhong an Ctrl+2 ke yi kan dao suo de xin xi qi shi yong zhu yi shi xiang ru he bi mian si suo 1 shi yong shi wu shi , jin liang suo duan shi wu de luo ji chu li guo cheng , ji zao ti jiao huo hui gun shi wu ; 2 she zhi si suo chao shi shen shu wei he li fan wei , ru :3 fen zhong -10 fen zhong ; chao guo shi jian , zi dong fang qi ben ci cao zuo , bi mian jin cheng xuan gua ; 3 you hua cheng xu , jian cha bing bi mian si suo xian xiang chu xian ; 4 . dui suo you de jiao ben he SP dou yao zai xi ce shi , zai zheng shi ban ben zhi qian 。 5 suo you de SP dou yao you cuo wu chu li ( tong guo @error) 6 yi ban bu yao xiu gai SQL SERVER shi wu de mo ren ji bie 。 bu tui jian qiang xing jia suo jie jue wen ti ru he dui xing biao shu ju ku jia suo ba ji ge you guan suo de wen ti 1 ru he suo yi ge biao de mou yi xing SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT * FROM table ROWLOCK WHERE id = 1 2 suo ding shu ju ku de yi ge biao SELECT * FROM table WITH (HOLDLOCK) jia suo yu ju : sybase: update biao set col1=col1 where 1=0 ; MSSQL: select col1 from biao (tablockx) where 1=0 ; oracle: LOCK TABLE biao IN EXCLUSIVE MODE ; jia suo hou qi ta ren bu ke cao zuo , zhi dao jia suo yong hu jie suo , yong commit huo rollback jie suo ji ge li zi bang zhu da jia jia shen yin xiang she table1(A,B,C) A B C a1 b1 c1 a2 b2 c2 a3 b3 c3 1) pai ta suo xin jian liang ge lian jie zai di yi ge lian jie zhong zhi xing yi xia yu ju begin tran update table1 set A='aa' where B='b2' waitfor delay '00:00:30' -- deng dai 30 miao commit tran zai di er ge lian jie zhong zhi xing yi xia yu ju begin tran select * from table1 where B='b2' commit tran ruo tong shi zhi xing shang shu liang ge yu ju , ze select cha xun bi xu deng dai update zhi xing wan bi cai neng zhi xing ji yao deng dai 30 miao 2) gong xiang suo zai di yi ge lian jie zhong zhi xing yi xia yu ju begin tran select * from table1 holdlock -holdlock ren wei jia suo where B='b2' waitfor delay '00:00:30' -- deng dai 30 miao commit tran zai di er ge lian jie zhong zhi xing yi xia yu ju begin tran select A,C from table1 where B='b2' update table1 set A='aa' where B='b2' commit tran ruo tong shi zhi xing shang shu liang ge yu ju , ze di er ge lian jie zhong de select cha xun ke yi zhi xing er update bi xu deng dai di yi ge shi wu shi fang gong xiang suo zhuan wei pai ta suo hou cai neng zhi xing ji yao deng dai 30 miao 3) si suo zeng she table2(D,E) D E d1 e1 d2 e2 zai di yi ge lian jie zhong zhi xing yi xia yu ju begin tran update table1 set A='aa' where B='b2' waitfor delay '00:00:30' update table2 set D='d5' where E='e1' commit tran zai di er ge lian jie zhong zhi xing yi xia yu ju begin tran update table2 set D='d5' where E='e1' waitfor delay '00:00:10' update table1 set A='aa' where B='b2' commit tran tong shi zhi xing , xi tong hui jian ce chu si suo , bing zhong zhi jin cheng bu chong yi dian : Sql Server2000 zhi chi de biao ji suo ding ti shi HOLDLOCK chi you gong xiang suo , zhi dao zheng ge shi wu wan cheng , ying gai zai bei suo dui xiang bu xu yao shi li ji shi fang , deng yu SERIALIZABLE shi wu ge li ji bie NOLOCK yu ju zhi xing shi bu fa chu gong xiang suo , yuan xu zang du , deng yu READ UNCOMMITTED shi wu ge li ji bie PAGLOCK zai shi yong yi ge biao suo de di fang yong duo ge ye suo READPAST rang sql server tiao guo ren he suo ding xing , zhi xing shi wu , shi yong yu READ UNCOMMITTED shi wu ge li ji bie zhi tiao guo RID suo , bu tiao guo ye , qu yu he biao suo ROWLOCK qiang zhi shi yong xing suo TABLOCKX qiang zhi shi yong du zhan biao ji suo , zhe ge suo zai shi wu qi jian zu zhi ren he qi ta shi wu shi yong zhe ge biao UPLOCK qiang zhi zai du biao shi shi yong geng xin er bu yong gong xiang suo ying yong cheng xu suo : ying yong cheng xu suo jiu shi ke hu duan dai ma sheng cheng de suo , er bu shi sql server ben shen sheng cheng de suo chu li ying yong cheng xu suo de liang ge guo cheng sp_getapplock suo ding ying yong cheng xu zi yuan sp_releaseapplock wei ying yong cheng xu zi yuan jie suo zhu yi : suo ding shu ju ku de yi ge biao de qu bie SELECT * FROM table WITH (HOLDLOCK) qi ta shi wu ke yi du qu biao , dan bu neng geng xin shan chu SELECT * FROM table WITH (TABLOCKX) qi ta shi wu bu neng du qu biao , geng xin he shan chu
󰈣󰈤
  免责声明:本文仅代表作者个人观点,与王朝网络无关。王朝网络登载此文出于传递更多信息之目的,并不意味着赞同其观点或证实其描述,其原创性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
我们约会吧
漂亮白皙的模特
好纯好美的女生
靓丽的网络美女
痞子的甘南日记
疑是银河落九天
雪域坝上四——纯美色
冬日恋歌——西城杨柳弄轻柔
 
>>返回首页<<
 为你推荐
 
 
 转载本文
 UBB代码 HTML代码
复制到剪贴板...
 
 热帖排行
 
 
 
 
©2005- 王朝网络 版权所有