SQLËø±íÓï¾ä(ת)
Ëø¶¨Êý¾Ý¿âµÄÒ»¸ö±í
SELECT * from table WITH (HOLDLOCK)
×¢Òâ: Ëø¶¨Êý¾Ý¿âµÄÒ»¸ö±íµÄÇø±ð
SELECT * from table WITH (HOLDLOCK)
ÆäËûÊÂÎñ¿ÉÒÔ¶ÁÈ¡±í£¬µ«²»ÄܸüÐÂɾ³ý
SELECT * from table WITH (TABLOCKX)
ÆäËûÊÂÎñ²»ÄܶÁÈ¡±í,¸üкÍɾ³ý
SELECT Óï¾äÖГ¼ÓËøÑ¡ÏŦÄÜ˵Ã÷
SQL ServerÌṩÁËÇ¿´ó¶øÍ걸µÄËø»úÖÆÀ´°ïÖúʵÏÖÊý¾Ý¿âϵͳµÄ²¢·¢ÐԺ͸ßÐÔÄÜ¡£Óû§¼ÈÄÜʹÓÃSQL ServerµÄȱʡÉèÖÃÒ²¿ÉÒÔÔÚselect Óï¾äÖÐʹÓÓ¼ÓËøÑ¡Ïî”À´ÊµÏÖÔ¤ÆÚµÄЧ¹û¡£ ±¾ÎĽéÉÜÁËSELECTÓï¾äÖеĸ÷ÏÓËøÑ¡Ïî”ÒÔ¼°ÏàÓ¦µÄ¹¦ÄÜ˵Ã÷¡£
¹¦ÄÜ˵Ã÷£º¡¡
NOLOCK£¨²»¼ÓËø£©
´ËÑ¡ÏѡÖÐʱ£¬SQL Server ÔÚ¶ÁÈ¡»òÐÞ¸ÄÊý¾Ýʱ²»¼ÓÈκÎËø¡£ ÔÚÕâÖÖÇé¿öÏ£¬Óû§ÓпÉÄܶÁÈ¡µ½Î´Íê³ÉÊÂÎñ£¨Uncommited Transaction£©»ò»Ø¹ö(Roll Back)ÖеÄÊý¾Ý, ¼´ËùνµÄ“ÔàÊý¾Ý”¡£
HOLDLOCK£¨±£³ÖËø£©
´ËÑ¡ÏѡÖÐʱ£¬SQL Server »á½«´Ë¹²ÏíËø±£³ÖÖÁÕû¸öÊÂÎñ½áÊø£¬¶ø²»»áÔÚ;ÖÐÊÍ·Å¡£
UPDLOCK£¨ÐÞ¸ÄËø£©
´ËÑ¡ÏѡÖÐʱ£¬SQL Server ÔÚ¶ÁÈ¡Êý¾ÝʱʹÓÃÐÞ¸ÄËøÀ´´úÌæ¹²ÏíËø£¬²¢½«´ËËø±£³ÖÖÁÕû¸öÊÂÎñ»òÃüÁî½áÊø¡£Ê¹ÓôËÑ¡ÏîÄܹ»±£Ö¤¶à¸ö½ø³ÌÄÜͬʱ¶ÁÈ¡Êý¾Ýµ«Ö»Óиýø³ÌÄÜÐÞ¸ÄÊý¾Ý¡£
TABLOCK£¨±íËø£©
´ËÑ¡ÏѡÖÐʱ£¬SQL Server ½«ÔÚÕû¸ö±íÉÏÖù²ÏíËøÖ±ÖÁ¸ÃÃüÁî½áÊø¡£ Õâ¸öÑ¡Ïî±£Ö¤ÆäËû½ø³ÌÖ»ÄܶÁÈ¡¶ø²»ÄÜÐÞ¸ÄÊý¾Ý¡£
PAGLOCK£¨Ò³Ëø£©
´ËÑ¡ÏîΪĬÈÏÑ¡Ï µ±±»Ñ¡ÖÐʱ£¬SQL Server ʹÓù²ÏíÒ³Ëø¡£
TABLOCKX£¨ÅÅËü±íËø£©
´ËÑ¡ÏѡÖÐʱ£¬SQL Server ½«ÔÚÕû¸ö±íÉÏÖÃÅÅËüËøÖ±ÖÁ¸ÃÃüÁî»òÊÂÎñ½áÊø¡£Õ⽫·ÀÖ¹ÆäËû½ø³Ì¶ÁÈ¡»òÐ޸ıíÖеÄÊý¾Ý¡£
HOLDLOCK ³ÖÓй²ÏíËø£¬Ö±µ½Õû¸öÊÂÎñÍê³É£¬Ó¦¸ÃÔÚ±»Ëø¶ÔÏó²»ÐèҪʱÁ¢¼´ÊÍ·Å£¬µÈÓÚSERIALIZABLEÊÂÎñ¸ôÀ뼶±ð
NOLOCK Óï¾äÖ´ÐÐʱ²»·¢³ö¹²ÏíËø£¬ÔÊÐíÔà¶Á £¬µÈÓÚ READ UNCOMMITTEDÊÂÎñ¸ôÀ뼶±ð
PAGLOCK ÔÚʹÓÃÒ»¸ö±íËøµÄµØ·½Óöà¸öÒ³Ëø
READPAST ÈÃsql serverÌø¹ýÈκÎËø¶¨ÐУ¬Ö´ÐÐÊÂÎñ£¬ÊÊÓÃÓÚREAD UNCOMMITTEDÊÂÎñ¸ôÀ뼶±ðÖ»Ìø¹ýRIDËø£¬²»Ìø¹ýÒ³£¬ÇøÓòºÍ±íËø
ROWLOCK Ç¿ÖÆʹÓÃÐÐËø
TABLOCKX Ç¿ÖÆʹÓöÀÕ¼±í¼¶Ëø£¬Õâ¸öËøÔÚÊÂÎñÆÚ¼ä×èÖ¹ÈκÎÆäËûÊÂÎñʹÓÃÕâ¸ö±í
UPLOCK Ç¿ÖÆÔÚ¶Á±íʱʹÓøüжø²»Óù²ÏíËø
×¢Òâ: Ëø¶¨Êý¾Ý¿âµÄÒ»¸ö±íµÄÇø±ð
SELECT * from table WITH (HOLDLOCK) ÆäËûÊÂÎñ/Óï¾ä¿ÉÒÔ¶ÁÈ¡±í£¬µ«²»ÄܸüÐÂɾ³ý
SELECT * from table WITH (TABLOCKX) ÆäËûÊÂÎñ/Óï¾ä²»ÄܶÁÈ¡±í,¸üкÍɾ³ý
À
Ïà¹ØÎĵµ£º
SQL Server 2008ÖеıíÖµÐͲÎÊý
×÷ÕߣºAl Tenhundfeld ÒëÕß Õź£Áú¡¡
±íÖµÐͲÎÊý£¨Table-valued parameters£©ÊÇSQL Server 2008ÖÐÒýÈëµÄÒ»ÖÖÐÂÌØÐÔ£¬ËüÌṩÁËÒ»ÖÖÄÚÖõķ½Ê½£¬Èÿͻ§¶ËÓ¦ÓÿÉÒÔֻͨ¹ýµ¥¶ÀµÄÒ»Ìõ²Î»¯ÊýSQLÓï¾ä£¬¾Í¿ÉÒÔÏòSQL Server·¢ËͶàÐÐÊý¾Ý¡£
±íÖ ......
´Ó²©¿ÍÔ°Öп´µ½Ò»ÆªÎÄÕ£¬½éÉÜ´óÈí¼þ¹«Ë¾ÃæÊÔʱ³£³£»á³öµÄÁ½µÀSQLÌ⣨¼û¸½Â¼£©¡£
ÎÒ¾õµÃÊÜÒæºÜ¶à£¬ÔÚ´Ë֮ǰ£¬ÎÒÒ»Ö±¾õµÃ£¬SQL2008ËƺõÌṩÁËÕâ·½ÃæµÄÖ§³Ö£¬µ«¸üµÍµÄ°æ±¾£¬°üÀ¨2005£¬·ÇÓαê×ö²»³öÀ´£¨Ë®Æ½¹»²Ë£©¡£×ܽáÐĵÃÈçÏ£º
1¡¢ Ç¿´óµÄgroup by
1 select stdname,
2 isnull( ......
±¾ÎÄÀ´×Ô£ºhttp://niunan.javaeye.com/blog/264197
±È½ÏÍòÄܵķÖÒ³£º
select top ÿҳÏÔʾµÄ¼Ç¼Êý * from topic where id not in
(select top £¨µ±Ç°µÄÒ³Êý-1£©×ÿҳÏÔʾµÄ¼Ç¼Êý id from topic order by id& ......
--------------------------------------------------------------------------
-- Author : ÔÖø£º²»Ïê ¸Ä±à£ºhtl258(Tony)
-- Subject: ÍêÉÆSQLÅ©Àúת»»º¯Êý£¨ÏÔʾÖÐÎĸñʽ£¬¼ÓÈëÈóÔµÄÏÔʾ£©
-------------------------------------------------------------------------- ......