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
Ïà¹ØÎĵµ£º
create PROCEDURE pagelist
@tablename nvarchar(50),
@fieldname nvarchar(50)='*',
@pagesize int output,--ÿҳÏÔʾ¼Ç¼ÌõÊý
@currentpage int output,--µÚ¼¸Ò³
@orderid nvarchar(50),--Ö÷¼üÅÅÐò
@sort int,--ÅÅÐò·½Ê½£¬1±íʾÉýÐò£¬0±íʾ½µÐòÅÅÁÐ
......
¹¦ÄÜ£º·µ»Ø×Ö·û¡¢¶þ½øÖÆ¡¢Îı¾»òͼÏñ±í´ïʽµÄÒ»²¿·Ö
Óï·¨£ºSUBSTRING ( expression, start, length )
SQL ÖÐµÄ substring º¯ÊýÊÇÓÃÀ´×¥³öÒ»¸öÀ¸Î»×ÊÁÏÖÐµÄÆäÖÐÒ»²¿·Ö¡£Õâ¸öº¯ÊýµÄÃû³ÆÔÚ²»Í¬µÄ×ÊÁÏ¿âÖв»ÍêȫһÑù£º
²ÎÊý£º
expression ×Ö·û´®¡¢¶þ½øÖÆ×Ö·û´®¡¢Î ......
CREATE FUNCTION dbo.UF_GetInvoiceSerials( @bizCode VARCHAR(10))
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @ret AS VARCHAR(1000)
SELECT @ret=Coalesce(@ret + ', ','') +
CASE e.ID
  ......
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', ' ......