marcopaggioro commented on code in PR #366:
URL:
https://github.com/apache/pekko-persistence-jdbc/pull/366#discussion_r2529876185
##########
core/src/main/scala/org/apache/pekko/persistence/jdbc/state/SequenceNextValUpdater.scala:
##########
@@ -90,6 +90,18 @@ import slick.sql.SqlStreamingAction
String]
}
+/**
+ * INTERNAL API
+ */
+@InternalApi private[jdbc] final class MySQLSequenceNextValUpdater(profile:
JdbcProfile)
+ extends SequenceNextValUpdater {
+
+ import profile.api._
+
+ def getSequenceNextValueExpr() =
+ sql"""SELECT LAST_INSERT_ID()""".as[String]
Review Comment:
I'm not 100% sure because this research was done in the last few days, but:
LAST_INSERT_ID() returns the last auto-increment value generated on that
connection, regardless of which table it came from. The potential problem is
exactly this “regardless of table”.
That’s why it is always used inside a single transaction:
https://github.com/apache/pekko-persistence-jdbc/pull/366/files#diff-4c8d085a2c92e80ec36d8077607be85b8b7edd1c379bf939d35454523407218aR105
The transaction guarantees that the REPLACE and the SELECT queries are
executed on the same connection.
The flow:
- `transactionally` takes a connection from the pool;
- performs the entire action (REPLACE + SELECT) on that connection;
- during that action, no other DBIOAction can use the same connection in
parallel;
- then releases the connection back to the pool.
So, within a single invocation of this action:
- you will never have another INSERT/REPLACE (on any table) between your
REPLACE and your SELECT LAST_INSERT_ID() on the same connection;
- therefore, LAST_INSERT_ID() will always refer to the REPLACE we just
performed.
Concurrency between multiple requests:
- Multiple connections: each has its own LAST_INSERT_ID(), so there is no
interference.
- Same connection: with Slick, we never have two transactions using the same
Connection in parallel, so there is no risk of “mixing up” LAST_INSERT_ID()
values between different actions.
BTW
As an alternative, we could replace `SELECT LAST_INSERT_ID()` with something
like:
```SELECT id FROM durable_state_global_offset```
This would still be safe in terms of concurrency, because the SELECT is
executed in the same transaction, on the same connection, immediately after the
REPLACE. The transaction guarantees that we see the row (and id) that was just
written by our REPLACE, regardless of concurrent activity on other connections.
--
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]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
