apache/shardingsphere · error · MCPLockingReadStatementException

Locking read statements such as SELECT ... FOR UPDATE are no

Error message

Locking read statements such as SELECT ... FOR UPDATE are not supported by the MCP read-only contract.

What it means

Thrown by SQLStatementSafetyValidator.checkStatement (MCPLockingReadStatementException) when a SelectStatement has a lock clause. SELECT ... FOR UPDATE / FOR SHARE / LOCK IN SHARE MODE takes row locks, which conflicts with the MCP read-only contract, so the validator rejects it with a distinct, more specific error than a generic ban.

Source

Thrown at mcp/core/src/main/java/org/apache/shardingsphere/mcp/core/tool/handler/execute/SQLStatementSafetyValidator.java:91

            String text = each.getText().trim();
            if (text.startsWith("/*!") || text.toUpperCase(Locale.ENGLISH).startsWith("/*M!")) {
                return true;
            }
        }
        return false;
    }
    
    private void checkStatement(final SQLStatement sqlStatement) {
        if (isBannedStatementType(sqlStatement) || containsExecutableComment(sqlStatement)) {
            throw new MCPBannedSQLStatementException();
        }
        if (sqlStatement instanceof SelectStatement) {
            SelectStatement select = (SelectStatement) sqlStatement;
            if (select.getInto().isPresent() || select.getOutfile().isPresent()) {
                throw new MCPBannedSQLStatementException();
            }
            if (select.getLock().isPresent()) {
                throw new MCPLockingReadStatementException();
            }
        }
    }
    
    private void checkExpression(final ExpressionSegment expression) {
        if (expression instanceof FunctionSegment) {
            checkFunction((FunctionSegment) expression);
        } else if (expression instanceof BinaryOperationExpression && ":=".equals(((BinaryOperationExpression) expression).getOperator())) {
            throw new MCPBannedSQLStatementException();
        } else if (expression instanceof ColumnSegment) {
            ColumnSegment column = (ColumnSegment) expression;
            if (column.getOwner().isPresent() && "NEXTVAL".equalsIgnoreCase(column.getIdentifier().getValue())) {
                throw new MCPBannedSQLStatementException();
            }
        }
    }
    
    private void checkFunction(final FunctionSegment function) {

View on GitHub (pinned to e952770a21)

Solutions

  1. Drop the lock clause and rely on the tool's read-only semantics for inspection
  2. If you need atomic claim/update behavior, move it to an application-side transaction over a normal JDBC connection, or use a transaction-capable MCP path explicitly designed for it
  3. Add optimistic concurrency (version column) instead of pessimistic locking for MCP-driven workflows

Example fix

-- before
SELECT * FROM t_order WHERE status='NEW' LIMIT 1 FOR UPDATE;

-- after
SELECT * FROM t_order WHERE status='NEW' LIMIT 1;
Defensive patterns

Strategy: validation

Validate before calling

String upper = sql.toUpperCase(Locale.ENGLISH);
if (upper.endsWith("FOR UPDATE") || upper.endsWith("FOR SHARE") || upper.endsWith("LOCK IN SHARE MODE")
        || upper.matches("(?s).*\\bFOR (NO KEY )?(UPDATE|SHARE)\\b.*")) {
    throw new IllegalArgumentException("Remove lock clauses for MCP read-only access");
}

Try / catch

try {
    executeSql(sql);
} catch (MCPLockingReadStatementException e) {
    executeSql(stripLockClause(sql)); // retry without FOR UPDATE/SHARE
}

Prevention

When it happens

Trigger: select.getLock().isPresent() on a parsed SelectStatement — any trailing lock clause such as `SELECT * FROM t WHERE id=1 FOR UPDATE`, `FOR SHARE`, `FOR NO KEY UPDATE`, or MySQL `LOCK IN SHARE MODE`.

Common situations: Reusing ORM-generated pessimistic-lock queries (Hibernate/MyBatis `FOR UPDATE` mappings) through the MCP execute tool; porting application transaction code that reserves rows; agents emulating 'claim next job' patterns via a locked read.

Related errors

AI-assisted analysis of apache/shardingsphere@e952770a21 (2026-08-14). Data as JSON: /api/errors/98a50db90f738ef4. Report an issue: GitHub.