MyCATApache/Mycat-Server · error · SQLNonTransientException

lock type must be write or read

Error message

lock type must be write or read

What it means

DruidLockTableParser only supports MySQL lock types WRITE and READ on the first lock item. Any other LockType (e.g. LOW_PRIORITY WRITE aliases the parser doesn't accept, or FOLD/other parse results) causes SQLNonTransientException 'lock type must be write or read'.

Solutions

1. Rewrite the statement to plain `LOCK TABLES t WRITE` or `LOCK TABLES t READ`
2. Remove LOW_PRIORITY/other modifiers before the lock keyword
3. If you need priority semantics, apply them at the MySQL node directly rather than through MyCat

Example fix

// before
LOCK TABLES t LOW_PRIORITY WRITE;
// after
LOCK TABLES t WRITE;

if (!sql.matches("(?is)\\s*lock\\s+tables?\\s+\\S+\\s+(READ|WRITE)\\s*;?")) throw new IllegalArgumentException("only READ/WRITE lock types supported");

try { route(sql); } catch (SQLNonTransientException e) { if (e.getMessage().equals("lock type must be write or read")) { rewriteToPlainLock(sql); } else throw e; }

When it happens

Trigger: statementParse on LOCK TABLES ... where items.get(0).getLockType() is neither LockType.WRITE nor LockType.READ — e.g. `LOCK TABLES t LOW_PRIORITY WRITE` or an unparsed lock qualifier.

Common situations: Scripts using LOW_PRIORITY/other MySQL lock modifiers; client drivers emitting extended lock syntax; copy-pasted Oracle/Postgres lock statements.

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

• You have an error in your SQL syntax, don't support lock…
• can't lock multi-table
• 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/9a8fa456eac61910. 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)