SQL ServerËÀËø×ܽá
1. ËÀËøÔÀí
¸ù¾Ý²Ù×÷ϵͳÖеĶ¨Ò壺ËÀËøÊÇÖ¸ÔÚÒ»×é½ø³ÌÖеĸ÷¸ö½ø³Ì¾ùÕ¼Óв»»áÊͷŵÄ×ÊÔ´£¬µ«Òò»¥ÏàÉêÇë±»ÆäËû½ø³ÌËùÕ¾Óò»»áÊͷŵÄ×ÊÔ´¶ø´¦ÓÚµÄÒ»ÖÖÓÀ¾ÃµÈ´ý״̬¡£
ËÀËøµÄËĸö±ØÒªÌõ¼þ£º
»¥³âÌõ¼þ(Mutual exclusion)£º×ÊÔ´²»Äܱ»¹²Ïí£¬Ö»ÄÜÓÉÒ»¸ö½ø³ÌʹÓá£
ÇëÇóÓë±£³ÖÌõ¼þ(Hold and wait)£ºÒѾµÃµ½×ÊÔ´µÄ½ø³Ì¿ÉÒÔÔÙ´ÎÉêÇëеÄ×ÊÔ´¡£
·Ç°þ¶áÌõ¼þ(No pre-emption)£ºÒѾ·ÖÅäµÄ×ÊÔ´²»ÄÜ´ÓÏàÓ¦µÄ½ø³ÌÖб»Ç¿ÖƵذþ¶á¡£
Ñ»·µÈ´ýÌõ¼þ(Circular wait)£ºÏµÍ³ÖÐÈô¸É½ø³Ì×é³É»·Â·£¬¸Ã»·Â·ÖÐÿ¸ö½ø³Ì¶¼ÔڵȴýÏàÁÚ½ø³ÌÕýÕ¼ÓõÄ×ÊÔ´¡£
¶ÔÓ¦µ½SQL ServerÖУ¬µ±ÔÚÁ½¸ö»ò¶à¸öÈÎÎñÖУ¬Èç¹ûÿ¸öÈÎÎñËø¶¨ÁËÆäËûÈÎÎñÊÔͼËø¶¨µÄ×ÊÔ´£¬´Ëʱ»áÔì³ÉÕâЩÈÎÎñÓÀ¾Ã×èÈû£¬´Ó¶ø³öÏÖËÀËø£»ÕâЩ×ÊÔ´¿ÉÄÜÊÇ£ºµ¥ÐÐ(RID£¬¶ÑÖеĵ¥ÐÐ)¡¢Ë÷ÒýÖеļü(KEY£¬ÐÐËø)¡¢Ò³(PAG£¬8KB)¡¢Çø½á¹¹(EXT£¬Á¬ÐøµÄ8Ò³)¡¢¶Ñ»òBÊ÷(HOBT) ¡¢±í(TAB£¬°üÀ¨Êý¾ÝºÍË÷Òý)¡¢Îļþ(File£¬Êý¾Ý¿âÎļþ)¡¢Ó¦ÓóÌÐòרÓÃ×ÊÔ´(APP)¡¢ÔªÊý¾Ý(METADATA)¡¢·ÖÅäµ¥Ôª(Allocation_Unit)¡¢Õû¸öÊý¾Ý¿â(DB)¡£Ò»¸öËÀËøʾÀýÈçÏÂͼËùʾ£º
˵Ã÷£ºT1¡¢T2±íʾÁ½¸öÈÎÎñ£»R1ºÍR2±íʾÁ½¸ö×ÊÔ´£»ÓÉ×ÊÔ´Ö¸ÏòÈÎÎñµÄ¼ýÍ·(ÈçR1->T1£¬R2->T2)±íʾ¸Ã×ÊÔ´±»¸ÄÈÎÎñËù³ÖÓУ»ÓÉÈÎÎñÖ¸Ïò×ÊÔ´µÄ¼ýÍ·(ÈçT1->S2£¬T2->S1)±íʾ¸ÃÈÎÎñÕýÔÚÇëÇó¶ÔӦĿ±ê×ÊÔ´£»
ÆäÂú×ãÉÏÃæËÀËøµÄËĸö±ØÒªÌõ¼þ£º
(1).»¥³â£º×ÊÔ´S1ºÍS2²»Äܱ»¹²Ïí£¬Í¬Ò»Ê±¼äÖ»ÄÜÓÉÒ»¸öÈÎÎñʹÓã»
(2).ÇëÇóÓë±£³ÖÌõ¼þ£ºT1³ÖÓÐS1µÄͬʱ£¬ÇëÇóS2£»T2³ÖÓÐS2µÄͬʱÇëÇóS1£»
(3).·Ç°þ¶áÌõ¼þ£ºT1ÎÞ·¨´ÓT2ÉÏ°þ¶áS2£¬T2Ò²ÎÞ·¨´ÓT1ÉÏ°þ¶áS1£»
(4).Ñ»·µÈ´ýÌõ¼þ£ºÉÏͼÖеļýÍ·¹¹³É»·Â·£¬´æÔÚÑ»·µÈ´ý¡£
2. ËÀËøÅŲé
(1). ʹÓÃSQL ServerµÄϵͳ´æ´¢¹ý³Ìsp_whoºÍsp_lock£¬¿ÉÒԲ鿴µ±Ç°Êý¾Ý¿âÖеÄËøÇé¿ö£»½ø¶ø¸ù¾ÝobjectID(@objID)(SQL Server 2005)/ object_name(@objID)(Sql Server 2000)¿ÉÒԲ鿴Äĸö×ÊÔ´±»Ëø£¬ÓÃdbcc ld(@blk)£¬¿ÉÒԲ鿴×îºóÒ»Ìõ·¢Éú¸øSQL ServerµÄSqlÓï¾ä£»
(2). ʹÓà SQL Server Profiler ·ÖÎöËÀËø: ½« Deadlock graph ʼþÀàÌí¼Óµ½¸ú×Ù¡£´ËʼþÀàʹÓÃËÀËøÉæ¼°µ½µÄ½ø³ÌºÍ¶ÔÏóµÄ XML Êý¾ÝÌî³ä¸ú×ÙÖÐµÄ TextData Êý¾ÝÁС£SQL Server ʼþ̽²éÆ÷ ¿ÉÒÔ½« XML ÎĵµÌáÈ¡µ½ËÀËø XML (.xdl) ÎļþÖУ¬ÒÔºó¿ÉÔÚ SQL Server Management Studio Öв鿴¸ÃÎļþ¡£
CREAT
Ïà¹ØÎĵµ£º
select gztzid,
gztztt,
gztzbt,
gztznr,
fslxmc,
decode(fsfs, '0', 'ÎÞÐè»Ø¸´', '1', 'ÐèÒª»Ø¸´') fsfs,
&nb ......
ιʶø֪У¬¹ûÈ»Èç´Ëѽ£¬µÚ¶þ´ÎÔÙ·¿ªÍ¬ÑùµÄÄÚÈݹûÈ»Óв»Í¬µÄÊÕ»ñ£¬ÓÐЩÊǵÚÒ»´Î¿´µÄʱºòûÓÐ×ÐϸÀí½âµÄ£¬»¹ÓÐЩ¿ÉÄÜÊÇÔÚµÚÒ»´Î¿´´Ò´Ò¾ÍÌø¹ýµÄ£¬µ±È»£¬¿ÉÄÜ»¹Óв¿·ÖÊÇ×Ô¼ºµ±Ê±¼ÇסÁËÍêÁËÓÖ¸øÍü¼ÇÁË¡£½ñÌìµÚ¶þ´Î¿´µ½×Ó³ÌÐòÕâÒ»Õ½ڣ¬·¢ÏÖÁËЩеÄÄÚÈÝ£¬ºÇºÇ¡£ÔÚÕâÀïÎÒ¾ÍдÏÂһЩ»ù±¾ÄÚÈݺÍÈÝÒ×Íü¼ÇµÄ£¬ÃâµÃÏ´ÎÓÖ¸øÍüÁË¡£ÄÚ ......
µ½½ñÌìΪֹ£¬ÈËÃǶԹØϵÊý¾Ý¿â×öÁË´óÁ¿µÄÑо¿£¬²¢¿ª·¢³ö¹ØϵÊý¾ÝÓïÑÔ£¬Îª²Ù×÷¹ØϵÊý¾Ý¿âÌṩÁË·½±ãµÄÓû§½Ó¿Ú¡£¹ØϵÊý¾ÝÓïÑÔÄ¿Ç°Óм¸Ê®ÖÖ£¬¾ßÓÐÔö¼Ó¡¢É¾³ý¡¢Ð޸ġ¢²éѯ¡¢Êý¾Ý¶¨ÒåÓë¿ØÖƵÈÍêÕûµÄÊý¾Ý¿â²Ù×÷¹¦ÄÜ¡£Í¨³£°ÑËüÃÇ·ÖΪÁ½Àࣺ¹Øϵ´úÊýÀàºÍ¹ØϵÑÝËãÀà¡£
ÔÚÕâЩÓïÑÔÖУ¬½á¹¹»¯²éѯÓïÑÔSQLÒÔÆäÇ¿´óµÄÊý¾Ý¿â²Ù× ......
ת×Ôhttp://www.111cn.cn/database/109/b992816b1dddbb641c25c0999883427e.htm
declare @text nvarchar(max);
with tb
as
(
select blocking_session_id,
session_id,db_name(database_id) as dbname,text from master.sys.dm_exec_requests a
CROSS APPLY master.sys.dm_exec_sql_text(a.sql_handle)
),
tb1 a ......
select *
from (select row_number() over(partition by t.type order by date desc) rn,
t.*
from ±íÃû t)
where rn <= 2;
typeÒª·ÖµÄÀà
date ÅÅÐò ......