MyCATApache/Mycat-Server · error · SQLNonTransientException

can't lock multi-table

Error message

can't lock multi-table

What it means

Also in visitorParse: after the syntax-tolerant loop over extra lock items, MyCat still logs 'can't lock multi-table' and throws SQLNonTransientException with that message whenever the statement locks more than one table. Multi-table LOCK TABLES is simply not supported for routing.

Solutions

1. Split into separate single-table LOCK TABLES statements
2. Replace locking with transactional isolation (SELECT ... FOR UPDATE) within one transaction
3. Route the statement directly to a single MySQL datanode instead of through MyCat

Example fix

// before
LOCK TABLES a WRITE, b READ;
// after
LOCK TABLES a WRITE;
LOCK TABLES b READ;

int tableCount = countLockItems(sql); if (tableCount > 1) throw new IllegalArgumentException("lock at most one table per statement");

try { route(sql); } catch (SQLNonTransientException e) { if (e.getMessage().equals("can't lock multi-table")) { lockOneTableAtATime(sql); } else throw e; }

When it happens

Trigger: visitorParse on any LOCK TABLES statement that passed the per-item syntax check but contains multiple tables (e.g. `LOCK TABLES a WRITE, b READ`).

Common situations: Application code that mirrors MySQL multi-table locking idioms; DBA scripts run through MyCat; ORMs locking several entities at once.

Understand the failure class

Background: UnsupportedOperationException and "is not supported" errors: when a library deliberately refuses a call — this error's family across 30 libraries.

Related errors

• lock type must be write or read
• You have an error in your SQL syntax, don't support lock…
• can't find table define of in schema:
• TODO:insert into .... select .... not supported!
• all columns in group by clause should be in the selected…

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

Appendix: source

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

		sql = sql.replaceAll("\n", " ").replaceAll("\t", " ");
		String[] stmts = SplitUtil.split(sql, ',', true);
		// 如果命令中存在","，则按多表锁的语句来处理
		if (stmts.length > 1) {
			String tmpStmt = null;
			String tmpWords[] = null;
			for (int i = 1; i < stmts.length; i ++) {
				tmpStmt = stmts[i];
				tmpWords = SplitUtil.split(tmpStmt, ' ', true);
				if (tmpWords.length==2 && ("READ".equalsIgnoreCase(tmpWords[1]) || "WRITE".equalsIgnoreCase(tmpWords[1]))) {
					// 如果符合多表锁的语法，则继续，并在最后提示不能多表锁！
					continue;
				} else {
					// 如果不符合多表锁的语法，则提示语法错误和不能多表锁！
					throw new SQLNonTransientException("You have an error in your SQL syntax, don't support lock multi tables!");
				}
			}
			LOGGER.error("can't lock multi-table");
			throw new SQLNonTransientException("can't lock multi-table");
		}
	}
}

View on GitHub (pinned to 65f8d8beb7)