MySQL事务控制和锁定语句

260人浏览 2023-10-23 15:37:22

MySQL支持对MyISAM和memeory存储引擎的表进行表级锁定，对BDB的表进行页级锁定，对InnoDB的表进行行级锁定。默认表锁和行锁是自动获得的，但是某些情况用户需要明确地进行锁表或者进行事务的控制，以便保证事务的完整性。

LOCK TABLE和UNLOCK TABLE

LOCK TABLE可用来锁定用于当前线程的表。如果表被其他线程锁定，则当前线程就会等待，知道获得锁定。
UNLOCK TABLE可以释放当前线程获得的任何锁定。当前线程执行另一个LOCK TABLE时，或当与服务器的连接关闭时，所有被当前线程锁定的表被隐含的解锁。

LOCKTABLES
	tbl_name[AS alias]{READ[LOCAL] | [LOW_PRIORITY] WRITE}
	[,tbl_name[AS alias]{READ[LOCAL] | [LOW_PRIORITY] WRITE}]...
UNLOCK TABLES

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来提交和回滚事务，那么就需要通过明确的事务控制命令来开始事务。

START TRANSACTION或BEGIN语句可以开始一项新的事务。
COMMIT和ROLLBACK用来提交或者回滚事务
CHAIN 和RELEASE子句分别用来定义在事务提交或者回滚之后的操作，CHAIN会立即启动一个新事务，并且和刚才的事务具有相同的隔离级别，RELEASE则会断开和客户端的连接。
SET AUTOCOMMIT 自动提交。
如果知识对某些语句需要进行事务控制，那么用START TRANSACTION开始一个事务更好，事务结束后会自动回到自动提交的状态。如果希望所有的事务都不是自动提交的，修改SET AUTOCOMMIT = 0更好。

同一个事务中最好不要使用不同引擎的表，否则rollback时需要对非事务类型的表进行特殊处理。
已验证

当前分布式事务只支持InnoDB引擎。

资源管理器（RM）
事务管理器（TM）
MySQL执行XA MySQL时，MySQL服务相当于一个RM，客户端相当于TM。
第一阶段：所有分支被预备好。即它们被TM告知要准备提交。通常意味着每个RM会记录对于被稳定保存的分支的行动。分支指示是否可以这样做，结果用于第二阶段。
第二阶段：TM告知RMs是否要提交或者回滚。如果第一阶段的结果是它们都能被提交，则所有分支被指示提交。如果第一阶段的结果是它们有任何分支不能提交，则所有分支被告知回滚。
单一分支时，第一阶段预备和提交。

XA {START|BEGIN}xid[JOIN|RESUME]

XA START xid用于启动一个带给定xid值的XA事务。xid唯一。

XA END xid [SUSPEND [FOR MIGRATE]]
XA PREPARE xid

使事务进入PREPARE状态，也就是两阶段提交的第一阶段。

XA COMMIT xid[ONE PHASE]
XA ROLLBACK xid

用来提交或者回滚具体的分支事务。（第二阶段）

XA RECOVER

返回当前数据库中处于PREPARE状态的分支事务的详细信息。

如果分支事务在达到PREAPRE状态时，数据库异常重新启动，服务器重新启动以后，可以继续对分支事务进行提交或者回滚操作，但是提交的事务没有写binlog，存在一定隐患。（主从不一致）