From 03d5e37cfda5da9dab8ae00aa682521f8ab9190b Mon Sep 17 00:00:00 2001 From: JetDrag Date: Tue, 4 Nov 2025 22:03:37 +0800 Subject: [PATCH] feat: support MySQL FOR SHARE with OF, NOWAIT, SKIP LOCKED option through with_for_update(). --- doc/build/changelog/unreleased_20/12964.rst | 8 ++++ lib/sqlalchemy/dialects/mysql/base.py | 10 ++++- test/dialect/mysql/test_for_update.py | 44 +++++++++++++++++++-- 3 files changed, 57 insertions(+), 5 deletions(-) create mode 100644 doc/build/changelog/unreleased_20/12964.rst diff --git a/doc/build/changelog/unreleased_20/12964.rst b/doc/build/changelog/unreleased_20/12964.rst new file mode 100644 index 0000000000..12073ec28c --- /dev/null +++ b/doc/build/changelog/unreleased_20/12964.rst @@ -0,0 +1,8 @@ +.. change:: + :tags: bug, mysql + :tickets: 12964 + + Resolved MySQL 8 ``FOR SHARE`` compilation so that + :meth:`_sql.Select.with_for_update` emits ``FOR SHARE`` (with ``OF``, + ``NOWAIT`` and ``SKIP LOCKED`` modifiers) when supported, while maintaining + ``LOCK IN SHARE MODE`` for older MySQL versions. diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py index 1c51302ba2..de5f0ccd39 100644 --- a/lib/sqlalchemy/dialects/mysql/base.py +++ b/lib/sqlalchemy/dialects/mysql/base.py @@ -1781,7 +1781,10 @@ class MySQLCompiler(compiler.SQLCompiler): ) -> str: assert select._for_update_arg is not None if select._for_update_arg.read: - tmp = " LOCK IN SHARE MODE" + if self.dialect.supports_for_share: + tmp = " FOR SHARE" + else: + tmp = " LOCK IN SHARE MODE" else: tmp = " FOR UPDATE" @@ -2735,6 +2738,7 @@ class MySQLDialect(default.DefaultDialect): sequences_optional = False supports_for_update_of = False # default for MySQL ... + supports_for_share = False # default for MySQL ... # ... may be updated to True for MySQL 8+ in initialize() _requires_alias_for_on_duplicate_key = False # Only available ... @@ -3194,6 +3198,10 @@ class MySQLDialect(default.DefaultDialect): self._is_mysql and self.server_version_info >= (8,) ) + self.supports_for_share = ( + self._is_mysql and self.server_version_info >= (8,) + ) + self._needs_correct_for_88718_96365 = ( not self.is_mariadb and self.server_version_info >= (8,) ) diff --git a/test/dialect/mysql/test_for_update.py b/test/dialect/mysql/test_for_update.py index 5c26d8eb6d..e9594108dd 100644 --- a/test/dialect/mysql/test_for_update.py +++ b/test/dialect/mysql/test_for_update.py @@ -196,6 +196,10 @@ class MySQLForUpdateCompileTest(fixtures.TestBase, AssertsCompiledSQL): for_update_of_dialect = mysql.dialect() for_update_of_dialect.server_version_info = (8, 0, 0) for_update_of_dialect.supports_for_update_of = True + for_update_of_dialect.supports_for_share = True + for_share_dialect = mysql.dialect() + for_share_dialect.server_version_info = (8, 0, 0) + for_share_dialect.supports_for_share = True def test_for_update_basic(self): self.assert_compile( @@ -215,6 +219,16 @@ class MySQLForUpdateCompileTest(fixtures.TestBase, AssertsCompiledSQL): "FROM mytable WHERE mytable.myid = %s LOCK IN SHARE MODE", ) + def test_for_update_read_mysql80(self): + self.assert_compile( + self.table1.select() + .where(self.table1.c.myid == 7) + .with_for_update(read=True), + "SELECT mytable.myid, mytable.name, mytable.description " + "FROM mytable WHERE mytable.myid = %s FOR SHARE", + dialect=self.for_share_dialect, + ) + def test_for_update_skip_locked(self): self.assert_compile( self.table1.select() @@ -235,6 +249,17 @@ class MySQLForUpdateCompileTest(fixtures.TestBase, AssertsCompiledSQL): "LOCK IN SHARE MODE SKIP LOCKED", ) + def test_for_update_read_skip_locked_mysql80(self): + self.assert_compile( + self.table1.select() + .where(self.table1.c.myid == 7) + .with_for_update(read=True, skip_locked=True), + "SELECT mytable.myid, mytable.name, mytable.description " + "FROM mytable WHERE mytable.myid = %s " + "FOR SHARE SKIP LOCKED", + dialect=self.for_share_dialect, + ) + def test_for_update_nowait(self): self.assert_compile( self.table1.select() @@ -255,6 +280,17 @@ class MySQLForUpdateCompileTest(fixtures.TestBase, AssertsCompiledSQL): "LOCK IN SHARE MODE NOWAIT", ) + def test_for_update_read_nowait_mysql80(self): + self.assert_compile( + self.table1.select() + .where(self.table1.c.myid == 7) + .with_for_update(read=True, nowait=True), + "SELECT mytable.myid, mytable.name, mytable.description " + "FROM mytable WHERE mytable.myid = %s " + "FOR SHARE NOWAIT", + dialect=self.for_share_dialect, + ) + def test_for_update_of_nowait(self): self.assert_compile( self.table1.select() @@ -340,7 +376,7 @@ class MySQLForUpdateCompileTest(fixtures.TestBase, AssertsCompiledSQL): .with_for_update(read=True, of=self.table1, nowait=True), "SELECT mytable.myid, mytable.name, mytable.description " "FROM mytable WHERE mytable.myid = %s " - "LOCK IN SHARE MODE OF mytable NOWAIT", + "FOR SHARE OF mytable NOWAIT", dialect=self.for_update_of_dialect, ) @@ -351,7 +387,7 @@ class MySQLForUpdateCompileTest(fixtures.TestBase, AssertsCompiledSQL): .with_for_update(read=True, of=self.table1, skip_locked=True), "SELECT mytable.myid, mytable.name, mytable.description " "FROM mytable WHERE mytable.myid = %s " - "LOCK IN SHARE MODE OF mytable SKIP LOCKED", + "FOR SHARE OF mytable SKIP LOCKED", dialect=self.for_update_of_dialect, ) @@ -366,7 +402,7 @@ class MySQLForUpdateCompileTest(fixtures.TestBase, AssertsCompiledSQL): ), "SELECT mytable.myid, mytable.name, mytable.description " "FROM mytable WHERE mytable.myid = %s " - "LOCK IN SHARE MODE OF mytable NOWAIT", + "FOR SHARE OF mytable NOWAIT", dialect=self.for_update_of_dialect, ) @@ -377,7 +413,7 @@ class MySQLForUpdateCompileTest(fixtures.TestBase, AssertsCompiledSQL): .with_for_update(read=True, of=self.table1), "SELECT mytable.myid, mytable.name, mytable.description " "FROM mytable WHERE mytable.myid = %s " - "LOCK IN SHARE MODE OF mytable", + "FOR SHARE OF mytable", dialect=self.for_update_of_dialect, ) -- 2.47.3