terrymanu commented on issue #32289:
URL:
https://github.com/apache/shardingsphere/issues/32289#issuecomment-3637677953
### Issue Understanding
- You want ShardingSphere-Proxy (5.5.0) to truly support MySQL LOCK/UNLOCK
TABLES on sharded tables instead of returning 1146 or “unsupported”.
### Root Cause
- Proxy currently lacks sharding semantics for LOCK/UNLOCK TABLES: table
names are not rewritten and locks are not managed across shards, so the logical
table is sent to each shard and the physical DB cannot find it.
### Problem Analysis
- The SQL is parsed as LockStatement/UnlockStatement, but rewrite does not
replace logical table names with physical shards.
- MySQL table locks require the same session/connection to hold the lock.
Proxy uses per-SQL connections in autocommit; even with rewrite, the lock would
be lost once the connection is returned.
- Therefore, to support it we need both table-name rewrite/all-shard
execution and connection retention during the lock lifetime.
### Problem Conclusion (Improvement: support lock)
- Plan highlights:
1. Routing + rewrite: route Lock/Unlock across shards, enumerate all
data nodes, and rewrite logical table names to actual shard tables.
2. Connection lifecycle: after LOCK TABLES, pin the involved backend
connections to the session (like a transaction) until UNLOCK TABLES or session
close; subsequent DML/DQL reuse these connections to keep the lock.
3. UNLOCK: also route/rewrite to all shards, execute, then release
held connections.
4. Tests & docs: add coverage for rewrite and lock effectiveness;
document scope/limitations (e.g., incompatible with cross-database routing).
Reference Implementation Sketch (Java-like pseudocode)
```java
// 1) Add a token generator so Lock/Unlock participates in table rewrite
class LockTableTokenGenerator implements
SQLTokenGenerator<LockStatementContext> {
@Override
public Collection<SQLToken> generateSQLTokens(LockStatementContext
ctx) {
// generate TableToken for each SimpleTableSegment -> actual shard
table
}
}
// register in StandardTokenGeneratorsBuilder /
ShardingTokenGeneratorsBuilder
// 2) Ensure routing covers Lock/Unlock in ShardingRouteEngineFactory
// produce RouteUnits over all shards
// 3) Pin connections during lock
if (sqlStatement instanceof LockStatement) {
connectionSession.getTransactionStatus().setInTransaction(true);
lockContext.bind(routeContext.getRouteUnits()); // remember and retain
backend connections
}
if (sqlStatement instanceof UnlockStatement) {
lockContext.release(); // release/close retained backend connections
connectionSession.getTransactionStatus().setInTransaction(false);
}
```
### Testing Suggestions
- Unit:
- Rewrite: LOCK TABLES t_order WRITE -> LOCK TABLES t_order_0 WRITE,
t_order_1…
- Routing: Lock/Unlock produces correct RouteUnits for shards.
- Connection pinning: Lock + subsequent DML use the same backend
connection; after Unlock, it is released.
- Integration: with two MySQL shards, run LOCK TABLES, then DML, then
UNLOCK TABLES; expect no 1146 and DML succeeds.
### Documentation
- Update official docs (e.g.,
https://shardingsphere.apache.org/document/current/en/features/sharding/appendix/
and the CN counterpart) to describe sharded-table LOCK/UNLOCK TABLES support,
same-session requirement, backend-connection pinning, and incompatibility with
cross-logic-database routing.
### Community Invitation
- Contributions welcome: split PRs into routing/rewrite, connection
pinning, and tests/docs. We’ll gladly help review and validate—thanks in
advance for any PRs!
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
