OracleµÄËø»úÖƹéÄÉ×ܽá
OracleµÄËø»úÖƹéÄÉ×ܽá
ËøÊÇ·ÀÖ¹ÔÚÁ½¸öÊÂÎñ²Ù×÷ͬһ¸öÊý¾ÝÔ´£¨±í»òÐУ©Ê±½»»¥ÆÆ»µÊý¾ÝµÄÒ»ÖÖ»úÖÆ¡£Oracle²ÉÓ÷âËø¼¼Êõ±£Ö¤²¢·¢²Ù×÷µÄ¿É´®ÐÐÐÔ¡£OracleµÄËø·ÖΪÁ½´ó
ÀࣺÊý¾ÝËø£¨Ò²³ÆDMLËø£©ºÍ×ÖµäËø¡£×ÖµäËøÊÇOracle
DBMSÄÚ²¿ÓÃÓÚ¶Ô×Öµä±íµÄ·âËø¡£×ÖµäËø°üÀ¨Óï·¨·ÖÎöËøºÍDDLËø£¬ÓÉDBMSÔÚ±ØÒªµÄʱºò×Ô¶¯¼ÓËøºÍÊÍ·ÅËø£¬Óû§ÎÞ»ú¿ØÖÆ¡£
OracleÖ÷ÒªÌṩÁË5ÖÖÊý¾ÝËø£º¹²ÏíËø£¨Share Table Lock£¬¼ò³ÆSËø£©¡¢ÅÅËüËø£¨Exclusive Table
Lock£¬¼ò³ÆXËø£©¡¢Ðм¶Ëø£¨Row Share Table Lock£¬¼ò³ÆRSËø£©¡¢Ðм¶ÅÅËüËø£¨Row Exclusive Table
Lock£¬¼ò³ÆRXËø£©ºÍ¹²ÏíÐм¶ÅÅËüËø£¨Share Row Exclusive Table
Lock£¬¼ò³ÆSRXËø£©¡£Æä·âËøÁ£¶È°üÀ¨Ðм¶ºÍ±í¼¶¡£
1. ¹²ÏíËø£¨Share Table Lock£¬S£©£º
¼ÓËøÓï·¨£ºLock Table TableName In Share Mode;
ÔÊÐíµÄ²Ù×÷£ºÒ»¸ö¹²ÏíËøÓÉÒ»¸öÊÂÎñ¿ØÖÆ£¬½öÔÊÐíÆäËüÊÂÎñ²éѯ±»Ëø¶¨µÄ±í¡£Ò»¸öÓÐЧµÄ¹²ÏíËøÃ÷È·µØÓÃSelect … For
updateÐÎʽËø¶¨ÐУ¬»òÖ´ÐÐLock Table TableName In Share
ModeÓï·¨Ëø¶¨Õû¸ö±í£¬²»ÔÊÐí±»ÆäËüÊÂÎñ¸üС£ÔÊÐí¶à¸öÊÂÎñÔÚͬһ¸ö±íÉϼӹ²ÏíËø£¬ÕâÖÖÇé¿öϲ»ÔÊÐíÔڸñíÉϼÓËøµÄÊÂÎñ¸üÐÂ±í£¨¼´Ê¹ÓÐÒ»¸öÊÂÎñ¿ØÖƵÄÊÇ
ÐÎÈçSelect Row … for
updateÕâÑùÐÐËøÒ²ÊDz»±»ÔÊÐíµÄ£©¡£Òò´Ë£¬½öÓÐÒ»¸öÊÂÎñµÄÒ»¸ö¹²ÏíËø¿ÉÒÔ¸üиñíÈç¹ûÆäËüÊÂÎñÒ²ÓÐÏàͬµÄÊÂÎñÔڸñíÉϵĻ°¡£
½ûÖ¹µÄ²Ù×÷£ºÒ»¸ö¹²ÏíËøÓÉÒ»¸öÊÂÎñÀ´¿ØÖÆ£¬·ÀÖ¹ÆäËüÊÂÎñ¸üиñí»òÖ´ÐÐÏÂÃæµÄÓï¾ä£º
LOCK TABLE TableName IN SHARE ROW EXCLUSIVE MODE;
LOCK TABLE TableName IN ROW EXCLUSIVE MODE;
2. ÅÅËüËø£¨Exclusive Table Lock£¬X£©£º
ÅÅËüËøÊÇÔÚËø»úÖÆÖÐÏÞÖÆ×î¶àµÄÒ»ÖÖËøÀàÐÍ£¬ÔÊÐí¼ÓÅÅËüËøµÄÊÂÎñ¶À×Ô¿ØÖƶԱíµÄдȨÏÞ¡£
¼ÓËøÓï·¨£ºLock Table TableName In Exclusive Mode;
ÔÊÐíµÄ²Ù×÷£ºÔÚÒ»¸ö±íÖÐÖ»ÄÜÓÐÒ»¸öÊÂÎñ¶Ô¸Ã±íʵÐÐÅÅËüËø£¬ÅÅËüËø½öÔÊÐíÆäËüµÄÊÂÎñ²éѯ¸Ã±í¡£
½ûÖ¹µÄ²Ù×÷£ºÓµÓÐÅÅÍâËøµÄÊÂÎñ½ûÖ¹ÆäËüÊÂÎñÖ´ÐÐÆäËüÈκÎDMLÀàÐ͵ÄÓï¾ä»òÔڸñíÉϼÓÈκÎÆäËüÀàÐ͵ÄËø¡£
¶¨ÒåÅÅËüËøµÄÓï·¨£º
LOCK TABLE TableName IN EXCLUSIVE MODE;
3. Ðм¶Ëø£¨Row Share Table Lock£¬RS£©£º
Ò»¸öÐм¶Ëø£¨ÓÐʱ³ÆΪSubshare Table Lock£¬¼ò³ÆSS£¬×Ó¹²ÏíËø£©ÐèÒª¸ÃÊÂÎñÔÚ±»Ëø¶¨ÐеıíÉÏÓÃupdateµÄÐÎʽ¼ÓËø¡£µ±ÓÐÏÂÃæÓï¾ä±»Ö´ÐеÄʱºòÐм¶Ëø×Ô¶¯¼ÓÔÚ²Ù×÷µÄ±íÉÏ¡£
SELECT . . . from TableName. . . FOR U
Ïà¹ØÎĵµ£º
ËäȻѧϰJavaºÜ¾ÃÁË£¬×Ô¼ºÒ²Á¬½Ó¹ýһЩÊý¾Ý¿â£¬±ÈÈçmysqlÖ®ÀàµÄ£¬Èç½ñÄØ£¬Ò²Ñ§Ï°ÁËÒ»¶Îʱ¼äµÄOracle£¬È»¶øÄØ£¬½ñÌìÊÇÎÒµÚÒ»´ÎÁ¬½ÓOracle£¬ºÙºÙ£¬Ó¦¸Ã»¹²»ËãÌ«³Ù°É¡£
½ñÌìÄØ£¬Óе㱿׾£¬´ó¼ÒĪЦ£¡
ÎÒÕâÊÇÒ»¸ö²éѯÀý×Ó
Ê×ÏÈ£¬Ô ......
ѧϰOracle DBAÒ²°ë¸ö¶àѧÆÚÁË£¬½ñÌìÃÍÈ»²Å·¢ÏÖ£¬ÔÀ´ÎÒµÄÊ黹ÊǺÜеģ¬ÉϿβÙ×÷ʱºòÒ²Ö»ÊÇÖªµÀ´ó¸ÅÔõô×ö£¬µ«ÊÇÒªÕæµÄÈ«²¿×Ô¼º×ö£¬¶ø²»È¥·Ê黹ÊÇÓÐÒ»¶¨µÄÄѶȵģ¬ËùÒÔÄØ£¬½ñÌ쿪ʼ½«DBA´ÓÍ·¸´Ï°Ò»±é£¬Í¬Ê±ÔÙ²Ù×÷Ò»±é¡£
µÚÒ»Õ£¬Ñ§µÄÊÇOracleµÄÌåϵ½á¹¹£ ......
»¹ÊǼ¸Î»¶ÁÎÒµÄÊé (¡¶Oracle´óÐÍÊý¾Ý¿âϵͳÔÚAIX UNIXÉϵÄʵսÏê½â¡·) µÄ¶ÁÕßÀ´Óʼþ×ÉѯÎÊÌ⣬ºÜ±§Ç¸ÎÒ²»ÄÜÖðλ»Ø¸´Óʼþ£¬µÚÒ»ÎÒÕæµÄͦ棬µÚ¶þÕâЩÎÊÌâÆÄÓй²ÐÔ¡£ÎÒÀ´½èÖúÕâ¸öµØ·½£¬Í³Ò»µØºÍÖîλ̽ÌÖ£¬ºÜ¶àÎÊÌâÎÒÒ²ÊÇÔÚÃþË÷ÖУ¬²»ÄÜÖ±½Ó¸ø´ó¼Ò´ð°¸£¬ºÇºÇ£¬´ó¼Ò¼ûÁ°¡!
˵˵ËøÎÊÌâ¡£ MS SQLÖ§³ÖµÄËøÀàÐͺÍOracle²îÒìÊǺ ......
OracleÌṩÁË·ÖÇø¼¼ÊõÒÔÖ§³ÖVLDB(Very Large DataBase)¡£·ÖÇø±íͨ¹ý¶Ô·ÖÇøÁеÄÅжϣ¬°Ñ·ÖÇøÁв»Í¬µÄ¼Ç¼£¬·Åµ½²»Í¬µÄ·ÖÇøÖС£·ÖÇøÍêÈ«¶ÔÓ¦ÓÃ͸Ã÷¡£
OracleµÄ·ÖÇø±í¿ÉÒÔ°üÀ¨¶à¸ö·ÖÇø£¬Ã¿¸ö·ÖÇø¶¼ÊÇÒ»¸ö¶ÀÁ¢µÄ¶Î£¨SEGMENT£©£¬¿ÉÒÔ´æ·Åµ½²»Í¬µÄ±í¿Õ¼äÖС£²éѯʱ¿ÉÒÔͨ¹ý²éѯ±íÀ´·ÃÎʸ÷¸ö·ÖÇøÖеÄÊý¾Ý£¬Ò²¿ÉÒÔͨ¹ýÔÚ²éѯ ......
Ç°ÑÔ
ÿһ¸öDBAÔÚ½øÐÐÊý¾Ý¿â¹ÜÀíµÄ¹ý³ÌÖв»¿É±ÜÃâµÄÒªÓöµ½ÐÎÐÎÉ«É«µÄ´íÎó(ORA-1547 ,ORA-904,ORA-1578 ......)¡£ÓÐЩ´íÎóÓÉÓÚƵ·±³öÏÖ¡¢ÔÒò¸´ÔÓ¶ø±» Oracle DBA ÃÇÏ·³Æ֮Ϊ"¾µäµÄ´íÎó"¡£ÆäÖÐORA-3113 "end of file on communication channel" ¾ÍÊÇÕâÑùµÄÒ»¸ö¡£
ÎÒÃÇ¿ÉÒÔ¼òµ¥µÄ°ÑÕâ¸ö´íÎóÀí½âΪOracle¿Í»§¶Ë½ø³ÌºÍÊý¾Ý¿ ......