MyCATApache/Mycat-Server · error · SQLNonTransientException

"lock type must be write or read"

Error message

"lock type must be write or read"

What it means

After resolving the table config, the LOCK TABLES parser validates that each lock item's type is WRITE or READ. Any other MySQL lock type (e.g. LOW_PRIORITY WRITE, or multi-item statements with unusual lock types) is rejected with a SQLNonTransientException because Mycat cannot map it to a backend route.

Solutions

  1. Rewrite the statement to plain `LOCK TABLES tbl WRITE` or `LOCK TABLES tbl READ` (strip LOW_PRIORITY / READ LOCAL modifiers)
  2. Strip or translate lock statements in the client/driver before they reach Mycat
  3. Configure Mycat's ignoreUnknownCommand / SQL firewall or route locking statements to a backend directly

Example fix

-- before
LOCK TABLES t_user READ LOCAL;
-- after
LOCK TABLES t_user READ;
Defensive patterns

Strategy: validation

Validate before calling

String sql = sql.trim().toUpperCase();
if (sql.startsWith("LOCK TABLES") && (sql.contains("READ LOCAL") || sql.contains("LOW_PRIORITY"))) {
    sql = sql.replace("READ LOCAL", "READ").replace("LOW_PRIORITY ", "");
}

Try / catch

try { route(...); } catch (SQLNonTransientException e) { if (e.getMessage().contains("lock type must be")) { rewritePlainLock(); } else throw e; }

Prevention

When it happens

Trigger: Executing `LOCK TABLES ... LOW_PRIORITY WRITE`, `... READ LOCAL`, or any lock type other than plain WRITE/READ against a Mycat-configured sharding table.

Common situations: Apps or dump tools emitting extended lock syntax (mysqldump uses `LOCK TABLES ... READ LOCAL`); ORM-generated session locking statements using priority modifiers; scripts written for standalone MySQL run unchanged through Mycat.

Understand the failure class

Background: Invalid enum value errors: "Unknown type", "Invalid scope", "must be one of" — when a string is not on the library's allowed list — this error's family across 23 libraries.

Related errors

AI-assisted analysis of MyCATApache/Mycat-Server@65f8d8beb7 (2026-09-11). Data as JSON: /api/errors/4a07e4ed5bf340c5. Report an issue: GitHub.

Appendix: source

Thrown at src/main/java/io/mycat/route/parser/druid/impl/DruidLockTableParser.java:39

 * @author songdabin
 */
public class DruidLockTableParser extends DefaultDruidParser implements DruidParser {
	@Override
	public void statementParse(SchemaConfig schema, RouteResultset rrs, SQLStatement stmt)
			throws SQLNonTransientException {
		MySqlLockTableStatement lockTableStat = (MySqlLockTableStatement)stmt;
		String table = lockTableStat.getItems().get(0).getTableSource().toString().toUpperCase();
		TableConfig tableConfig = schema.getTables().get(table);
		if (tableConfig == null) {
			String msg = "can't find table define of " + table + " in schema:" + schema.getName();
			LOGGER.warn(msg);
			throw new SQLNonTransientException(msg);
		}
		LockType lockType = lockTableStat.getItems().get(0).getLockType();
		if (LockType.WRITE != lockType && LockType.READ != lockType) {
			String msg = "lock type must be write or read";
			LOGGER.warn(msg);
			throw new SQLNonTransientException(msg);
		}
		List<String> dataNodes = tableConfig.getDataNodes();
		RouteResultsetNode[] nodes = new RouteResultsetNode[dataNodes.size()];
		for (int i = 0; i < dataNodes.size(); i ++) {
			nodes[i] = new RouteResultsetNode(dataNodes.get(i), ServerParse.LOCK, stmt.toString());
		}
		rrs.setNodes(nodes);
		rrs.setFinishedRoute(true);
	}
	
	@Override
	public void visitorParse(SchemaConfig schema, RouteResultset rrs, SQLStatement stmt, MycatSchemaStatVisitor visitor)
			throws SQLNonTransientException {
		// 对于lock tables table1 write, table2 read类型的多表锁语句，DruidParser只能解析出table1，
		// 由于多表锁在分布式场景处理逻辑繁琐，且应用场景较少，因此在此处对这种锁表语句进行拦截。
		// 多表锁的语句在语义上会有","，这里以此为判断依据
		String sql = rrs.getStatement();
		sql = sql.replaceAll("\n", " ").replaceAll("\t", " ");

View on GitHub (pinned to 65f8d8beb7)