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
Óï¾ä£»
CREATE
Table
#Who(spid&nbs
Ïà¹ØÎĵµ£º
select d.code,d.name, sum(w.weight) weight,round(avg(w.price),2) price,sum(w.money) money
from weight_info w left outer join t_dict d on w.productcode=d.code left outer join t_balancecode b on w.balancecode=b.balancecode where 1=1 and w.operdate>TO_TIMESTAMP('2009-11-2 04:12:32.0', ' ......
Student(S#,Sname,Sage,Ssex) ѧÉú±í
Course(C#,Cname,T#) ¿Î³Ì±í
SC(S#,C#,score) ³É¼¨±í
Teacher(T#,Tname) ½Ìʦ±í
ÎÊÌ⣺
1¡¢²éѯ“001”¿Î³Ì±È“002”¿Î³Ì³É¼¨¸ßµÄËùÓÐѧÉúµÄѧºÅ£»
select a.S# from (select s#,score from SC where C#='001') a,(select s#,score
fr ......
Ö÷ÒªÕë¶ÔSQL ServerÊý¾Ý¿âËÀËøÏÖÏóµÄÔ¤·À¼°½â¾ö´ëÊ©½øÐÐÁËÏêϸµÄ½éÉÜ£¬¸ü¶àÄÚÈÝÇë´ó¼Ò²Î¿¼ÏÂÎÄ£º
¡¡¡¡ËÀËøÊÇÖ¸ÔÚij×é×ÊÔ´ÖУ¬Á½¸ö»òÁ½¸öÒÔÉϵÄÏß³ÌÔÚÖ´Ðйý³ÌÖУ¬ÔÚÕù¶áijһ×ÊԴʱ¶øÔì³É»¥ÏàµÈ´ýµÄÏÖÏó£¬ÈôÎÞÍâÁ¦µÄ×÷ÓÃÏÂ,ËüÃǶ¼½«ÎÞ·¨ÍƽøÏÂÈ¥£¬ËÀʱ¾Í¿ÉÄÜ»á²úÉúËÀËø,ÕâЩÓÀÔ¶ÔÚ»¥ÏàµÈ´ýµÄ½ø³Ì³ÆÎªËÀËøÏ̡߳£¼òµ¥µ ......