|
VFP ·R¥ÎªÌªÀ°Ï ¥»°Q½×°Ï¬° Visual Foxpro ·R¥ÎªÌ¸gÅç¥æ¬yªº¦a¤è, ½Ð¦h¦h§Q¥Î"·j´M"ªº¥\¯à, ¥ý¬d¬Ý¬Ý¦³µL«e¨Ò¥i´`, ¦pªGÁÙ¦³¤£À´ªº¦Aµo°Ý. ³¡¥÷¥DÃD¦³ªþ¥[ÀÉ®×, ¶·¥ýµù¥U¦¨¬°ªÀ°Ï©~¥Á¤~¥i¥H¤U¸ü.
|
¤W¤@½g¥DÃD :: ¤U¤@½g¥DÃD |
µoªí¤H |
¤º®e |
Ruey
µù¥U®É¶¡: 2003-03-12 ¤å³¹: 1698 ¨Ó¦Û: tunglo
²Ä 1 ¼Ó
|
µoªí©ó: ¬P´Á¤@ ¤E¤ë 01, 2003 8:44 pm ¤å³¹¥DÃD: SQL7¤¤ªºLOCKªº²z¸Ñ(Âà¶K) |
|
|
¨Ó·½:¶®ª°
Âà¸ü¡mSQL7¤¤ªºLOCKªº²z¸Ñ1¡n
lock¬O¸ê®Æ®w¤¤ªº¤@Ó¤ñ¸û«n¡A¦ý¤S®e©ö³Q©¿µøªº°ÝÃD¡C
¥H«e¦bCTEC¤W½Ò®É¡AÁ`¦³¾ÇûÅ¥ªº®ÉÔÀ´¡A¦ý¦Ò¸Õ®É¦³¤£¤ÓÁA¸Ñ¡C
¦b½sµ{¤¤¡A¤S¦]©Ò¥Îªº½sµ{¤u¨ãªº¤£¦P¦Ó¤£¯à«Ü¦nªº¸Ñ¨Mlockªº°ÝÃD¡C
¤U±§Ú±NSQL7¤¤ªºlock°µÓ²³æªº¤¶²Ð¡A
¤p«¬¸ê®Æ®w¥H¤Î¤j¦h¼ÆªºMIS¨t²Î¡A¹ïlock¨S¤°»òn¨D¡A
ì¦]¬O¸ê®Æ¶q¤p¡A¨Öµo¤Ö¡A¥Î¤áºÝªº¦^À³n¨D¤£°ª¡C
¥Ñ¦¹¥i¨£¡A¤W±¤TÓ¦]¯À¥¿¬O§Ú̹ê²{lockµ¦²¤ªº¥Øªº¡C
¦bSQL7¤¤¡AQueryAnalyzer¬O¤@Ó«D±`¦nªº¤u¨ã¡A¥Î¥¦¥i¥H²M·¡ªº¤ÀªR
¤£¦Pªº»y¥y¦b¤£¦PªºÀô¹Ò¤U²£¥ÍªºÂê¡A¨Öµo©Ê¾Þ§@µ¥µ¥¡C
²³æªº»¡¡A´N¬O§Q¥Îsp_Lock
©MÁô¦¡¥æ©ö¨Ó°µ¨ì³o¤@ÂI¡C
º¥ý»Ýn±j½Õªº¬Olock§¹¥þ¨Ì¿à§Aªº»Ýn¡C¨Ã¤£¬O»¡lock¶V¦h¶V¦n¡C©Î¬Olock
¶V¤Ö¶V¦n¡C§ÚÌÀ³¸Ó¨î©w¦X¾Aªºlockµ¦²¤¨Óº¡¨¬§Ú̪º»Ýn¡C¨º´N¬O¦b¨Öµo©Ê©M¸ê®Æ§¹¾ã©Êªº¥¿ÅÂI¡C
¥Î¹Lwordªº³£ª¾¹D¡Auser1¥´¶}¤F¤@Óword¤å¾×¡Auser2¥´¶}¦P¤@Ó¤å¾×®É¡Aword·|¦³´£¥Ü§i¤§user2¥u¯à¦b°ßŪ¤è¦¡¤U¤u§@¡C¦b³oùØ¡A¬°¤F«OÅ@¤å¾×ªº¸ê®Æ§¹¾ã©Ê(user1¥[¤Flock)¡A¨Öµo¾Þ§@¨ü¨ì¤F¨î(user2 readonly)¡CSQL7¤]¦³¦P¼Ëªº±¡ªp¡C
¨ä¦¸¡A§ÚÌn»{ÃѨìlock¬O¥ÑSQL7¨Ó²£¥Íªº¡A¤]¬O¥ÑSQL7¨ÓºÞ²zªº¡Alock²£¥Íªº¼Æ¶q©MºØÃþ¨Ì¿à§Ú̪ºsql»y¥y©MÀô¹Ò¡C§Ú̳q±`¬O³q¹L±±¨îÀô¹ÒÅܼƨӶ¡±µ±±¨îlockªº²£¥Í¡A¡]ÁöµMSQL7¤ä«ù¦bsql»y¥y¤¤ª½±µ¥[Âê¡A¦ý³oºØ¥Îªk¤Ö¡^¡C
¦A¦³ªº´N¬Olockªº¥Í©R´Á¡C·í»y¥y°õ¦æ®É²£¥ÍÂê¡A·í»y¥yµ²§ô®Élock³QÄÀ©ñ¡A¦b¨Æ°È¤¤lockn¨ì¨Æ°Èµ²§ô¤~ÄÀ©ñ¡C±q³oÓ¯SÂI¤¤§ÚÌ´N¥i¥H§ä¨ìÆ[¹îlockªº¤èªk¡C¨º´N¬O¤£Åý¥æ©öµ²§ô¡CSQL7ªºÁô¦¡¥æ©ö´N¨ã¦³³oºØ¯à¤O¡A»Ýn»¡©úªº¬O¡ASQL7ªºÁô¦¡¥æ©ö¸g±`³Q¥Î©ó§ï¼gproduction¸ê®Æ®w¤¤ªº¸ê®Æ¡C·í§ï¼g¥¿½T®É¤~´£¥æ¡AÁקK¥X¿ù¡CSQL7ªºÁô¦¡¥æ©ö¦³«Ü±jªº¹ê½î·N¸q¡C
Âà¸ü¡mSQL7¤¤ªºLOCKªº²z¸Ñ2¡n
SQL7¤¤ÂꪺºØÃþ«Ü¦h¡A¦³DB¯Å¡Aªí¯Å¡APAGE¯Å¡A¦æ¯Å(key)¡A¦æ¯Å¬O7¤¤ªº·s¥\¯à¡A³o¤]¬O¤j«¬¸ê®Æ®wªº¤@Ӽлx¡C¦ý§Y¨Ï³o¼Ë¡ASQL7ªºÂê¤]¤£¦pDB2©MORACLE¡C§Ú̳ÌÃö¤ßªº¬Oªí¯Å©M¦æ¯Å¡A³oùؤ¶²ÐÂê¥Dn¥Øªº¬O¬°¤FÁA¸Ñ¦bSP_LOCK¤¤¥X²{ªº¸ê°Tªº§t¸q¡C
¥ý¤¶²Ðªí¯Å¡A¥Dn¨âºØ¡AS¡AX¡C S©MS¬Û®e¡AX©M¥ô¦ó³£¤£¬Û®e¡C¨Ò¡G
statement1 ¦bªíA¤W¬I¥[¤FS¡A¦¹®É¦pªGstatement2¦bªíA¤W¤]±N¬I¥[S¡A¨º»òstatement2´N¥i¥H³Q°õ¦æ(¨Öµo)¡A¦pªGstatement2¦bªíA¤W±N¬I¥[X¡A¨º»òstatement2±Nµ¥«Ý¡Aª½¨ìstatement1°õ¦æ§¹«á(S³QÄÀ©ñ)¤~³Q°õ¦æ(¨S¤F¨Öµo)¡C
¦pªGstatement1 ¦bªíA¤W¬I¥[¤FX¡A¦¹®É¦pªGstatement2¦bªíA¤W¤]±N¬I¥[S©ÎX¡A¨º»òstatement2±Nµ¥«Ý¡Aª½¨ìstatement1°õ¦æ§¹«á(X³QÄÀ©ñ)¤~³Q°õ¦æ(¨S¤F¨Öµo)¡C
±µµÛ¤¶²Ð¦æ¯Å¡A±qªí¯ÅÂê´N¥i¥H¬Ý¥X¡Aªí¯ÅÂê¹ï¨Öµoªº¤ä´©¦³¡A¦]¦¹¤j«¬¸ê®Æ®w¤~§Q¥Î¦æ¯ÅÂê¨Ó¤ä«ù§ó¦hªº¨Öµo¡C¦æ¯ÅÂê¤]¥Dn¦³¨âºØ¡AS¡AX¡C S©MS¬Û®e¡AX©M¥ô¦ó³£¤£¬Û®e¡C·íSQL7¹ê²{¦æ¯ÅÂꪺ®ÉÔ¡A¥¦¦Û°Ê¦bªí¤W¬I¥[ªí¯ÅÂêIS¡AIX¡C
¤]´N¬O¥u¦³¦bªí¤W¥ý¬I¥[IS¡A¤~¯à¦b¦æ¤W¬I¥[S¡A¥u¦³¦bªí¤W¥ý¬I¥[IX¡A¤~¯à¦b¦æ¤W¬I¥[X¡C¤@¤@¹ïÀ³¡C¨Ò¡G
statement1 ¦bªíA¤Wªº¦æA¬I¥[¤FS¡A¦ÛµM¦bªíA¤W¬I¥[¤FIS¡A¦¹®É¦pªGstatement2¦bªíAªº¥ô¤@¦æ¤W±N¬I¥[S(¦ÛµM¦bªíA¤W¬I¥[¤FIS)¡A¨º»òstatement2´N¥i¥H³Q°õ¦æ(¨Öµo)¡A¦pªGstatement2¦bªíA¤W°£¦æA¤W±N¬I¥[X(¦ÛµM¦bªíA¤W¬I¥[¤FIX)¡A¨º»òstatement2¤]¥i¥H³Q°õ¦æ(¨Öµo)¡A¦pªGstatement2¦bªíA¤W¦æA¤W±N¬I¥[X(¦ÛµM¦bªíA¤W¬I¥[¤FIX)¡A¨º»òstatement2±Nµ¥«Ý¡Aª½¨ìstatement1°õ¦æ§¹«á(S³QÄÀ©ñ)¤~³Q°õ¦æ(¨S¤F¨Öµo)¡C
¦bªí¯ÅÂꤤÁÙ¦³¤@ºØ¬OSIX¡A¤]´N¬Oº¥ý¦bªí¯Å¥[S¡A¦P®É»Ýn¦b¦æ¯Å¥[X¡C
±q¤W±ªº¤¶²Ð´N¥i¥H±o¨ì¦Uªí¯ÅÂê¡AS X IS IX SIXªº¬Û®eÃö«Y¡C§Ṳ́]¥i¥H±q«á±ªº¹êÅç¨ÓÅçÃÒ³o¤@ÂI¡C
¹êÅç¡G¹êÅ礤n«Ø¥ß¨ìDBªº¨âÓ³s±µ¡C¨ä¤¤¤@Ó³s±µ¹B¦æ¦bÁô§t¥æ©ö¤U¡C
¥Ø¼Ð¤@¡A¦b¹B¦æ¦bÁô§t¥æ©ö¤Uªº³s±µùØ¡A¹B¦æDML¡A¦A¹B¦æsp_lock,²z¸ÑDML»y¥y©Ò²£¥ÍªºÂꪺ¤£¦P¡C
¥Ø¼Ð¤G¡A¦b¹B¦æ¦bÁô§t¥æ©ö¤Uªº³s±µùØ¡A³]¸m¤£¦Pªº¨Æª«¹jÂ÷¯Å§O¡A¦A¹B¦æDML¡A²z¸Ñ¦b¤£¦P¹jÂ÷¯Å§O¤Uªº¦P¤@»y¥y©Ò²£¥Í¤£¦PªºÂê¡C
¥Ø¼Ð¤T¡A¦P®É¦b¥t¤@³s±µ¤¤¹B¦æ¤£¦PªºDML¡A³q¹L°õ¦æ±¡ªp¡A¨ÓÅçÃÒÂꪺ¬Û®e©Ê¡C
Âà¸ü¡mSQL7¤¤ªºLOCKªº²z¸Ñ3¡n
¦b¹ê»Ú±¡ªp¤¤¡A³q±`·|¦]¬°lockµo¥Í¥|¤¤±¡ªp¡C
¤U±¥ÎÓ©w®y¨t²ÎÁ|¨Ò¡G©Ò¦³ªº»y¥y³£¤u§@¦bÀq»{¹jÂ÷¯Å§O¤U
1 ¥á¥¢ªº§ó·s
user1 run
begin tran
select ªÅ®y¦ì ±o¨ì1¡A2¡A3
update 1 ©w®y¦ì1
commit
¦pªGuser2¦buser1 update¤§«e¹B¦æ¦P¼Ëªºµ{¦¡¡A³£·|±o¨ìªÅ®y¦ì¬O1,2,3ªº¸ê°T
¡A·íµMuser2¤]¥i¥H©w®y¦ì1¡Cµ²ªG´N¬Ouser2ªº©w®y¬O³Ì²×¦³®Äªº¡Auser1ªº©w®y
³QÂл\¡C¬°¤FÁקK³oºØ±¡ªp¡A¬Ý¬Ý¥Î¹jÂ÷¯Å§O¯à¤£¯à¸Ñ¨M¡C
³o¥|ºØ¹jÂ÷¯Å§O³Ì¦h¦bªí¤W¬I¥[S¡A¥Ñ©óS¬O¬Û®eªº¡A¦]¦¹user2·Ó¼Ë¯à±o¨ìªÅ®y¦ì
¬O1,2,3ªº¸ê°T¡A¥á¥¢ªº§ó·s¤]·Ó¼Ë·|µo¥Í¡CSQL´£¨Ñ¤Fªí¯ÅÂê©w¿ï¶µTABLOCKX¡A
¯àÁקK³oºØ±¡ªpªºµo¥Í¡A¦ý³à¥¢¤F¨Öµo¡C
2 żŪ
user1 run
begin tran
update row A
commit
¦pªGuser2¯à¦buser1 updateªº¦P®ÉŪ¥Xªí¤¤ªº¸ê®Æ(¥]§t row A) ¨º»ò´N²£¥Í¤F
żŪ¡A¦]¬°user1¥i¯àcommit¤]¥i¯àrollback¡Aupdate¨Ã¨S¦³³Q½T»{¡CÀq»{±¡ªp¤U
¬O¤£·|²£¥ÍżŪªº¡A¦]¬°update±N²£¥Í¦æªºX¡AªíªºIX¡A¦ÓªíIX©MS¬O¤£¬Û®eªº¡C
¨º»ò¦b¤°»ò±¡ªp¤U§ÚÌ»ÝnżŪ©O¡A¦pªG§Ṳ́£»Ýn½T¤Áªº¸ê®Æ¡A§ÚÌ»Ýnªº¬O
¤@Ó¤j·§ªºÁͶաAÓ§O¸ê®Æªº¤£½T©w¨S¦³Ãö«Y¡A·Q·Q¤@ӪѲ¼¨t²Î¡A¦P¤@®É¨èªº
§ï¼g·|«Ü¦h¡A¤]´N¬Oªí¤W·|¦³³\¦hIXÂê¡A¦pªG§ÚÌ»Ýn¬d¸ßªíÀò±o¤jP±¡ªp¡A¨º
»ò¥u¦³µ¥§ï¼gµ²§ô¡A¬°¤F´£°ª¨Öµo¡A¦¹®É§ÚÌ»ÝnżŪ¡C¸Ñ¨M¤èªk´N¬O¦buser2ªº
·|¸ÜÀô¹Ò¤¤³]¸m¨Æ°È¹jÂ÷¯Å§O¬°read uncommitted
3 ¤£¥i«½ÆŪ
user1 run
begin tran
select tab1 where
...
...
select tab1 where
commit
·íuser1¦b¥æ©ö¤¤°õ¦æ¨â¦¸select¡A©Ò±o¨ìªºµ²ªG¤£¤@¼Ë¡A³o«K¬O¤£¥i«½ÆŪ¡C
µo¥Íªºì¦]¬Ouser2¦buser1²Ä¤G¦¸select¤§«e¹ïtab1°µ¤Fקï¡Cselect»y¥yÁöµM
¹ïªí¦³ÅªÂê¡A¦ý¦bÀq»{±¡ªp¤U¡AŪÂê¦bselect»y¥y°õ¦æ§¹´N³QÄÀ©ñ¡A¦Ó¤£¬O«O«ù
¨ì¥æ©öµ²§ô¡C¬°¤FÁקK¤£¥i«½ÆŪªºµo¥Í¡A¥unselect²£¥ÍªºÂê«O«ù¨ì¥æ©öµ²§ô
´N¥i¥H¤F¡A±Nuser1ªº·|¸ÜÀô¹Ò¤¤³]¸m¨Æ°È¹jÂ÷¯Å§O¬°repeatable read
4 ¤Û¼v
user1 run
begin tran
select tab1 where
...
...
select tab1 where
commit
ÁöµM±Nuser1ªº·|¸ÜÀô¹Ò¤¤³]¸m¨Æ°È¹jÂ÷¯Å§O¬°repeatable read¡A¦ý·íuser1¦b¥æ
©ö¤¤°õ¦æ¨â¦¸select¡A©Ò±o¨ìªºµ²ªGÁÙ·|¤£¤@¼Ë¡A³o«K¬O¤Û¼v¡C¦]¬°user1¶È¶È±N
select¥X¨Óªº¦æ¥[¤FSÂê¡A¦ýuser2¥i¥Hinsertº¡¨¬where±ø¥óªº·s¦æ¡A¨Ï±o¨â¦¸
selectªºµ²ªG¤£¤@¼Ë¡A¸Ñ¨M¤èªk¬O±Nuser1ªº·|¸ÜÀô¹Ò¤¤³]¸m¨Æ°È¹jÂ÷¯Å§O¬°
serializable,©ÎªÌ¦bªí¯ÅÂê©w¿ï¶µ¤¤¿ïholdlock
Âê©w¶W®É¡G
«e±»¡¹L¡A·í¬YÓ»y¥y¦]¬°Âê¦Ó¤£¯à¥ß§Y°õ¦æ®É¡A·|µ¥«Ý¡Aª½¨ìÂê³QÄÀ©ñ¡C¦ý¦p
ªG«ù¦³Âꪺ»y¥y°õ¦æ®É¶¡¹Lªø(¥¼Àu¤Æ)¡C¨º»ò´N·|µ¥«Ý¹Lªø¡A¼vÅTÅTÀ³¡A¦]¦¹¡A
§Ú̳q¹Llock_timeout¨Ó±Nµ¥«Ý¹Lªøªº»y¥yrollback¡C»Ýnª`·Nªº¬O¡A·í¸ê®Æ¶q
¼W¥[®É¡A¥i¯àµo¥Í¤£§Æ±æªºlock_timeout¡Cì¦]¬OSQL»Ýn§ó¦hªº®É¶¡¨Ó³B²z¸ê®Æ
¡A¬°¦¹§Ṳ́]»Ýn¾A·íªº¼W¥[lock_timeout¡C _________________ #############################
§Ö¼Ö¶ý«}¨t¦C©¯ºÖ¦v°t,³Ü¤Q¥þÂû´ö~ì¨Ó©¯ºÖ¨º»ò²³æ!!
¾Ç·|VFP¨Ï¥ÎªÌªÀ°Ïªº·j´M,Code¤~·|§ó¦³½ì~
############################# |
|
¦^³»ºÝ |
|
|
|
|
±z µLªk ¦b³oÓª©±µoªí¤å³¹ ±z µLªk ¦b³oÓª©±¦^ÂФ峹 ±z µLªk ¦b³oÓª©±½s¿è¤å³¹ ±z µLªk ¦b³oÓª©±§R°£¤å³¹ ±z µLªk ¦b³oÓª©±¶i¦æ§ë²¼ ±z µLªk ¦b³oÓª©±ªþ¥[ÀÉ®× ±z µLªk ¦b³oÓª©±¤U¸üÀÉ®×
|
Powered by phpBB © 2001, 2005 phpBB Group ¥¿Å餤¤å»y¨t¥Ñ phpbb-tw ºûÅ@»s§@
|