[MDEV-17514] Please implement FOR UPDATE OF tablename,... and FOR SHARE OF tablename,... Created: 2018-10-22 Updated: 2023-09-29 |
|
| Status: | Open |
| Project: | MariaDB Server |
| Component/s: | Locking |
| Fix Version/s: | None |
| Type: | Task | Priority: | Major |
| Reporter: | Dean Trower | Assignee: | Unassigned |
| Resolution: | Unresolved | Votes: | 2 |
| Labels: | compat80 | ||
| Issue Links: |
|
||||||||
| Description |
|
MySQL v8 has implemented enhanced control of locking in SELECT statements with joins, whereby you can lock some but not all tables in the join with FOR UPDATE OF <table>,<table>... and/or FOR SHARE OF <table>,<table>... (the latter equivalent to taking locks as in 'LOCK IN SHARE MODE'). This is very useful, because (for example) in a long running transaction that performs an update, you might often have joins with tables that aren't being updated, and these might be used by many other simultaneous sessions that you don't want to block. So it's important to only lock "FOR UPDATE" those tables actually being updated. You can try doing this with subqueries, but it's very cumbersome. Please implement the "FOR UPDATE OF ..." and "FOR SHARE OF ..." syntax! Thanks. |
| Comments |
| Comment by Roger S [ 2022-04-01 ] |
|
It's a bit sad that this is still not supported until now. Quite common use cases are covered by this. |
| Comment by Michael Widenius [ 2023-09-29 ] |
|
Another solution is to not lock rows for tables that are not updated. MVCC will ensure that the rows are not disappearing during the query. |
| Comment by Dean Trower [ 2023-09-29 ] |
|
Michael, that doesn't address the common case where you're issuing a SELECT on some tables that need to be locked for update, because you're planning to update them in a later statement within your transaction, and you need to ensure they don't change in the interim. Or where you need to read-lock certain rows that mustn't change because you plan to insert data that will reference them as foreign keys. There are times when the default MVCC is NOT what you need for certain specific tables (but might still efficiently be used for others in the same query). |