{"record":{"id":"98a50db90f738ef4","repo":"apache/shardingsphere","slug":"locking-read-statements-such-as-select-for-upd","errorCode":null,"errorMessage":"Locking read statements such as SELECT ... FOR UPDATE are not supported by the MCP read-only contract.","messagePattern":"Locking read statements such as SELECT \\.\\.\\. FOR UPDATE are not supported by the MCP read-only contract\\.","errorType":"exception","errorClass":"MCPLockingReadStatementException","httpStatus":null,"severity":"error","filePath":"mcp/core/src/main/java/org/apache/shardingsphere/mcp/core/tool/handler/execute/SQLStatementSafetyValidator.java","lineNumber":91,"sourceCode":"            String text = each.getText().trim();\n            if (text.startsWith(\"/*!\") || text.toUpperCase(Locale.ENGLISH).startsWith(\"/*M!\")) {\n                return true;\n            }\n        }\n        return false;\n    }\n    \n    private void checkStatement(final SQLStatement sqlStatement) {\n        if (isBannedStatementType(sqlStatement) || containsExecutableComment(sqlStatement)) {\n            throw new MCPBannedSQLStatementException();\n        }\n        if (sqlStatement instanceof SelectStatement) {\n            SelectStatement select = (SelectStatement) sqlStatement;\n            if (select.getInto().isPresent() || select.getOutfile().isPresent()) {\n                throw new MCPBannedSQLStatementException();\n            }\n            if (select.getLock().isPresent()) {\n                throw new MCPLockingReadStatementException();\n            }\n        }\n    }\n    \n    private void checkExpression(final ExpressionSegment expression) {\n        if (expression instanceof FunctionSegment) {\n            checkFunction((FunctionSegment) expression);\n        } else if (expression instanceof BinaryOperationExpression && \":=\".equals(((BinaryOperationExpression) expression).getOperator())) {\n            throw new MCPBannedSQLStatementException();\n        } else if (expression instanceof ColumnSegment) {\n            ColumnSegment column = (ColumnSegment) expression;\n            if (column.getOwner().isPresent() && \"NEXTVAL\".equalsIgnoreCase(column.getIdentifier().getValue())) {\n                throw new MCPBannedSQLStatementException();\n            }\n        }\n    }\n    \n    private void checkFunction(final FunctionSegment function) {","sourceCodeStart":73,"sourceCodeEnd":109,"githubUrl":"https://github.com/apache/shardingsphere/blob/e952770a215630a3659c75d64369168cd3e26b82/mcp/core/src/main/java/org/apache/shardingsphere/mcp/core/tool/handler/execute/SQLStatementSafetyValidator.java#L73-L109","documentation":"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.","triggerScenarios":"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`.","commonSituations":"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.","solutions":["Drop the lock clause and rely on the tool's read-only semantics for inspection","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","Add optimistic concurrency (version column) instead of pessimistic locking for MCP-driven workflows"],"exampleFix":"-- before\nSELECT * FROM t_order WHERE status='NEW' LIMIT 1 FOR UPDATE;\n\n-- after\nSELECT * FROM t_order WHERE status='NEW' LIMIT 1;","handlingStrategy":"validation","validationCode":"String upper = sql.toUpperCase(Locale.ENGLISH);\nif (upper.endsWith(\"FOR UPDATE\") || upper.endsWith(\"FOR SHARE\") || upper.endsWith(\"LOCK IN SHARE MODE\")\n        || upper.matches(\"(?s).*\\\\bFOR (NO KEY )?(UPDATE|SHARE)\\\\b.*\")) {\n    throw new IllegalArgumentException(\"Remove lock clauses for MCP read-only access\");\n}","typeGuard":null,"tryCatchPattern":"try {\n    executeSql(sql);\n} catch (MCPLockingReadStatementException e) {\n    executeSql(stripLockClause(sql)); // retry without FOR UPDATE/SHARE\n}","preventionTips":["Map ORM pessimistic-lock queries away from the MCP path","Prefer optimistic concurrency for read-modify-write workflows"],"tags":["mcp","sql-validation","locking-read","for-update","shardingsphere"],"backgroundTag":null,"analyzedSha":"e952770a215630a3659c75d64369168cd3e26b82","analyzedAt":"2026-08-14T13:54:53.392Z","schemaVersion":2},"datasetVersion":"2026-08-15T22:17:37.221Z"}