for inserts, not really. if you want to guard against duplicate, the pattern is use a savepoint (begin_nested()), catch integrity error, then use the new row if someone else inserted it. this is the more performant option as well as opposed to pessimistic locking.
https://docs.sqlalchemy.org/en/14/orm/session_transaction.html#session-begin-nested includes an example with merge(). The *other* way to do it is to use INSERT..ON CONFLICT (PostgreSQL, SQLite) or INSERT..ON DUPLICATE KEY (MySQL, MariaDB) statements. These are popular choices as well but you need to construct them as statement objects. On Wed, Feb 3, 2021, at 8:49 PM, Vitaly Kruglikov wrote: > How can the query/merge or query/add be performed atomically? What would > happen if between the two calls another process inserted a row with the same > unique key? Wouldn't the `s.merge()` then trigger a > `psycopg2.errors.UniqueViolation` exception (in postgres case) when the new > row insert is flushed? Is there a way to make the execution of the ORM atomic > so that the `s.merge()` would be guaranteed to succeed (barring network > errors, etc.)? > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/d117fb88-2fd5-4006-a60f-424cc25c4bd7n%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/d117fb88-2fd5-4006-a60f-424cc25c4bd7n%40googlegroups.com?utm_medium=email&utm_source=footer>. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/c5176c7b-5c25-4fcd-8891-9f91bf87f554%40www.fastmail.com.
