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 Öв鿴¸
Ïà¹ØÎĵµ£º
¹¤¾ß£º
Sybase PowerDesigner 12.5
Microsoft SQL Server 2005
µÚÒ»²½£º¸ÅÒªÉè¼Æ
´ò¿ªPowerDesignerÈí¼þ£¬Éè¼Æ“¸ÅÄîÊý¾ÝÄ£ÐÍ”(Conceptual Data Model)£º
ͼ1
Éè¼Æ±í½á¹¹£º
ͼ2
ÔÚÉè¼ÆÊôÐÔ(×Ö¶Î)µÄʱºò£¬Èý¸ö×Öĸ(M¡¢P¡¢D)·Ö±ð±íʾ£º
M£ºÊÇ·ñΪ¿Õ;(√±íʾ²»ÔÊÐíΪ¿Õ)
P£ºÊÇ·ñ뻅 ......
×î½üÓöµ½Ò»¸öÏîÄ¿£¬¿Í»§ÔÚ×ܲ¿Â¼ÈëÐÅÏ¢ºó£¬Êý¾ÝÐèҪͬ²½µ½ÏÂÊôµÄÈô¸É¸ö×ÓϵͳÖС£ÏÖÔÚ×ܲ¿µ¥¶ÀʹÓÃÒ»¸öÊý¾Ý¿â£¬ÏÂÊôµ¥Î»¸÷×ÔʹÓÃ×Ô¼ºµÄÊý¾Ý¿â¡£
¶ÔÓÚÕâ¸öÎÊÌ⣬Ïëµ½Á˼¸¸ö½â¾ö·½°¸£º
1¡¢×Ô¼ºÐ´´úÂë½â¾ö¡£¶¨ÒåºÃ·ÃÎÊÿ¸öÊý¾ ......
1. ¸´ÖƱí½á¹¹
Sql´úÂë
1. select * into B from A where 1=0;
select * into B from A where 1=0;
2.¸´ÖƱí¼Ç¼ ¸´ÖÆijЩ×Ö¶Î
Sql´úÂë
1. insert into B(a, b, c) select d, e, f from A;
insert into B(a, b, c) select d, e, f from A;
¸´Ö ......
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[PE011_Page]
@TableName varchar(50), --±íÃû
@Fields varchar(5000) = '*', --×Ö¶ÎÃû(È«²¿×Ö¶ÎΪ*)
@OrderField varchar(5000), &n ......