ÎÊÌâÃèÊö
һλͬʱàдÁËÒ»¸öʹÓÃÌáʾwith (NOLOCK,NOWAIT)"µÄ²éѯ.
A colleague wrote a query which uses the hints "with (NOLOCK,NOWAIT)".
ÀýÈç
select first_name, last_name, age
from people with (nolock,nowait)
¼ÙÉè:
NOLOCK ˵²»Óõ£ÐÄÈκ춱ðµÄÈκÎËø¶¨£¬ÏÖÔھͶÁÈ¡Êý¾Ý"
NOLOCK says "don't worry about any locks at any level, just read the data now"
NOWAIT ˵²»ÒªµÈ´ý£¬Èç¹û±í±»Ëø¶¨£¬Ö»»á³ö´í"
ÎÊÌâ:
ΪʲôͬʱʹÓÃÁ½Õß?µ±È» NOWAIT ÓÀÔ¶²»»á±»ÊµÏÖ£¬ÒòΪ NOLOCK Òâζ×ÅËüÎÞÂÛÈçºÎ¶¼²»»áµÈ´ýËø......?
NOWAIT says "don't wait, just error if the table is locked"
Question:
Why use both at the same time? Surely NOWAIT will never be realised, as NOLOCK means it wouldn't wait for locks anyway ... ?
ÍƼö´ð°¸
ÕâÊǶàÓàµÄ(»òÕßÖÁÉÙÊÇÎÞЧµÄ).ÔÚÒ»¸ö²éѯ´°¿ÚÖУ¬Ö´ÐÐ:
It's redundant (or at least, ineffective). In one query window, execute:
create table T (ID int not null)
begin transaction
alter table T add ID2 int not null
±£³ÖÕâ¸ö´°¿Ú´ò¿ª£¬´ò¿ªÁíÒ»¸ö²éѯ´°¿Ú²¢Ö´ÐÐ:
leave this window open, open another query window and execute:
select * from T WITH (NOLOCK,NOWAIT)
¾¡¹ÜÓÐ NOWAIT
Ìáʾ£¬²¢ÇÒ¾¡¹Ü¼Ç¼ΪÔÚÓöµ½ÈκÎËøʱÁ¢¼´·µ»ØÏûÏ¢£¬µ«µÚ¶þ¸ö²éѯ½«¹ÒÆ𣬵ȴý Schema Ëø.
Despite the NOWAIT
hint, and despite it being documented as returning a message as soon as any lock is encountered, this second query will hang, waiting for the Schema lock.
<´ò»÷>ÔĶÁÓйرí¸ñÌáʾµÄÎĵµ:
NOWAIT
:
ָʾÊý¾Ý¿âÒýÇæÔÚ±íÉÏÓöµ½ËøʱÁ¢¼´·µ»ØÏûÏ¢
Instructs the Database Engine to return a message as soon as a lock is encountered on the table
Çë×¢Ò⣬ÕâÀïÖ¸µÄÊÇËø£¬ÈκÎËø.
Note that this is talking about a lock, any lock.
NOLOCK
(àÅ£¬Êµ¼ÊÉÏÊÇREADUNCOMMITTED
):
READUNCOMMITTED ºÍ NOLOCK Ìáʾ½öÊÊÓÃÓÚÊý¾ÝËø.ËùÓвéѯ£¬°üÀ¨ÄÇЩ´øÓÐ READUNCOMMITTED ºÍ NOLOCK ÌáʾµÄ²éѯ£¬¶¼»áÔÚ±àÒëºÍÖ´ÐÐÆÚ¼ä»ñÈ¡ Sch-S(¼Ü¹¹Îȶ¨ÐÔ)Ëø.Òò´Ë£¬µ±²¢·¢ÊÂÎñÔÚ±íÉϳÖÓÐ Sch-M(¼Ü¹¹ÐÞ¸Ä)Ëøʱ£¬²éѯ»á±»×èÈû.
READUNCOMMITTED and NOLOCK hints apply only to data locks. All queries, including those with READUNCOMMITTED and NOLOCK hints, acquire Sch-S (schema stability) locks during compilation and execution. Because of this, queries are blocked when a concurrent transaction holds a Sch-M (schema modification) lock on the table.
ËùÒÔ£¬NOLOCK
ȷʵÐèÒªµÈ´ý some Ëø.°Õ¹¤>
So, NOLOCK
does need to wait for some locks.
Õâƪ¹ØÓÚΪʲôҪͬʱʹÓà NOLOCK ºÍ NOWAIT?µÄÎÄÕ¾ͽéÉܵ½ÕâÁË£¬Ï£ÍûÎÒÃÇÍƼöµÄ´ð°¸¶Ô´ó¼ÒÓÐËù°ïÖú£¬Ò²Ï£Íû´ó¼Ò¶à¶àÖ§³Ö¸ú°æÍø£¡