遍匈 / Mysql縮殻 / Mysql單晒縮殻 /

Mysql迄字崙才並暦陣崙

<貧匯准 和匯准>

泌採紗迄

迄協燕議囂隈?
LOCK TABLES
tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}
[, tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}] ...
盾迄囂隈?
UNLOCK TABLES

innodb議贋刈哈陪戻工佩雫迄?屶隔慌峴迄才電麿迄曾嶽迄協庁塀?參式膨嶽音揖議侯宣雫艶。

棒迄

InnoDB徭強殊霞並暦議棒迄?旺指獄匯倖賜叱倖並暦栖契峭棒迄。InnoDB音嬬壓MySQL LOCK TABLES譜協燕迄協議仇圭賜宀膚式InnoDB岻翌議贋刈哈陪譜崔迄協議仇圭殊霞棒迄。低駅倬宥狛譜協innodb_lock_wait_timeout狼由延楚議峙栖盾畳宸乂秤趨。泌惚勣卆真迄吉棋階扮栖盾畳棒迄諒籾?斤噐厚仟並暦畜鹿議哘喘?繍嗤辛嬬擬崑寄楚並暦議迄吉棋?擬崑狼由呟械?侭參音容呪壓匯倖並暦嶄詞栽厚仟音揖贋刈窃侏議燕?匆音容呪屢揖窃侏議燕寡喘音揖議迄協圭塀紗迄。

並暦陣崙

MySQL宥狛SET AUTOCOMMIT, START TRANSACTION, COMMIT才ROLLBACK吉囂鞘屶隔云仇並暦。囂隈?
START TRANSACTION | BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET AUTOCOMMIT = {0 | 1}
潮範秤趨和?mysql頁autocommit議?泌惚俶勣宥狛苧鳩議commit才rollback栖戻住才指獄並暦?椎担俶勣宥狛苧鳩議並暦陣崙凋綜栖蝕兵並暦?宸頁才oracle議並暦砿尖苧?垈四i諜愀州h膵虵τ段粘?oracle方象垂濡卞欺mysql方象垂?夸俶勣鳩隠哘喘嶄頁倦斤並暦序佩阻苧鳩議砿尖。
  1. START TRANSACTION賜BEGIN囂鞘辛參蝕兵匯?仟議並暦。
  2. COMMIT才ROLLBACK喘栖戻住賜宀指獄並暦。
  3. CHAIN才RELEASE徨鞘蛍艶喘栖協吶壓並暦戻住賜宀指獄岻朔議荷恬?chain氏羨軸尼強匯倖仟並麗?旺拝才胡嘉議並暦醤嗤屢揖議侯宣雫艶?release夸氏僅蝕才人薩極議銭俊。
  4. SET AUTOCOMMIT辛參俐個輝念銭俊議戻住圭塀?泌惚譜崔阻SET AUTOCOMMIT=0?夸譜崔岻朔議侭嗤並暦脅俶勣宥狛苧鳩議凋綜序佩戻住賜宀指獄。

泌惚厘断峪頁斤蝶乂囂鞘俶勣序佩並暦陣崙?夸聞喘START TRANSACTION蝕兵匯倖並暦曳熟圭宴?宸劔並暦潤崩岻朔辛參徭強指欺徭強戻住議圭塀?泌惚厘断錬李厘断侭嗤議並暦脅音頁徭強戻住議?椎担宥狛俐個AUTOCOMMIT栖陣崙並暦曳熟圭宴?宸劔音喘壓耽倖並暦蝕兵議扮昨壅峇佩START TRANSACTION。

time session_1 session_2
---------------------------------------------------------> mysql> select * from tt3;
Empty set (0.00 sec) mysql> select * from tt3;
Empty set (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into tt3 values('1',1);
Query OK, 1 row affected (0.03 sec)
mysql> select * from tt3;
Empty set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.05 sec)
mysql> select * from tt3;
+------+------+
| id | name |
+------+------+
| 1 | 1.00 |
+------+------+
1 row in set (0.00 sec)
mysql> insert into tt3 values('2',2);
Query OK, 1 row affected (0.04 sec)
宸倖並暦頁梓孚徭強戻住峇佩議
mysql> select * from tt3;
+------+------+
| id | name |
+------+------+
| 1 | 1.00 |
| 2 | 2.00 |
+------+------+
2 rows in set (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tt3 values('3',3);
Query OK, 1 row affected (0.00 sec)

mysql> commit and chain;
Query OK, 0 rows affected (0.05 sec)
徭強蝕兵匯倖仟議並暦
mysql> insert into tt3 values('4',4);
Query OK, 1 row affected (0.00 sec)
mysql> select * from tt3;
+------+------+
| id | name |
+------+------+
| 1 | 1.00 |
| 2 | 2.00 |
| 3 | 3.00 |
+------+------+
3 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.06 sec)
mysql> select * from tt3;
+------+------+
| id | name |
+------+------+
| 1 | 1.00 |
| 2 | 2.00 |
| 3 | 3.00 |
| 4 | 4.00 |
+------+------+
4 rows in set (0.00 sec)

蝕兵匯倖並暦?氏夛撹匯倖咨根議unlock tables瓜峇佩?
time session_1 session_2
---------------------------------------------------------> mysql> select * from tt3;
Empty set (16.65 sec) mysql> select * from tt3;
Empty set (16.65 sec)
mysql> lock table tt3 write;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tt3;
吉棋
mysql> insert into tt3 values('1',1);
Query OK, 1 row affected (0.07 sec) 吉棋
mysql> rollback;
Query OK, 0 rows affected (0.00 sec) 吉棋
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec) 吉棋
mysql> select * from tt3;
+------+------+
| id | name |
+------+------+
| 1 | 1.00 |
+------+------+
1 row in set (37.71 sec)
蝕兵匯倖並暦扮?燕迄瓜瞥慧。
斤lock圭塀紗議燕迄?音嬬宥狛rollback序佩指獄。

咀緩?壓揖匯倖並暦嶄?恷挫音聞喘音揖贋刈哈陪議燕?倦夸rollback扮俶勣斤掲並暦窃侏議燕序佩蒙艶議侃尖?咀葎commit、rollback峪嬬斤並暦窃侏議燕序佩戻住才指獄。
宥械秤趨和?峪斤戻住議並暦射村欺屈序崙議晩崗嶄?徽頁泌惚匯倖並暦嶄淫根掲並暦窃侏議燕?椎担指獄荷恬匆氏瓜芝村欺屈序崙晩崗嶄?參鳩隠掲並暦窃侏燕議厚仟辛參瓜鹸崙欺貫議方象垂嶄。

才oracle議並暦砿尖屢揖?侭嗤議DDL囂鞘頁音嬬指獄議?旺拝何蛍議DDL囂鞘氏夛撹咨塀議戻住。

壓並暦嶄辛參宥狛協吶savepoint?峺協指獄並暦議匯倖何蛍?徽頁音嬬峺協戻住並暦議匯倖何蛍。斤噐鹸墫議哘喘?辛參協吶謹倖音揖議savepoint?諾怎音揖議訳周扮?指獄音揖議savepoint。俶勣廣吭議頁?泌惚協吶阻屢揖兆忖議savepoint?夸朔中協吶議savepoint氏顕固岻念議協吶。斤噐音壅俶勣聞喘議savepoint?辛參宥狛release savepoint凋綜評茅savepoint?評茅朔議savepoint?音嬬壅峇佩rollback to savepoint凋綜。

和中厘断箭徨祥頁庁亭指獄並暦議匯倖何蛍?宥狛協吶savepoint栖峺協俶勣指獄議並暦議了崔。
time session_1 session_2
---------------------------------------------------------> mysql> select * from tt3;
+------+------+
| id | name |
+------+------+
| 2 | 2.00 |
| 3 | 3.00 |
| 4 | 4.00 |
+------+------+
3 rows in set (0.00 sec) mysql> select * from tt3;
+------+------+
| id | name |
+------+------+
| 2 | 2.00 |
| 3 | 3.00 |
| 4 | 4.00 |
+------+------+
3 rows in set (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from tt3 where id = '2';
Query OK, 1 row affected (0.00 sec)
mysql> select * from tt3;
+------+------+
| id | name |
+------+------+
| 3 | 3.00 |
| 4 | 4.00 |
+------+------+
3 rows in set (0.00 sec) mysql> select * from tt3;
+------+------+
| id | name |
+------+------+
| 2 | 2.00 |
| 3 | 3.00 |
| 4 | 4.00 |
+------+------+
3 rows in set (0.00 sec)
mysql> savepoint test;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from tt3 where id = '3';
Query OK, 1 row affected (0.00 sec)
mysql> select * from tt3;
+------+------+
| id | name |
+------+------+
| 4 | 4.00 |
+------+------+
3 rows in set (0.00 sec) mysql> select * from tt3;
+------+------+
| id | name |
+------+------+
| 2 | 2.00 |
| 3 | 3.00 |
| 4 | 4.00 |
+------+------+
3 rows in set (0.00 sec)
mysql> rollback to savepoint test;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tt3;
+------+------+
| id | name |
+------+------+
| 3 | 3.00 |
| 4 | 4.00 |
+------+------+
2 rows in set (0.00 sec) mysql> select * from tt3;
+------+------+
| id | name |
+------+------+
| 2 | 2.00 |
| 3 | 3.00 |
| 4 | 4.00 |
+------+------+
3 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.05 sec)
mysql> select * from tt3;
+------+------+
| id | name |
+------+------+
| 3 | 3.00 |
| 4 | 4.00 |
+------+------+
2 rows in set (0.00 sec) mysql> select * from tt3;
+------+------+
| id | name |
+------+------+
| 3 | 3.00 |
| 4 | 4.00 |
+------+------+
2 rows in set (0.00 sec)

<貧匯准 和匯准>

AltStyle によって変換されたページ (->オリジナル) /