MySQL数据库如何锁定和解锁数据库表 查询mysql 哪些表正在被锁状态
\u5982\u4f55\u5229\u7528MySQL\u6570\u636e\u5e93\u9501\u5b9a\u548c\u89e3\u9501\u6570\u636e\u5e93\u8868\u7b2c\u4e00\u6b65\uff0c\u521b\u5efa\u6570\u636e\u5e93\u8868writer\u548c\u67e5\u770b\u8868\u7ed3\u6784\uff0c\u5229\u7528SQL\u8bed\u53e5\uff1a
create table writer(
wid int(10),
wno int(10),
wname varchar(20),
wsex varchar(2),
wage int(2)
);
desc writer;
\u5982\u4e0b\u56fe\u6240\u793a\uff1a
\u7b2c\u4e8c\u6b65\uff0c\u5411\u6570\u636e\u5e93\u8868writer\u63d2\u5165\u4e94\u6761\u6570\u636e\uff0c\u63d2\u5165\u540e\u67e5\u770b\u8868\u91cc\u6570\u636e\uff0c\u5982\u4e0b\u56fe\u6240\u793a\uff1a
\u7b2c\u4e09\u6b65\uff0c\u5229\u7528\u9501\u5b9a\u8bed\u53e5\u9501\u5b9a\u6570\u636e\u5e93\u8868writer\uff0c\u5229\u7528SQL\u8bed\u53e5\uff1a
lock table writer read;
\u8ba9\u6570\u636e\u5e93\u8868\u53ea\u8bfb\u4e0d\u80fd\u8fdb\u884c\u5199
\u5982\u4e0b\u56fe\u6240\u793a\uff1a
\u7b2c\u56db\u6b65\uff0c\u4e3a\u4e86\u9a8c\u8bc1\u9501\u5b9a\u6548\u679c\uff0c\u53ef\u4ee5\u67e5\u770b\u6570\u636e\u5e93\u8868\u6570\u636e\uff0c\u5229\u7528SQL\u8bed\u53e5\uff1a
select * from writer;
\u5982\u4e0b\u56fe\u6240\u793a\uff1a
\u7b2c\u4e94\u6b65\uff0c\u5229\u7528update\u8bed\u53e5\u5bf9id=5\u8fdb\u884c\u66f4\u65b0\uff0cSQL\u8bed\u53e5\u4e3a\uff1a
update writer set wname = '\u80e1\u601d\u601d' where id = 5;
\u5982\u4e0b\u56fe\u6240\u793a\uff1a
6
\u7b2c\u516d\u6b65\uff0c\u5229\u7528unlock\u8fdb\u884c\u89e3\u9501\uff0cSQL\u8bed\u53e5\u4e3a\uff1a
unlock tables;
\u5982\u4e0b\u56fe\u6240\u793a\uff1a
\u53ef\u76f4\u63a5\u5728mysql\u547d\u4ee4\u884c\u6267\u884c\uff1ashow engine innodb status\G;
\u67e5\u770b\u9020\u6210\u6b7b\u9501\u7684sql\u8bed\u53e5\uff0c\u5206\u6790\u7d22\u5f15\u60c5\u51b5\uff0c\u7136\u540e\u4f18\u5316sql\u7136\u540eshow processlist;
show status like \u2018%lock%\u2019
show OPEN TABLES where In_use > 0; \u8fd9\u4e2a\u8bed\u53e5\u8bb0\u5f55\u5f53\u524d\u9501\u8868\u72b6\u6001
\u53e6\u5916\u53ef\u4ee5\u6253\u5f00\u6162\u67e5\u8be2\u65e5\u5fd7\uff0clinux\u4e0b\u6253\u5f00\u9700\u5728my.cnf\u7684[mysqld]\u91cc\u9762\u52a0\u4e0a\u4ee5\u4e0b\u5185\u5bb9\uff1a
slow_query_log=TRUE(\u6709\u4e9bmysql\u7248\u672c\u662fON)
slow_query_log_file=/usr/local/mysql/slow_query_log.txt
long_query_time=3
\u6269\u5c55\u8d44\u6599\uff1aMySQL\u9501\u5b9a\u72b6\u6001\u67e5\u770b\u547d\u4ee4
Checking table\uff1a\u6b63\u5728\u68c0\u67e5\u6570\u636e\u8868\uff08\u8fd9\u662f\u81ea\u52a8\u7684\uff09\u3002
Closing tables\uff1a\u6b63\u5728\u5c06\u8868\u4e2d\u4fee\u6539\u7684\u6570\u636e\u5237\u65b0\u5230\u78c1\u76d8\u4e2d\uff0c\u540c\u65f6\u6b63\u5728\u5173\u95ed\u5df2\u7ecf\u7528\u5b8c\u7684\u8868\u3002\u8fd9\u662f\u4e00\u4e2a\u5f88\u5feb\u7684\u64cd\u4f5c\uff0c\u5982\u679c\u4e0d\u662f\u8fd9\u6837\u7684\u8bdd\uff0c\u5c31\u5e94\u8be5\u786e\u8ba4\u78c1\u76d8\u7a7a\u95f4\u662f\u5426\u5df2\u7ecf\u6ee1\u4e86\u6216\u8005\u78c1\u76d8\u662f\u5426\u6b63\u5904\u4e8e\u91cd\u8d1f\u4e2d\u3002
Connect Out\uff1a\u590d\u5236\u4ece\u670d\u52a1\u5668\u6b63\u5728\u8fde\u63a5\u4e3b\u670d\u52a1\u5668\u3002
Copying to tmp table on disk\uff1a\u7531\u4e8e\u4e34\u65f6\u7ed3\u679c\u96c6\u5927\u4e8etmp_table_size\uff0c\u6b63\u5728\u5c06\u4e34\u65f6\u8868\u4ece\u5185\u5b58\u5b58\u50a8\u8f6c\u4e3a\u78c1\u76d8\u5b58\u50a8\u4ee5\u6b64\u8282\u7701\u5185\u5b58\u3002
Creating tmp table\uff1a\u6b63\u5728\u521b\u5efa\u4e34\u65f6\u8868\u4ee5\u5b58\u653e\u90e8\u5206\u67e5\u8be2\u7ed3\u679c\u3002
deleting from main table\uff1a\u670d\u52a1\u5668\u6b63\u5728\u6267\u884c\u591a\u8868\u5220\u9664\u4e2d\u7684\u7b2c\u4e00\u90e8\u5206\uff0c\u521a\u5220\u9664\u7b2c\u4e00\u4e2a\u8868\u3002
create table writer(
wid int(10),
wno int(10),
wname varchar(20),
wsex varchar(2),
wage int(2)
第二步，向数据库表writer插入五条数据，插入后查看表里数据
第三步，利用锁定语句锁定数据库表writer，利用SQL语句：
lock table writer read;
让数据库表只读不能进行写
第四步，为了验证锁定效果，可以查看数据库表数据，利用SQL语句：
select * from writer;
第五步，利用update语句对id=5进行更新，SQL语句为：
update writer set wname = '胡思思' where id = 5;
第六步，利用unlock进行解锁，SQL语句为：
unlock tables;
绛旓細1銆佹墦寮mysql鐨瀹㈡埛绔 杩欓噷浣跨敤navicat锛岃繛鎺鏁版嵁搴锛岀瓑鍒皀avicat涓婚〉闈紝鍙屽嚮闇瑕佹搷浣滅殑鏁版嵁搴撹繛鎺ャ2銆佺櫥褰曞埌鏁版嵁搴撲富椤甸潰鍚庯紝鐐瑰嚮宸︿晶鐨勬暟鎹簱杩炴帴锛屾墦寮鏁版嵁搴擄紝鍙互鐪嬪埌鍙互鎿嶄綔鐨勬墍鏈夋暟鎹簱銆3銆佽繖鏃舵湁鏈変袱涓暟鎹簱锛岀洰鏍囨槸灏嗘暟鎹1鐨勬墍鏈夋暟鎹悓姝ュ埌鏁版嵁搴2涓婏紝闇瑕佺偣鍑讳富椤甸潰涓婄殑銆4銆佹墦寮宸ュ叿鑿滃崟锛...
绛旓細pthread_mutex_lock() 鍜 pthread_mutex_unlock() 瀹炵幇浠ヨ幏寰楅珮閫熷害銆傛煡鐪嬬珷鑺 6.3.6.2 杈呭姪鍔熻兘鍑芥暟銆 鏌ョ湅绔犺妭 5.3.1 MySQL 濡備綍閿佸畾琛,浠ヨ幏鍙栧叧浜庨攣瀹氭柟妗堢殑鏇村淇℃伅銆 浣犲彲浠ヤ娇鐢 FLUSH TABLES WITH READ LOCK 鍛戒护浠ヨ閿侀攣瀹鎵鏈鏁版嵁搴涓殑鎵鏈夎〃銆傛煡鐪嬬珷鑺 4.5.3 FLUSH 鍙ユ硶銆傚鏋滀綘鏈変竴涓彲浠ュ強鏃跺缓绔...
绛旓細浠庡簱涓婂皢涓㈠け杩欓儴鍒鏁版嵁銆傝В鍐冲姙娉曪細浠庡浠藉紑濮嬪埌鍚姩澶嶅埗锛閿佸畾琛 t锛屼繚璇 aaaa:10000-20000 涓病鏈変慨鏀硅〃 t 鐨勪簨鍔°傛纭慨澶嶆楠わ細瀵硅〃 t 鍔犺閿侊紱鍦ㄤ富搴撲笂澶囦唤琛 t锛涘仠姝粠搴撳鍒讹紝鎭㈠琛 t锛涘惎鍔ㄥ鍒讹紱瑙ｉ攣琛 t銆傚鏋滄槸澶ц〃锛岃繖閲屽彲浠ョ敤鍙紶杈撹〃绌洪棿鏂瑰紡澶囦唤銆佹仮澶嶈〃锛屽噺灏戦攣琛ㄦ椂闂淬
绛旓細鍙﹀鍙互鎵撳紑鎱㈡煡璇㈡棩蹇楋紝linux涓嬫墦寮闇鍦╩y.cnf鐨刐mysqld]閲岄潰鍔犱笂浠ヤ笅鍐呭锛歴low_query_log=TRUE(鏈変簺mysql鐗堟湰鏄疧N)slow_query_log_file=/usr/local/mysql/slow_query_log.txt long_query_time=3 select *from v$locked_object锛氬彲浠ヨ幏寰楄閿佺殑瀵硅薄鐨刼bject_id鍙婁骇鐢熼攣鐨勪細璇漵id銆傞氳繃鏌ヨ缁撴灉涓...
绛旓細鏂囦腑shell涓巑ysql鍧囦娇鐢╮oot璐﹀彿锛屽湪鐪熷疄鐜涓紝璇锋牴鎹儏鍐垫洿鎹1.鍋滄slave鏈嶅姟鍣ㄧ殑涓讳粠鍚屾 涓轰簡闃叉涓讳粠鏁版嵁涓嶅悓姝ワ紝闇瑕佸厛鍋滄slave涓婄殑鍚屾鏈嶅姟銆係TOP SLAVE;2.瀵筸aster鏈嶅姟鍣ㄧ殑鏁版嵁搴鍔犻攣 涓轰簡閬垮厤鍦ㄥ浠界殑鏃跺欏鏁版嵁搴撹繘琛屾洿鏂版搷浣滐紝蹇呴』瀵规暟鎹簱鍔犻攣銆侳LUSH TABLES WITH READ LOCK;濡傛灉鏄痺eb鏈嶅姟...
绛旓細褰撲綘瀹屾垚浜MySQL鏁版嵁搴澶囦唤鏃,闇瑕侀噸鍚湇鍔″櫒(濡傛灉鍏抽棴浜嗗畠)鎴栭噴鏀惧姞鍦ㄨ〃涓婄殑閿佸畾(濡傛灉浣犺鏈嶅姟鍣ㄨ繍琛)銆 瑕佺敤鐩存帴鎷疯礉鏂囦欢鎶婁竴涓暟鎹簱浠庝竴鍙版満鍣ㄦ嫹璐濆埌鍙︿竴鍙版満鍣ㄤ笂,鍙槸灏嗘枃浠舵嫹璐濆埌鍙︿竴鍙版湇鍔″櫒涓绘満鐨勯傚綋鏁版嵁鐩綍涓嬪嵆鍙傝纭繚鏂囦欢鏄疢yIASM鏍煎紡鎴栦袱鍙版満鍣ㄦ湁鐩稿悓鐨勭‖浠剁粨鏋,鍚﹀垯浣犵殑鏁版嵁搴撳湪鍙︿竴鍙颁富鏈轰笂...
绛旓細鍥犱负涓涓敤鎴疯繘绋嬪彲鑳戒骇鐢熶竴涓互涓婄殑閿, 鏉OS杩涚▼骞朵笉鑳藉交搴曟竻闄ら攣鐨勯棶棰樸 璁板緱鍦鏁版嵁搴绾у埆鐢╝lter system kill session 'sid,serial#';鏉鎺変笉姝ｅ父鐨勯攣銆 杩欓噷杩樿浜嗕竴浜: ...>> 鏌ョ湅oracle鏄惁閿佽〃 Oracle鏁版嵁搴撴搷浣滀腑,鎴戜滑鏈夋椂浼氱敤鍒伴攣琛ㄦ煡璇互鍙瑙ｉ攣鍜宬ill杩涚▼绛夋搷浣,閭ｄ箞杩欎簺鎿嶄綔鏄庝箞瀹炵幇鐨勫憿?鏈枃...
绛旓細浠庤〃绌洪棿涓鍙22涓牳蹇冨厓鏁版嵁锛屽啀娆″瓨鍌ㄥ苟瀹屾垚鏁翠釜杩囩▼銆傜粡杩囪繖浜涙楠わ紝鏁版嵁瀛楀吀琛ㄧ殑鍏冩暟鎹凡鍑嗗灏辩华锛岀瓑寰呬綘鐨勬帰绱傛牳蹇冩暟鎹瓧鍏歌〃鍏冩暟鎹紝濡俢atalogs鍜宑haracter_sets绛夛紝琚簿蹇冩寫閫夊嚭鏉ワ紝鏀拺璧锋暣涓鏁版嵁搴涓栫晫鐨勫熀鐭炽傚綋MySQL鍚姩鏃讹紝瀹冨嚟鍊熼鍏堢紪鐮佺殑琛ㄥ畾涔夊拰m_core_registry涓殑涓存椂澶囩敤鍏冩暟鎹紝椤哄埄...
绛旓細濡傛灉浣犱笉鍦鏁版嵁搴鐩綍涓嬶紝鍙氳繃鎸囧畾鍒扮洰褰曠殑璺緞妫鏌ユ墍鏈夊湪閭ｉ噷鐨勮〃锛歴hell> myisamchk /path/to/database_dir/*.MYI 浣犵敋鑷冲彲浠ラ氳繃涓MySQL鏁版嵁鐩綍鐨勮矾寰勬寚瀹氫竴涓氶厤绗︽潵妫鏌ユ墍鏈夌殑鏁版嵁搴撲腑鐨勬墍鏈夎〃锛歴hell> myisamchk /path/to/datadir/*/*.MYI 鎺ㄨ崘鐨勫揩閫熸鏌ユ墍鏈塎yISAM琛ㄧ殑鏂瑰紡鏄細shell> ...
绛旓細mysql select for update鐨 connection 浠ヤ笅鍔ㄤ綔涔嬩竴锛1 鏂紑鏁版嵁搴閾炬帴 2 commit 3 rollback
