½²½âMSSQLÊý¾Ý¿âÖÐSQLËø»úÖƺÍÊÂÎñ¸ôÀ뼶±ð
Ëø»úÖÆ
NOLOCKºÍREADPASTµÄÇø±ð¡£
1. ¿ªÆôÒ»¸öÊÂÎñÖ´ÐвåÈëÊý¾ÝµÄ²Ù×÷¡£
BEGIN TRAN t
INSERT INTO Customer
SELECT 'a','a'
2. Ö´ÐÐÒ»Ìõ²éѯÓï¾ä¡£
SELECT * from Customer WITH (NOLOCK)
½á¹ûÖÐÏÔʾ"a"ºÍ"a"¡£µ±1ÖÐÊÂÎñ»Ø¹öºó£¬ÄÇôa½«³ÉΪÔàÊý¾Ý¡£(×¢:1ÖеÄÊÂÎñδÌá½») ¡£NOLOCK±íÃ÷ûÓжÔÊý¾Ý±íÌí¼Ó¹²ÏíËøÒÔ×èÖ¹ÆäËüÊÂÎñ¶ÔÊý¾Ý±íÊý¾ÝµÄÐ޸ġ£
SELECT * from Customer
ÕâÌõÓï¾ä½«Ò»Ö±ËÀËø£¬Ö±µ½ÅÅËûËø½â³ý»òÕßËø³¬Ê±ÎªÖ¹¡£(×¢:ÉèÖÃËø³¬Ê±SET LOCK_TIMEOUT 1800)
SELECT * from Customer WITH (READPAST)
ÕâÌõÓï¾ä½«ÏÔʾaδÌύǰµÄ״̬£¬µ«²»Ëø¶¨Õû¸ö±í¡£Õâ¸öÌáʾָÃ÷Êý¾Ý¿âÒýÇæ·µ»Ø½á¹ûʱºöÂÔ¼ÓËøµÄÐлòÊý¾ÝÒ³¡£
3. Ö´ÐÐÒ»Ìõ²åÈëÓï¾ä¡£
BEGIN TRAN t
INSERT INTO Customer
SELECT 'b','b'
COMMIT TRAN t
Õâ¸öʱºò£¬¼´Ê¹²½Öè1µÄÊÂÎñ»Ø¹ö£¬ÄÇôaÕâÌõÊý¾Ý½«¶ªÊ§£¬¶øb¼ÌÐø²åÈëÊý¾Ý¿âÖС£
NOLOCK
1£® Ö´ÐÐÈçÏÂÓï¾ä¡£
BEGIN TRAN ttt
SELECT * from Customer WITH (NOLOCK)
WAITFOR delay '00:00:20'
COMMIT TRAN ttt
×¢£ºNOLOCK²»¼ÓÈκÎËø£¬¿ÉÒÔÔöɾ²é¸Ä¶ø²»Ëø¶¨¡£
INSERT INTO Customer SELECT 'a','b' –²»Ëø¶¨
DELETE Customer where ID=1 –²»Ëø¶¨
SELECT * from Customer –²»Ëø¶¨
UPDATE Customer SET Title='aa' WHERE ID=1 –²»Ëø¶¨
ROWLOCK
1. Ö´ÐÐÒ»Ìõ´øÐÐËøµÄ²éѯÓï¾ä¡£
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ -- (±ØÐë)
BEGIN TRAN ttt
SELECT * from Customer WITH (ROWLOCK) WHERE ID=17
WAITFOR delay '00:00:20'
COMMIT TRAN ttt
×¢£ºÔÚɾ³ýºÍ¸üÐÂÕýÔÚ²éѯµÄÊý¾Ýʱ£¬»áËø¶¨Êý¾Ý¡£¶ÔÆäËûδ²éѯµÄÐкÍÔö¼Ó£¬²éѯÊý¾ÝÎÞÓ°Ïì¡£
INSERT INTO Customer SELECT 'a','b' –²»µÈ´ý
DELETE Customer where ID=17 –µÈ´ý
DELETE Customer where ID<>17 –²»µÈ´ý
SELECT * from Customer –²»µÈ´ý
UPDATE Customer SET Title='aa' WHERE ID=17–µÈ´ý
UPDATE Customer SET Title='aa' WHERE ID<>17–²»µÈ´ý
HOLDLOCK£¬TABLOCKºÍTABLOCKX
1. Ö´ÐÐHOLDLOCK
BEGIN TRAN ttt
SELECT * from Customer WITH (HOLDLOCK)
WAITFOR delay '00:00:10'
COMMIT TRAN ttt
×¢£ºÆäËûÊÂÎñ¿ÉÒÔ¶ÁÈ¡±í£¬µ«²»ÄܸüÐÂɾ³ý
update Customer set Title='aa' —ÒªµÈ´ý10ÃëÖС£
SEL
Ïà¹ØÎĵµ£º
SQL Server ϵͳȫ¾Ö±äÁ¿
@@CONNECTIONS
·µ»Ø×ÔÉÏ´ÎÆô¶¯ÒÔÀ´Á¬½Ó»òÊÔͼÁ¬½ÓµÄ´ÎÊý¡£
@@CURSOR_ROWS
·µ»ØÁ¬½ÓÉÏ×îºó´ò¿ªµÄÓαêÖе±Ç°´æÔڵĺϸñÐеÄÊýÁ¿(·µ»Ø±»´ò¿ªµÄÓαêÖл¹Î´±»¶ÁÈ¡µÄÓÐЧÊý¾ÝÐеÄÐÐÊý)
@@DATEFIRST
·µ»ØÿÖܵÚÒ»ÌìµÄÊý×Ö
@@ERROR
·µ»Ø×îºóÖ´ÐеÄSQL Óï¾äµÄ´íÎó´úÂë¡£
@@FETCH_STATUS
·µ»Ø ......
Æô¶¯ÓëÅäÖòÎÊý ¸ü¸ÄϵͳԪÊý¾ÝºÍ²ÎÊýÅäÖà ¶¯Ì¬ÊÓͼ ´æ´¢µÄÎïÀíºÍÂß¼½á¹¹ Æô¶¯ºÍ¹Ø±Õ
topÆô¶¯ÓëÅäÖòÎÊý
ÔÚSQL Server°²×°µÄʱºò£¬Í¬Ê±Ò²»áÍùWindows×¢²á±íÀïÃæÌí¼ÓһЩ¼Ç¼£¬ÕâЩע²á±í¼üÖµÖ¸¶¨ÁËʵÀýËùÐèÒªµÄ¸÷ÖÖ²ÎÊý¡£±ÈÈ磬ÓеÄ×¢²á±í¼üÖµÖ¸¶¨ÁË´íÎóÈÕÖ¾µÄ±£´æĿ¼£¬ÁíÍâµÄÖ¸¶¨ÁËĬÈϵı¸·ÝĿ¼µÈµÈ¡£SQL ServerÔÚÔ ......
ʹÓà Microsoft SQL Server °²×°Ïòµ¼µÄ"ʵÀýÃû"Ò³£¬¿ÉÖ¸¶¨´´½¨Ä¬ÈÏʵÀý»¹ÊÇ´´½¨ SQL Server Express ÃüÃûʵÀý¡£³ý·ÇÄúÑ¡ÔñĬÈÏʵÀý£¬·ñÔò SQL Server Express ½«Ê¼ÖÕ°²×°ÃüÃûʵÀý (SQLExpress)¡£´ËÐÐΪÓë SQL Server 2005 ²»Í¬£¬ºóÕßÔÚδѡÔñÃüÃûʵÀýµÄÇé¿öÏ£¬½«Ê¼ÖÕ°²×°Ä¬ÈÏʵÀý¡£
Ñ¡Ïî
Ñ¡Ïî
˵Ã÷
ĬÈÏʵÀý ......