Atomic upserts and idempotent inserts¶

Version availability

The API on this page is implemented on the Core development branch for the next Core 3.2 release. It is not part of Core 3.1.0. Do not call these methods until the new package version is published and installed.

A unique index is the database-enforced arbiter of idempotency. Do not replace an atomic insert with a separate SELECT followed by INSERT: that introduces a race between workers.

Insert if absent¶

Create a mapped entity using your Entity Manager, setting its mapped identifier and fields. In Swoole workers, pass an existing native connection when the insertion belongs to a larger workflow transaction:

$event = $eventManager->newEntity();
$event->setId($eventUuid)
    ->setEventKey($idempotencyKey)
    ->setStatus('pending');

$inserted = $eventManager->insertIfAbsent($event, $pdo);

if (!$inserted) {
    // The event already exists: do not emit it twice.
}

insertIfAbsent() returns true only if a row was inserted. On a unique-key conflict it returns false and leaves the existing row unchanged. Create a UNIQUE index for the idempotency key. Supplying $pdo leaves transaction ownership with the caller; no ambiguous write is retried after a lost connection.

MySQL affected-row semantics

The implementation expects normal MySQL PDO affected-row behavior. Do not enable PDO::MYSQL_ATTR_FOUND_ROWS, which would make an unchanged duplicate update appear affected. MySQL's ON DUPLICATE KEY handles all unique indexes; it does not select a specific unique constraint. PostgreSQL's ON CONFLICT accepts an explicit conflict target.

Update on conflict¶

$upsert = $eventManager
    ->createUpsertBuilder($event)
    ->onConflict(['eventKey'])
    ->doUpdate(['status']);

$affected = $eventManager->executeUpsertAffectedRows($upsert, $pdo);

The field names eventKey and status are resolved through ORM mapping. Incoming values are passed as bound parameters, not interpolated into SQL.

Database	Insert	Ignored conflict	Updated conflict
MySQL 8.4	1 affected row	0 when unchanged	Usually 2
PostgreSQL	1	0	1

The integer result denotes affected rows, not a cross-database "was inserted" indicator for update mode. Use insertIfAbsent() for portable idempotent insertion.

Safety and lifecycle¶

Existing FOR UPDATE, SKIP LOCKED and CAS update methods remain available.
A supplied connection is never acquired, committed, rolled back or released by the upsert method.
Unknown or unmapped field names are rejected before SQL generation.
Conflicts require real database uniqueness constraints; entity-level validation alone is insufficient.
The atomic operation intentionally does not run entity lifecycle callbacks. Use entity persistence when callbacks are required.
For PostgreSQL generated integer primary keys, follow the existing mapping rules for #[NoLastInsertId] and server-generated keys.

Exceptions and error states¶

Use Small\SwooleEntityManager\Database\Common\Exception\SqlState to distinguish unique violations, foreign-key violations and deadlocks from wrapped driver exceptions. Never automatically retry a non-idempotent write after a lost connection: the first commit outcome may be unknown.