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 int, ecid int, status nvarchar(50), loginname nvarchar(50), hostname nvarchar(50), blk int, dbname nvarchar(50), cmd nvarchar(50), request_ID int); CREATE Table #Lock(spid int, dpid int, objid int, indld int, [Type] nvarchar(20), Resource nvarchar(50), Mode nvarchar(10), Status nvarchar(10) ); INSERT INTO #Who EXEC sp_who active --¿´ÄĸöÒýÆðµÄ×èÈû£¬blk INSERT INTO #Lock EXEC sp_lock --¿´ËøסÁËÄǸö×ÊÔ´id£¬objid DECLARE @DBName nvarchar(20); SET @DBName='NameOfDataBase' SELECT #Who.* FROM #Who WHERE dbname=@DBName SELECT #Lock.* FROM #Lock JOIN #Who ON #Who.spid=#Lock.spid AND dbname=@DBName; --×îºó·¢Ë͵½SQL ServerµÄÓï¾ä DECLARE crsr Cursor FOR SELECT blk FROM #Who WHERE dbname=@DBName AND blk<>0; DECLARE @blk int; open crsr; FETCH NEXT FROM crsr INTO @blk; WHILE (@@FETCH_STATUS = 0) BEGIN; dbcc inputbuffer(@blk); FETCH NEXT FROM crsr INTO @blk; END; close crsr; DEALLOCATE crsr; --Ëø¶¨µÄ×ÊÔ´ SELECT #Who.spid,hostname,objid,[type],mode,object_name(objid) as objName FROM #Lock JOIN #Who ON #Who.spid=#Lock.spid AND dbname=@DBName WHERE objid<>0; DROP Table #Who; DROP Table #Lock; (2). ʹÓà SQL Server Profiler ·ÖÎöËÀËø: ½« Deadlock graph ʼþÀàÌí¼Óµ½¸ú×Ù¡£´ËʼþÀàʹÓÃËÀËøÉæ¼°µ½µÄ½ø³ÌºÍ¶ÔÏóµÄ XML Êý¾ÝÌî³ä¸ú×ÙÖÐµÄ TextData Êý¾ÝÁС£SQL Server ʼþ̽²éÆ÷ ¿ÉÒÔ½« XML ÎĵµÌáÈ¡µ½ËÀËø XML (.xdl) ÎļþÖУ¬ÒÔºó¿ÉÔÚ SQL Server Management Studio Öв鿴¸ÃÎļþ¡£ 3. ±ÜÃâËÀËø ÉÏÃæ1ÖÐÁгöÁËËÀËøµÄËĸö±ØÒªÌõ¼þ£¬ÎÒÃÇÖ»ÒªÏë°ì·¨ÆÆÆäÖеÄÈÎÒâÒ»¸ö»ò¶à¸öÌõ¼þ£¬¾Í¿ÉÒÔ±ÜÃâËÀËø·¢Éú£¬Ò»°ãÓÐÒÔϼ¸ÖÖ·½·¨(FROM Sql Server 2005Áª»ú´ÔÊé)£º (1).°´Í¬Ò»Ë³Ðò·ÃÎʶÔÏó¡£(×¢£º±ÜÃâ³öÏÖÑ­»·) (2).±ÜÃâÊÂÎñÖеÄÓû§½»»¥¡£(×¢£º¼õÉÙ³ÖÓÐ×ÊÔ´µÄʱ¼ä£¬½ÏÉÙËø¾ºÕù) (3).±£³ÖÊÂÎñ¼ò¶Ì²¢´¦ÓÚÒ»¸öÅú´¦ÀíÖС£(×¢£ºÍ¬(2)£¬¼õÉÙ³ÖÓÐ×ÊÔ´µÄʱ¼ä) (4).ʹÓýϵ͵ĸôÀ뼶±ð¡£(×¢£ºÊ¹Óýϵ͵ĸôÀ뼶±ð£¨ÀýÈçÒÑÌá½»¶Á£©±ÈʹÓýϸߵĸôÀ뼶±ð£¨ÀýÈç¿ÉÐòÁл¯£©³ÖÓй²ÏíËøµÄʱ¼ä¸ü¶Ì£¬¼õÉÙËø¾ºÕù) (5).ʹÓûùÓÚÐа汾¿ØÖƵĸôÀ뼶±ð£º2005ÖÐÖ§³Ö¿ìÕÕÊÂÎñ¸ôÀëºÍÖ¸¶¨READ_COMMITTED¸ôÀ뼶±ðµÄÊÂÎñʹÓÃÐа汾¿ØÖÆ£¬¿ÉÒÔ½«¶ÁÓëд²Ù×÷Ö®¼ä·¢ÉúµÄËÀËø¼¸ÂʽµÖÁ×îµÍ£º SET ALLOW_SNAPSHOT_ISOLATION ON --ÊÂÎñ¿ÉÒÔÖ¸¶¨ SNAPSHOT ÊÂÎñ¸ôÀ뼶±ð; SET READ_COMMITTED_SNAPSHOT ON --Ö¸¶¨ READ_COMMITTED ¸ôÀ뼶±ðµÄÊÂÎñ½«Ê¹ÓÃÐа汾¿ØÖƶø²»ÊÇËø¶¨¡£Ä¬ÈÏÇé¿öÏÂ(ûÓпªÆô´ËÑ¡ÏûÓмÓwith nolockÌáʾ)£¬SELECTÓï¾ä»á¶ÔÇëÇóµÄ×ÊÔ´¼ÓSËø(¹²ÏíËø)£»¶ø¿ªÆôÁË´ËÑ¡Ïîºó£¬SELECT²»»á¶ÔÇëÇóµÄ×ÊÔ´¼ÓSËø¡£ ×¢Ò⣺ÉèÖà READ_COMMITTED_SNAPSHOT Ñ¡Ïîʱ£¬Êý¾Ý¿âÖÐÖ»ÔÊÐí´æÔÚÖ´ÐÐ ALTER DATABASE ÃüÁîµÄÁ¬½Ó¡£ÔÚ ALTER DATABASE Íê³É֮ǰ£¬Êý¾Ý¿âÖоö²»ÄÜÓÐÆäËû´ò¿ªµÄÁ¬½Ó¡£Êý¾Ý¿â²»±ØÒ»¶¨Òª´¦ÓÚµ¥Óû§Ä£Ê½ÖС£ (6).ʹÓðó¶¨Á¬½Ó¡£(×¢£º°ó¶¨»á»°ÓÐÀûÓÚÔÚͬһ̨·þÎñÆ÷ÉϵĶà¸ö»á»°Ö®¼äЭµ÷²Ù×÷¡£°ó¶¨»á»°ÔÊÐíÒ»¸ö»ò¶à¸ö»á»°¹²ÏíÏàͬµÄÊÂÎñºÍËø(µ«Ã¿¸ö»Ø»°±£ÁôÆä×Ô¼ºµÄÊÂÎñ¸ôÀ뼶±ð)£¬²¢¿ÉÒÔʹÓÃͬһÊý¾Ý£¬¶ø²»»áÓÐËø³åÍ»¡£¿ÉÒÔ´Óͬһ¸öÓ¦ÓóÌÐòÄڵĶà¸ö»á»°Öд´½¨°ó¶¨»á»°£¬Ò²¿ÉÒÔ´Ó°üº¬²»Í¬»á»°µÄ¶à¸öÓ¦ÓóÌÐòÖд´½¨°ó¶¨»á»°¡£ÔÚÒ»¸ö»á»°ÖпªÆôÊÂÎñ(begin tran)ºó£¬µ÷ÓÃexec sp_getbindtoken @Token out;À´È¡µÃToken£¬È»ºó´«ÈëÁíÒ»¸ö»á»°²¢Ö´ÐÐEXEC sp_bindsession @TokenÀ´½øÐаó¶¨(×îºóµÄʾÀýÖÐÑÝʾÁË°ó¶¨Á¬½Ó)¡£ 4. ËÀËø´¦Àí·½·¨£º (1). ¸ù¾Ý2ÖÐÌṩµÄsql£¬²é¿´ÄǸöspid´¦ÓÚwait״̬£¬È»ºóÓÃkill spidÀ´¸Éµô(¼´ÆÆ»µËÀËøµÄµÚËĸö±ØÒªÌõ¼þ:Ñ­»·µÈ´ý)£»µ±È»ÕâÖ»ÊÇÒ»ÖÖÁÙʱ½â¾ö·½°¸£¬ÎÒÃÇ×ܲ»ÄÜÔÚÓöµ½ËÀËø¾ÍÔÚÓû§µÄÉú²ú»·¾³ÉÏÅŲéËÀËø¡¢Kill sp£¬ÎÒÃÇÓ¦¸Ã¿¼ÂÇÈçºÎÈ¥±ÜÃâËÀËø¡£ (2). ʹÓÃSET LOCK_TIMEOUT timeout_period(µ¥Î»ÎªºÁÃë)À´É趨ËøÇëÇó³¬Ê±¡£Ä¬ÈÏÇé¿öÏ£¬Êý¾Ý¿âûÓг¬Ê±ÆÚÏÞ(timeout_periodֵΪ-1£¬¿ÉÒÔÓÃSELECT @@LOCK_TIMEOUTÀ´²é¿´¸ÃÖµ£¬¼´ÎÞÏÞÆڵȴý)¡£µ±ÇëÇóËø³¬¹ýtimeout_periodʱ£¬½«·µ»Ø´íÎó¡£timeout_periodֵΪ0ʱ±íʾ¸ù±¾²»µÈ´ý£¬Ò»Óöµ½Ëø¾Í·µ»ØÏûÏ¢¡£ÉèÖÃËøÇëÇó³¬Ê±£¬ÆÆ»·ÁËËÀËøµÄµÚ¶þ¸ö±ØÒªÌõ¼þ(ÇëÇóÓë±£³ÖÌõ¼þ)¡£ ·þÎñÆ÷: ÏûÏ¢ 1222£¬¼¶±ð 16£¬×´Ì¬ 50£¬ÐÐ 1 Òѳ¬¹ýÁËËøÇëÇó³¬Ê±Ê±¶Î¡£ (3). SQL ServerÄÚ²¿ÓÐÒ»¸öËø¼àÊÓÆ÷Ïß³ÌÖ´ÐÐËÀËø¼ì²é£¬Ëø¼àÊÓÆ÷¶ÔÌض¨Ïß³ÌÆô¶¯ËÀËøËÑË÷ʱ£¬»á±êʶÏß³ÌÕýÔڵȴýµÄ×ÊÔ´£»È»ºó²éÕÒÌض¨×ÊÔ´µÄËùÓÐÕߣ¬²¢µÝ¹éµØ¼ÌÐøÖ´ÐжÔÄÇЩÏ̵߳ÄËÀËøËÑË÷£¬Ö±µ½ÕÒµ½Ò»¸ö¹¹³ÉËÀËøÌõ¼þµÄÑ­»·¡£¼ì²âµ½ËÀËøºó£¬Êý¾Ý¿âÒýÇæ Ñ¡ÔñÔËÐлعö¿ªÏú×îСµÄÊÂÎñµÄ»á»°×÷ΪËÀËøÎþÉüÆ·£¬·µ»Ø1205 ´íÎ󣬻عöËÀËøÎþÉüÆ·µÄÊÂÎñ²¢ÊͷŸÃÊÂÎñ³ÖÓеÄËùÓÐËø£¬Ê¹ÆäËûÏ̵߳ÄÊÂÎñ¿ÉÒÔÇëÇó×ÊÔ´²¢¼ÌÐøÔËÐС£ 5. Á½¸öËÀËøʾÀý¼°½â¾ö·½·¨ 5.1 SQLËÀËø (1). ²âÊÔÓõĻù´¡Êý¾Ý£º CREATE TABLE Lock1(C1 int default(0)); CREATE TABLE Lock2(C1 int default(0)); INSERT INTO Lock1 VALUES(1); INSERT INTO Lock2 VALUES(1); (2). ¿ªÁ½¸ö²éѯ´°¿Ú£¬·Ö±ðÖ´ÐÐÏÂÃæÁ½¶Îsql --Query 1 Begin Tran Update Lock1 Set C1=C1+1; WaitFor Delay '00:01:00'; SELECT * FROM Lock2 Rollback Tran; --Query 2 Begin Tran Update Lock2 Set C1=C1+1; WaitFor Delay '00:01:00'; SELECT * FROM Lock1 Rollback Tran; ÉÏÃæµÄSQLÖÐÓÐÒ»¾äWaitFor Delay '00:01:00'£¬ÓÃÓڵȴý1·ÖÖÓ£¬ÒÔ·½±ã²é¿´ËøµÄÇé¿ö¡£ (3). ²é¿´ËøÇé¿ö ÔÚÖ´ÐÐÉÏÃæµÄWaitForÓï¾äÆڼ䣬ִÐеڶþ½ÚÖÐÌṩµÄÓï¾äÀ´²é¿´ËøÐÅÏ¢£º Query1ÖУ¬³ÖÓÐLock1ÖеÚÒ»ÐÐ(±íÖÐÖ»ÓÐÒ»ÐÐÊý¾Ý)µÄÐÐÅÅËûËø(RID:X)£¬²¢³ÖÓиÃÐÐËùÔÚÒ³µÄÒâÏò¸üÐÂËø(PAG:IX)¡¢¸Ã±íµÄÒâÏò¸üÐÂËø(TAB:IX)£»Query2ÖУ¬³ÖÓÐLock2ÖеÚÒ»ÐÐ(±íÖÐÖ»ÓÐÒ»ÐÐÊý¾Ý)µÄÐÐÅÅËûËø(RID:X)£¬²¢³ÖÓиÃÐÐËùÔÚÒ³µÄÒâÏò¸üÐÂËø(PAG:IX)¡¢¸Ã±íµÄÒâÏò¸üÐÂËø(TAB:IX)£» Ö´ÐÐÍêWaitfor£¬Query1²éѯLock2£¬ÇëÇóÔÚ×ÊÔ´ÉϼÓSËø£¬µ«¸ÃÐÐÒѾ­±»Query2¼ÓÉÏÁËXËø£»Query2²éѯLock1£¬ÇëÇóÔÚ×ÊÔ´ÉϼÓSËø£¬µ«¸ÃÐÐÒѾ­±»Query1¼ÓÉÏÁËXËø£»ÓÚÊÇÁ½¸ö²éѯ³ÖÓÐ×ÊÔ´²¢»¥²»ÏàÈ㬹¹³ÉËÀËø¡£ (4). ½â¾ö°ì·¨ a). SQL Server×Ô¶¯Ñ¡ÔñÒ»ÌõSQL×÷ËÀËøÎþÉüÆ·£ºÔËÐÐÍêÉÏÃæµÄÁ½¸ö²éѯºó£¬ÎÒÃǻᷢÏÖÓÐÒ»ÌõSQLÄÜÕý³£Ö´ÐÐÍê±Ï£¬¶øÁíÒ»¸öSQLÔò±¨ÈçÏ´íÎó£º ·þÎñÆ÷: ÏûÏ¢ 1205£¬¼¶±ð 13£¬×´Ì¬ 50£¬ÐÐ 1 ÊÂÎñ£¨½ø³Ì ID xx£©ÓëÁíÒ»¸ö½ø³ÌÒѱ»ËÀËøÔÚ lock ×ÊÔ´ÉÏ£¬ÇÒ¸ÃÊÂÎñÒѱ»Ñ¡×÷ËÀËøÎþÉüÆ·¡£ÇëÖØÐÂÔËÐиÃÊÂÎñ¡£ Õâ¾ÍÊÇÉÏÃæµÚËĽÚÖнéÉܵÄËø¼àÊÓÆ÷¸É»îÁË¡£ b). °´Í¬Ò»Ë³Ðò·ÃÎʶÔÏ󣺵ߵ¹ÈÎÒâÒ»ÌõSQLÖеÄUpdateÓëSELECTÓï¾äµÄ˳Ðò¡£ÀýÈçÐ޸ĵڶþÌõSQL³ÉÈçÏ£º --Query2 Begin Tran SELECT * FROM Lock1--ÔÚLock1ÉÏÉêÇëSËø WaitFor Delay '00:01:00'; Update Lock2 Set C1=C1+1;--Lock2:RID:X Rollback Tran; µ±È»ÕâÑùÐÞ¸ÄÒ²ÊÇÓдú¼ÛµÄ£¬Õâ»áµ¼ÖµÚÒ»ÌõSQLÖ´ÐÐÍê±Ï֮ǰ£¬µÚ¶þÌõSQLÒ»Ö±´¦ÓÚ×èÈû״̬¡£µ¥¶ÀÖ´ÐÐQuery1»òQuery2ÐèÒªÔ¼1·ÖÖÓ£¬µ«Èç¹û¿ªÊ¼Ö´ÐÐQuery1ʱ£¬ÂíÉÏͬʱִÐÐQuery2£¬ÔòQuery2ÐèÒª2·ÖÖÓ²ÅÄÜÖ´ÐÐÍꣻÕâÖÖ°´Ë³ÐòÇëÇó×ÊÔ´´ÓÒ»¶¨³Ì¶ÈÉϽµµÍÁ˲¢·¢ÐÔ¡£ c). SELECTÓï¾ä¼ÓWith(NoLock)Ìáʾ£ºÄ¬ÈÏÇé¿öÏÂSELECTÓï¾ä»á¶Ô²éѯµ½µÄ×ÊÔ´¼ÓSËø(¹²ÏíËø)£¬SËøÓëXËø(ÅÅËûËø)²»¼æÈÝ£»µ«¼ÓÉÏWith(NoLock)ºó£¬SELECT²»¶Ô²éѯµ½µÄ×ÊÔ´¼ÓËø(»òÕß¼ÓSch-SËø£¬Sch-SËø¿ÉÒÔÓëÈκÎËø¼æÈÝ)£»´Ó¶ø¿ÉÒÔÊÇÕâÁ½ÌõSQL¿ÉÒÔ²¢·¢µØ·ÃÎÊͬһ×ÊÔ´¡£µ±È»£¬´Ë·½·¨ÊʺϽâ¾ö¶ÁÓëд²¢·¢ËÀËøµÄÇé¿ö£¬µ«¼ÓWith(NoLock)¿ÉÄܻᵼÖÂÔà¶Á¡£ SELECT * FROM Lock2 WITH(NOLock) SELECT * FROM Lock1 WITH(NOLock) d). ʹÓýϵ͵ĸôÀ뼶±ð¡£SQL Server 2000Ö§³ÖËÄÖÖÊÂÎñ´¦Àí¸ôÀ뼶±ð(TIL)£¬·Ö±ðΪ£ºREAD UNCOMMITTED¡¢READ COMMITTED¡¢REPEATABLE READ¡¢SERIALIZABLE£»SQL Server 2005ÖÐÔö¼ÓÁËSNAPSHOT TIL¡£Ä¬ÈÏÇé¿öÏ£¬SQL ServerʹÓÃREAD COMMITTED TIL£¬ÎÒÃÇ¿ÉÒÔÔÚÉÏÃæµÄÁ½ÌõSQLÇ°¶¼¼ÓÉÏÒ»¾äSET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED£¬À´½µµÍTILÒÔ±ÜÃâËÀËø£»ÊÂʵÉÏ£¬ÔËÐÐÔÚREAD UNCOMMITTED TILµÄÊÂÎñ£¬ÆäÖеÄSELECTÓï¾ä²»¶Ô½á¹û×ÊÔ´¼ÓËø»ò¼ÓSch-SËø£¬¶ø²»»á¼ÓSËø£»µ«»¹ÓÐÒ»µãÐèҪעÒâµÄÊÇ£ºREAD UNCOMMITTED TILÔÊÐíÔà¶Á£¬ËäÈ»¼ÓÉÏÁ˽µµÍTILµÄÓï¾äºó£¬ÉÏÃæÁ½ÌõSQLÔÚÖ´Ðйý³ÌÖв»»á±¨´í£¬µ«Ö´Ðнá¹ûÊÇÒ»¸ö·µ»Ø1£¬Ò»¸ö·µ»Ø2£¬¼´¶Áµ½ÁËÔàÊý¾Ý£¬Ò²ÐíÕâ²¢²»ÊÇÎÒÃÇËùÆÚÍûµÄ¡£ e). ÔÚSQLÇ°¼ÓSET LOCK_TIMEOUT timeout_period£¬µ±ÇëÇóËø³¬¹ýÉ趨µÄtimeout_periodʱ¼äºó£¬¾Í»áÖÕÖ¹µ±Ç°SQLµÄÖ´ÐУ¬ÎþÉü×Ô¼º£¬³ÉÈ«±ðÈË¡£ f). ʹÓûùÓÚÐа汾¿ØÖƵĸôÀ뼶±ð(SQL Server 2005Ö§³Ö)£º¿ªÆôÏÂÃæµÄÑ¡Ïîºó£¬SELECT²»»á¶ÔÇëÇóµÄ×ÊÔ´¼ÓSËø£¬²»¼ÓËø»òÕß¼ÓSch-SËø£¬´Ó¶ø½«¶ÁÓëд²Ù×÷Ö®¼ä·¢ÉúµÄËÀËø¼¸ÂʽµÖÁ×îµÍ£»¶øÇÒ²»»á·¢ÉúÔà¶Á¡£°¡ SET ALLOW_SNAPSHOT_ISOLATION ON SET READ_COMMITTED_SNAPSHOT ON g). ʹÓðó¶¨Á¬½Ó(ʹÓ÷½·¨¼ûÏÂÒ»¸öʾÀý¡£) 5.2 ³ÌÐòËÀËø(SQL×èÈû) ¿´Ò»¸öÀý×Ó£ºÒ»¸öµäÐ͵ÄÊý¾Ý¿â²Ù×÷ÊÂÎñËÀËø·ÖÎö£¬°´ÕÕÎÒ×Ô¼ºµÄÀí½â£¬ÎÒ¾õµÃÕâÓ¦¸ÃËãÊÇC#³ÌÐòÖгöÏÖËÀËø£¬¶ø²»ÊÇÊý¾Ý¿âÖеÄËÀËø£»ÏÂÃæµÄ´úÂëÄ£ÄâÁ˸ÃÎÄÖжÔÊý¾Ý¿âµÄ²Ù×÷¹ý³Ì£º //ÂÔÈ¥µÄÎ޹صÄcode SqlConnection conn = new SqlConnection(connectionString); conn.Open(); SqlTransaction tran = conn.BeginTransaction(); string sql1 = "Update Lock1 SET C1=C1+1"; string sql2 = "SELECT * FROM Lock1"; ExecuteNonQuery(tran, sql1); //ʹÓÃÊÂÎñ:ÊÂÎñÖÐLockÁËTable ExecuteNonQuery(null, sql2); //пªÒ»¸öconnectionÀ´¶ÁÈ¡Table public static void ExecuteNonQuery(SqlTransaction tran, string sql) { SqlCommand cmd = new SqlCommand(sql); if (tran != null) { cmd.Connection = tran.Connection; cmd.Transaction = tran; cmd.ExecuteNonQuery(); } else { using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); cmd.Connection = conn; cmd.ExecuteNonQuery(); } } } Ö´Ðе½ExecuteNonQuery(null, sql2)ʱÅ׳öSQLÖ´Ðг¬Ê±µÄÒì³££¬ÏÂͼ´ÓÊý¾Ý¿âµÄ½Ç¶ÈÀ´¿´¸ÃÎÊÌ⣺ ´úÂë´ÓÉÏÍùÏÂÖ´ÐУ¬»á»°1³ÖÓÐÁ˱íLock1µÄXËø£¬ÇÒÊÂÎñûÓнáÊø£¬»Ø»°1¾ÍÒ»Ö±³ÖÓÐXËø²»ÊÍ·Å£»¶ø»á»°2Ö´ÐÐselect²Ù×÷£¬ÇëÇóÔÚ±íLock1ÉϼÓSËø£¬µ«SËøÓëXËøÊDz»¼æÈݵģ¬ËùÒԻػ°2µÄ±»×èÈûµÈ´ý£¬²»ÔڵȴýÖУ¬¾ÍÔڵȴýÖлñµÃ×ÊÔ´£¬¾ÍÔڵȴýÖг¬Ê±¡£¡£¡£´ÓÖÐÎÒÃÇ¿ÉÒÔ¿´µ½£¬ÀïÃ沢ûÓгöÏÖËÀËø£¬¶øÖ»ÊÇSELECT²Ù×÷±»×èÈûÁË¡£Ò²ÕýÒòΪ²»ÊÇÊý¾Ý¿âËÀËø£¬ËùÒÔSQL ServerµÄËø¼àÊÓÆ÷ÎÞ·¨¼ì²âµ½ËÀËø¡£ ÎÒÃÇÔÙ´ÓC#³ÌÐòµÄ½Ç¶ÈÀ´¿´¸ÃÎÊÌ⣺ C#³ÌÐò³ÖÓÐÁ˱íLock1ÉϵÄXËø£¬Í¬Ê±¿ªÁËÁíÒ»¸öSqlConnection»¹ÏëÔڸñíÉÏÇëÇóÒ»°ÑSËø£¬Í¼ÖÐÒѾ­¹¹³ÉÁË»·Â·£»Ì«Ì°ÐÄÁË£¬½á¹û×Ô¼º°Ñ×Ô¼º¸øËøËÀÁË¡£¡£¡£ ËäÈ»Õâ²»ÊÇÒ»¸öÊý¾Ý¿âËÀËø£¬µ«È´ÊÇÒòΪÊý¾Ý¿â×ÊÔ´¶øµ¼ÖµÄËÀËø£¬ÉÏÀýÖÐÌáµ½µÄ½â¾öËÀËøµÄ·½·¨ÔÚÕâÀïÒ²»ù±¾ÊÊÓã¬Ö÷ÒªÊDZÜÃâ¶Á²Ù×÷±»×èÈû£¬½â¾ö·½·¨ÈçÏ£º a). °ÑSELECT·ÅÔÚUpdateÓï¾äÇ°£ºSELECT²»ÔÚÊÂÎñÖУ¬ÇÒÖ´ÐÐÍê±Ï»áÊÍ·ÅSËø£» b). °ÑSELECTÒ²·Å¼ÓÈëµ½ÊÂÎñÖУºExecuteNonQuery(tran, sql2); c). SELECT¼ÓWith(NOLock)Ìáʾ£º¿ÉÄܲúÉúÔà¶Á£» d). ½µµÍÊÂÎñ¸ôÀ뼶±ð£ºSELECTÓï¾äÇ°¼ÓSET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED£»Í¬ÉÏ£¬¿ÉÄܲúÉúÔà¶Á£» e). ʹÓûùÓÚÐа汾¿ØÖƵĸôÀ뼶±ð£¨Í¬ÉÏÀý£©¡£ g). ʹÓðó¶¨Á¬½Ó£ºÈ¡µÃÊÂÎñËùÔڻỰµÄtoken£¬È»ºó´«ÈëпªµÄconnectionÖУ»Ö´ÐÐEXEC sp_bindsession @Tokenºó°ó¶¨ÁËÁ¬½Ó£¬×îºóÖ´ÐÐexec sp_bindsession null;À´È¡Ïû°ó¶¨£»×îºóÐèҪעÒâµÄËĵãÊÇ£º (1). ʹÓÃÁË°ó¶¨Á¬½ÓµÄ¶à¸öconnection¹²Ïíͬһ¸öÊÂÎñºÍÏàͬµÄËø£¬µ«¸÷×Ô±£Áô×Ô¼ºµÄÊÂÎñ¸ôÀ뼶±ð£» (2). Èç¹ûÔÚsql3×Ö·û´®µÄ¡°exec sp_bindsession null¡±»»³É¡°commit tran¡±»òÕß¡°rollback tran¡±£¬Ôò»áÌá½»Õû¸öÊÂÎñ£¬×îºóÒ»ÐÐC#´úÂëtran.Commit()¾Í¿ÉÒÔ²»ÓÃÖ´ÐÐÁË(Ö´Ðлᱨ´í£¬ÒòΪÊÂÎñÒѾ­½áÊøÁË-,-)¡£ (3). ¿ªÆôÊÂÎñ(begin tran)ºó£¬²Å¿ÉÒÔµ÷ÓÃexec sp_getbindtoken @Token outÀ´È¡µÃToken£»Èç¹û²»ÏëÔÙпªµÄconnectionÖнáÊøµôÔ­ÓеÄÊÂÎñ£¬ÔòÔÚÕâ¸öconnection close֮ǰ£¬±ØÐëÖ´ÐС°exec sp_bindsession null¡±À´È¡Ïû°ó¶¨Á¬½Ó£¬»òÕßÔÚпªµÄconnectoin close֮ǰÏȽáÊøµôÊÂÎñ(commit/tran)¡£ (4). (Sql server 2005 Áª»ú´ÔÊé)ºóÐø°æ±¾µÄ Microsoft SQL Server ½«É¾³ý¸Ã¹¦ÄÜ¡£Çë±ÜÃâÔÚеĿª·¢¹¤×÷ÖÐʹÓøù¦ÄÜ£¬²¢×ÅÊÖÐ޸ĵ±Ç°»¹ÔÚʹÓøù¦ÄܵÄÓ¦ÓóÌÐò¡£ Çë¸ÄÓöà¸ö»î¶¯½á¹û¼¯ (MARS) »ò·Ö²¼Ê½ÊÂÎñ¡£ tran = connection.BeginTransaction(); string sql1 = "Update Lock1 SET C1=C1+1"; ExecuteNonQuery(tran, sql1); //ʹÓÃÊÂÎñ:ÊÂÎñÖÐLockÁ˲âÊÔ±íLock1 string sql2 = @"DECLARE @Token varchar(255); exec sp_getbindtoken @Token out; SELECT @Token;"; string token = ExecuteScalar(tran, sql2).ToString(); string sql3 = "EXEC sp_bindsession @Token;Update Lock1 SET C1=C1+1;exec sp_bindsession null;"; SqlParameter parameter = new SqlParameter("@Token", SqlDbType.VarChar); parameter.Value = token; ExecuteNonQuery(null, sql3, parameter); //пªÒ»¸öconnectionÀ´²Ù×÷²âÊÔ±íLock1 tran.Commit(); ¸½£ºËø¼æÈÝÐÔ(FROM SQL Server 2005 Áª»ú´ÔÊé) Ëø¼æÈÝÐÔ¿ØÖƶà¸öÊÂÎñÄÜ·ñͬʱ»ñȡͬһ×ÊÔ´ÉϵÄËø¡£Èç¹û×ÊÔ´Òѱ»ÁíÒ»ÊÂÎñËø¶¨£¬Ôò½öµ±ÇëÇóËøµÄģʽÓëÏÖÓÐËøµÄģʽÏà¼æÈÝʱ£¬²Å»áÊÚÓèеÄËøÇëÇó¡£Èç¹ûÇëÇóËøµÄģʽÓëÏÖÓÐËøµÄģʽ²»¼æÈÝ£¬ÔòÇëÇóÐÂËøµÄÊÂÎñ½«µÈ´ýÊÍ·ÅÏÖÓÐËø»òµÈ´ýËø³¬Ê±¼ä¸ô¹ýÆÚ¡£