Details
-
Task
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
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!
(preferably allowing both in the same SELECT, for maximum control).
Thanks.
Attachments
Issue Links
- is part of
-
-
- Open
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Workflow | MariaDB v3 [ 90204 ] | MariaDB v4 [ 130924 ] |
| Labels | compat80 |
| Link | This issue is part of MDEV-28906 [ MDEV-28906 ] |
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.
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).
It's a bit sad that this is still not supported until now. Quite common use cases are covered by this.