Table of Contents

Class SceneService

Namespace
FishMMO.Database.Npgsql.Services
Assembly
FishMMO-DB.dll

Base class for database services that execute EF Core operations with consistent execution behavior (transactional and read-only), retry behavior for transient failures, and standardized error mapping into DatabaseResult.

public sealed class SceneService : BaseService<SceneEntity>, ISceneService, IFetchByKeyAction<long, SceneData>, IFetchManyByKeyAction<long, SceneData>
Inheritance
SceneService
Implements
Inherited Members

Remarks

This base type provides three primary execution paths:

  • ExecuteTransactionAsync(Func<Task>, CancellationToken) and ExecuteTransactionAsync<TResult>(Func<Task<TResult>>,string,CancellationToken) create a fresh NpgsqlDbContext, begin an explicit transaction, execute the delegate, then call SaveChangesAsync(CancellationToken) and commit.
  • ExecuteWriteAsync(Func<Task>,string,CancellationToken) and ExecuteWriteAsync<TResult>(Func<Task<TResult>>,string,CancellationToken) create a fresh NpgsqlDbContext, execute the delegate, then call SaveChangesAsync(CancellationToken) without starting an explicit transaction.
  • ExecuteReadAsync(Func<Task>,string,CancellationToken) and ExecuteReadAsync<TResult>(Func<Task<TResult>>,string,CancellationToken) create a fresh NpgsqlDbContext but do not start an explicit transaction and do not call SaveChanges.

Standalone Execution (no ambient scope): A new context is created per attempt to avoid EF change-tracker state leaking across retries. Transient database failures are retried with exponential backoff. Optimistic concurrency conflicts (Version-based authority) and StaleStateException are never retried; they are returned as non-transient failures so the caller can re-read and decide how to proceed.

Ambient Scope Execution (inside an existing Unit of Work): When an operation detects an active FishMMO.Database.Npgsql.Services.DatabaseExecutionScope, it reuses the ambient NpgsqlDbContext and does NOT retry on transient failures. This is by design for the following reasons:

  1. Transaction State Corruption: PostgreSQL aborts the entire transaction on most transient failures. The connection and transaction become unusable, making retry with the same context impossible.
  2. Context State Pollution: The DbContext's change tracker accumulates state. Retrying with a polluted change tracker can cause duplicate key violations or incorrect updates.
  3. Semantic Correctness: If operation A succeeded and operation B failed transiently, retrying B alone without re-evaluating A's preconditions could violate business invariants.

When a transient failure occurs inside an ambient scope, the failure is returned immediately so the caller can restart the entire unit of work with fresh state. Savepoints are used for nested atomicity but cannot recover from connection-level failures.

Constructors

SceneService(INpgsqlDbContextFactory)

Initializes a new instance of SceneService.

public SceneService(INpgsqlDbContextFactory dbContextFactory)

Parameters

dbContextFactory INpgsqlDbContextFactory

DbContext factory for creating contexts.

Exceptions

ArgumentNullException

Thrown when dbContextFactory is null.

Methods

DeleteAsync(long, CancellationToken)

Deletes a single scene row by its database ID.

public Task<DatabaseResult> DeleteAsync(long sceneId, CancellationToken cancellationToken = default)

Parameters

sceneId long

Scene row to delete.

cancellationToken CancellationToken

Cancellation token.

Returns

Task<DatabaseResult>

DatabaseResult indicating success, or NotFound when the row is already gone.

Remarks

The row id is the only identifier for a scene instance that means the same thing in every process, so it is the only way to address one. A DeleteByHandleAsync keyed on (scene_server_id, scene_handle) used to sit alongside this: it had no callers left after scene identity moved to the row id, and leaving it in the interface only invited a caller to reintroduce a process-local handle as a cross-process key.

Idempotent — deleting a row that is already gone succeeds, because every caller is removing something it has already stopped using.

DeleteBySceneServerAsync(long, CancellationToken)

Deletes all scenes for a scene server.

public Task<DatabaseResult<int>> DeleteBySceneServerAsync(long sceneServerId, CancellationToken cancellationToken = default)

Parameters

sceneServerId long

Scene server ID.

cancellationToken CancellationToken

Cancellation token.

Returns

Task<DatabaseResult<int>>

DatabaseResult containing number of scenes deleted on success, or error information on failure.

Remarks

Uses ExecuteSqlRawAsync with execution strategy wrapping to ensure transient database failures are automatically retried. Returns 0 rows deleted if no scenes exist (idempotent).

DeleteByStaleSceneServersAsync(long, DateTime, int, CancellationToken)

Deletes this world server's scene rows whose owning scene server has stopped pulsing, or is no longer registered at all.

public Task<DatabaseResult<int>> DeleteByStaleSceneServersAsync(long worldServerId, DateTime pulseOlderThanUtc, int maxRows = 256, CancellationToken cancellationToken = default)

Parameters

worldServerId long

World server whose rows to reap.

pulseOlderThanUtc DateTime

A scene server that has not pulsed since this instant is treated as gone.

maxRows int

Upper bound on rows removed in one call.

cancellationToken CancellationToken

Cancellation token.

Returns

Task<DatabaseResult<int>>

DatabaseResult containing the number of rows deleted.

Remarks

A scene server only deletes its own rows on a graceful shutdown, so a crash leaves every scene it hosted advertised as Ready forever. Those rows are actively harmful rather than merely stale: the world server routes players to them, sending clients to an address that either refuses them or — once a replacement scene server reuses the port — answers as a server that does not have the scene. Either way the client is bounced back, re-routed from the same row, and bounced again, with nothing in the loop that ages out.

Rows whose scene_server_id is 0 are skipped: those are queued or loading scenes that have not been assigned a host yet, and belong to DeleteStaleUnreadyAsync(long, DateTime, int, CancellationToken).

DeleteByWorldServerAsync(long, CancellationToken)

Deletes all scenes for a world server.

public Task<DatabaseResult<int>> DeleteByWorldServerAsync(long worldServerId, CancellationToken cancellationToken = default)

Parameters

worldServerId long

World server ID.

cancellationToken CancellationToken

Cancellation token.

Returns

Task<DatabaseResult<int>>

DatabaseResult containing number of scenes deleted on success, or error information on failure.

Remarks

Uses ExecuteSqlRawAsync with execution strategy wrapping to ensure transient database failures are automatically retried. Returns 0 rows deleted if no scenes exist (idempotent).

DeleteStaleUnreadyAsync(long, DateTime, int, CancellationToken)

Deletes scene rows for a world server that never reached Ready and are older than olderThanUtc.

public Task<DatabaseResult<int>> DeleteStaleUnreadyAsync(long worldServerId, DateTime olderThanUtc, int maxRows = 256, CancellationToken cancellationToken = default)

Parameters

worldServerId long

World server whose rows to reap.

olderThanUtc DateTime

Rows created strictly before this instant are eligible.

maxRows int

Upper bound on rows removed in one call, so a large backlog is drained across several sweeps rather than in one long transaction.

cancellationToken CancellationToken

Cancellation token.

Returns

Task<DatabaseResult<int>>

DatabaseResult containing the number of rows deleted.

Remarks

Nothing else removes a Pending, Loading or Failed row. That is not merely untidy: such a row keeps its character_id, and a character pointed at one can never finish entering the world. A Loading row orphaned by a scene server that died between dequeue and load still has scene_server_id = 0, so DeleteBySceneServerAsync(long, CancellationToken) does not match it on that server's restart, and it survives indefinitely. Reaping by age is what bounds both.

Ready rows are deliberately untouched: they represent live scene instances and are removed by the scene server that owns them when it unloads them or shuts down.

DequeueAsync(CancellationToken)

Dequeues the next pending scene load request and marks it as loading.

public Task<DatabaseResult<SceneData>> DequeueAsync(CancellationToken cancellationToken = default)

Parameters

cancellationToken CancellationToken

Cancellation token.

Returns

Task<DatabaseResult<SceneData>>

DatabaseResult containing scene data if a pending scene was found and dequeued, or error information on failure.

Remarks

Uses FromSqlRaw with FOR UPDATE SKIP LOCKED and execution strategy wrapping to ensure transient database failures are automatically retried. Atomically updates status from Pending to Loading to prevent race conditions. Returns failure with error code NO_PENDING_SCENES when no pending scenes exist.

EnqueueAsync(long, string, SceneType, long, CancellationToken)

Enqueues a new scene load request.

public Task<DatabaseResult<long>> EnqueueAsync(long worldServerId, string sceneName, SceneType sceneType, long characterId = 0, CancellationToken cancellationToken = default)

Parameters

worldServerId long

World server ID.

sceneName string

Scene name.

sceneType SceneType

Scene type.

characterId long

Character ID (optional, for instances).

cancellationToken CancellationToken

Cancellation token.

Returns

Task<DatabaseResult<long>>

DatabaseResult containing scene ID on success, or error information on failure.

Remarks

Uses SaveChangesAsync with execution strategy wrapping to ensure transient database failures are automatically retried. Uses BaseService execution wrappers for automatic transient failure retry and centralized exception mapping.

EnqueueForPartyAsync(long, string, SceneType, long, long, int, bool, IReadOnlyList<long>, CancellationToken)

Enqueues an instance for a character only while no member of their party already holds a usable one of the same scene.

public Task<DatabaseResult<long>> EnqueueForPartyAsync(long worldServerId, string sceneName, SceneType sceneType, long characterId, long partyId, int difficulty, bool isPrivate, IReadOnlyList<long> partyCharacterIds, CancellationToken cancellationToken = default)

Parameters

worldServerId long

World server the party belongs to.

sceneName string

Instance scene being requested.

sceneType SceneType

Scene type to record on the row.

characterId long

Character the new row is created for.

partyId long

Party that will own the instance, or 0 for an ungrouped character. Recorded on the row and blocked on in addition to the member ids, so the instance stays resolvable by the party even after whoever opened it has left or logged out.

difficulty int

Index into the dungeon's own difficulty list.

isPrivate bool

True to open it hidden from the dungeon finder's public list.

partyCharacterIds IReadOnlyList<long>

Every character whose existing instance should block this insert — the party's members, including the requester. An empty or null list makes this equivalent to EnqueueAsync(long, string, SceneType, long, CancellationToken).

cancellationToken CancellationToken

Cancellation token.

Returns

Task<DatabaseResult<long>>

The new row's ID, or 0 when a party member already holds a Pending, Loading or Ready instance — of any scene — and no row was created. The caller must then look that instance up: join it when it is the dungeon being asked for, refuse otherwise.

Remarks

The dungeon finder searches the party for an existing instance and creates one only if it finds none, but those are two statements with an await between them — and every member of a party clicking the same entrance runs that sequence at the same time, on per-character async workers, potentially on different scene servers. Each one saw no instance, each one created its own, and a party that pressed the button together was split across separate copies of the dungeon: precisely the outcome the party search exists to prevent, in exactly the situation it is needed most.

The existence check and the insert are one statement here, so the losers of the race insert nothing and are told to join the winner's instance instead.

Ready is included in the blocking states as well as Pending and Loading. Unlike the open-world limit — where a running instance says nothing about whether another is needed — a party wants exactly one instance, and a running one is the strongest reason not to make a second.

One instance, not one per dungeon. The blocking check does not match on scene name. Scoped to the name, a party could hold a live copy of every dungeon on the shard at once — open one, walk out, open the next — with each abandoned copy holding a full physics scene and a scene row until its own idle timeout expired.

EnqueueIfUnderOutstandingLimitAsync(long, string, SceneType, int, CancellationToken)

Enqueues a scene load only while fewer than maxOutstanding loads of the same scene are already in flight for this world server.

public Task<DatabaseResult<long>> EnqueueIfUnderOutstandingLimitAsync(long worldServerId, string sceneName, SceneType sceneType, int maxOutstanding = 1, CancellationToken cancellationToken = default)

Parameters

worldServerId long

World server requesting the scene.

sceneName string

Scene to load.

sceneType SceneType

Scene type to record on the row.

maxOutstanding int

How many Pending or Loading rows for this (world, scene, type) may exist at once. The caller derives this from how many connections are actually waiting, so a single waiting player produces one load while a login surge that genuinely needs several instances gets them in parallel. Values below 1 are treated as 1.

cancellationToken CancellationToken

Cancellation token.

Returns

Task<DatabaseResult<long>>

The new row's ID, or 0 when maxOutstanding loads of the same (world, scene, type) are already outstanding and no row was created. Failure results carry the database error.

Remarks

For the world server's open-world routing, which asks for a scene on every routing cycle for as long as anyone is still waiting on it. EnqueueAsync(long, string, SceneType, long, CancellationToken) inserts unconditionally, so a zone that takes twenty seconds to load — an entirely ordinary cold start — collected a fresh request every two seconds while it did. Scene servers dequeue those and load them: ten stacked copies of one open-world zone, each with its own physics scene, each sitting empty and therefore not eligible for stale unload until StaleSceneTimeout (an hour by default) elapsed.

Deliberately not used by the dungeon finder. Concurrent Pending rows for one dungeon name are correct there — they belong to different parties — and that path already dedupes per character and per party.

The count and the insert are one statement, so a second caller cannot slip between them.

FetchAsync(long, CancellationToken)

Fetches an entity for the given key.

public Task<DatabaseResult<SceneData>> FetchAsync(long sceneId, CancellationToken cancellationToken = default)

Parameters

sceneId long
cancellationToken CancellationToken

Token to cancel the operation.

Returns

Task<DatabaseResult<SceneData>>

FetchAvailableAsync(long, string, int, CancellationToken)

Gets list of ready scenes for a world server and scene name with available capacity.

public Task<DatabaseResult<IReadOnlyList<SceneData>>> FetchAvailableAsync(long worldServerId, string sceneName, int maxClients, CancellationToken cancellationToken = default)

Parameters

worldServerId long

World server ID.

sceneName string

Scene name.

maxClients int

Maximum client capacity.

cancellationToken CancellationToken

Cancellation token.

Returns

Task<DatabaseResult<IReadOnlyList<SceneData>>>

DatabaseResult containing list of available scene data matching criteria, or error information on failure.

Remarks

This method uses LINQ (ToListAsync with AsNoTracking) and automatically benefits from the retry policy configured on the DbContext without requiring explicit execution strategy wrapping. Filters by Ready status and character_count less than maxClients. Returns empty list if no scenes match.

FetchCharacterInstanceAsync(long, SceneType, long, string, CancellationToken)

Gets the instance a character opened for one particular scene.

public Task<DatabaseResult<SceneData>> FetchCharacterInstanceAsync(long characterId, SceneType sceneType, long worldServerId, string sceneName, CancellationToken cancellationToken = default)

Parameters

characterId long

Character ID.

sceneType SceneType

Scene type.

worldServerId long

World server the instance must belong to.

sceneName string

Scene the instance must be of.

cancellationToken CancellationToken

Cancellation token.

Returns

Task<DatabaseResult<SceneData>>

DatabaseResult containing scene data if found, or error information on failure.

Remarks

The world and scene are part of the query, not a check the caller applies afterwards. A character accumulates one instance row per dungeon it has opened — nothing deletes a Ready row until its scene goes stale — so matching on character and type alone returned an arbitrary one of them. Asked for dungeon A while holding a row for dungeon B, the caller saw "no instance", created a second row, and left A's still-running instance stranded and unreachable; the character then accumulated another row on every alternation between the two.

Ordered newest-first so the answer is deterministic even where duplicate rows already exist from before this filter, and so the most recently opened instance wins.

Only rows a character can still be placed in — Pending, Loading or Ready — are considered. Every caller already discarded anything else, so this does not change what they see; it changes which row wins the ordering when a character holds several. A Failed row that happened to be newer used to mask a live instance the character owned, and EnqueueForPartyAsync(long, string, SceneType, long, long, int, bool, IReadOnlyList<long>, CancellationToken) blocks on that live row — so the caller could neither be routed to it nor create a replacement until it unloaded on its own.

Returns entity not found exception if no matching instance exists.

FetchCharacterInstancesAsync(IReadOnlyList<long>, SceneType, long, long, CancellationToken)

Fetches every enterable instance owned by any of the given characters on one world server.

public Task<DatabaseResult<IReadOnlyList<SceneData>>> FetchCharacterInstancesAsync(IReadOnlyList<long> characterIds, SceneType sceneType, long worldServerId, long partyId = 0, CancellationToken cancellationToken = default)

Parameters

characterIds IReadOnlyList<long>

Characters to look up; duplicates and non-positive ids are ignored.

sceneType SceneType

Instance type to match, normally SceneType.Group.

worldServerId long

World server the characters belong to.

partyId long

Party to match in addition to the character ids, or 0 to match on characters alone. This is what makes an instance resolvable after its opener has left the party or logged out: without it the remaining members see nothing, open a second copy, and split from anyone still inside — and a member who steps out cannot get back in.

cancellationToken CancellationToken

Cancellation token.

Returns

Task<DatabaseResult<IReadOnlyList<SceneData>>>

The matching rows, newest first. Empty when the characters hold none.

Remarks

The batched form of FetchCharacterInstanceAsync(long, SceneType, long, string, CancellationToken), and the query the dungeon finder actually needs: it has to know whether the party holds an instance at all before it can decide between joining, refusing, and creating — and it used to answer that with one round trip per party member, plus another for the requester.

Only Pending, Loading and Ready rows are returned, matching what EnqueueForPartyAsync(long, string, SceneType, long, long, int, bool, IReadOnlyList<long>, CancellationToken) blocks on, so a caller cannot be refused a creation by a row this does not show it.

FetchJoinableInstancesAsync(long, string, int, SceneType, int, int, CancellationToken)

Lists the instances of one dungeon, at one difficulty, that anybody may join.

public Task<DatabaseResult<IReadOnlyList<SceneData>>> FetchJoinableInstancesAsync(long worldServerId, string sceneName, int difficulty, SceneType sceneType, int maxClients, int maxRows = 32, CancellationToken cancellationToken = default)

Parameters

worldServerId long

World server to search.

sceneName string

Dungeon scene name.

difficulty int

Difficulty index to match.

sceneType SceneType

Instance type, normally SceneType.Group.

maxClients int

Capacity of one instance of this dungeon; rows at or above it are omitted.

maxRows int

Ceiling on rows returned. Clamped to 1..128.

cancellationToken CancellationToken

Cancellation token.

Returns

Task<DatabaseResult<IReadOnlyList<SceneData>>>

Joinable rows, oldest first. Empty when there are none.

Remarks

Backs the dungeon finder's browsable list. Excludes instances the owning party has marked private and instances already at capacity, because neither can be joined and a row whose Join is guaranteed to be refused is worse than no row.

Includes instances that are still Pending or Loading. A party that has just opened a dungeon spends several seconds in those states, which is exactly when a straggler is looking for them — hiding it would show an empty list to somebody whose group is right there, and they would open a second copy.

FetchManyAsync(long, CancellationToken)

Fetches many items for the given key.

public Task<DatabaseResult<IReadOnlyList<SceneData>>> FetchManyAsync(long worldServerId, CancellationToken cancellationToken = default)

Parameters

worldServerId long
cancellationToken CancellationToken

Token to cancel the operation.

Returns

Task<DatabaseResult<IReadOnlyList<SceneData>>>

PulseAsync(long, int, CancellationToken)

Updates the character count for a scene (heartbeat).

public Task<DatabaseResult> PulseAsync(long sceneId, int characterCount, CancellationToken cancellationToken = default)

Parameters

sceneId long

Scene row to update.

characterCount int

Current character count.

cancellationToken CancellationToken

Cancellation token.

Returns

Task<DatabaseResult>

DatabaseResult indicating success or error information on failure.

Remarks

Uses ExecuteSqlRawAsync with execution strategy wrapping to ensure transient database failures are automatically retried. Returns entity not found exception if no scene matches.

Addressed by row id rather than by scene handle. A scene handle is the owning process's own identifier for a loaded scene and is not unique anywhere else, so two scene servers that happened to allocate the same handle overwrote each other's population on every pulse — and that population is the number the world server routes and load-balances on.

PulseBatchAsync(List<(long sceneId, int characterCount)>, int, CancellationToken)

Updates the character count for multiple scenes in a single batched operation.

public Task<DatabaseResult<int>> PulseBatchAsync(List<(long sceneId, int characterCount)> pulses, int maxBatchSize = 1000, CancellationToken cancellationToken = default)

Parameters

pulses List<(long sceneId, int characterCount)>

List of (sceneId, characterCount) pairs to update.

maxBatchSize int

Maximum number of scenes per database round-trip (500–2500).

cancellationToken CancellationToken

Cancellation token.

Returns

Task<DatabaseResult<int>>

The total number of rows affected across all batches.

Remarks

Addressed by row id for the reason given on PulseAsync(long, int, CancellationToken).

SetInstancePrivacyAsync(long, long, long, bool, CancellationToken)

Shows or hides one instance in the dungeon finder's public list.

public Task<DatabaseResult<bool>> SetInstancePrivacyAsync(long sceneId, long requiredPartyId, long requiredCharacterId, bool isPrivate, CancellationToken cancellationToken = default)

Parameters

sceneId long

Instance row to change.

requiredPartyId long

Party that must own the row, or 0 for an ungrouped instance.

requiredCharacterId long

Character that must own an ungrouped row.

isPrivate bool

True to hide it from the list, false to offer it.

cancellationToken CancellationToken

Cancellation token.

Returns

Task<DatabaseResult<bool>>

True when a row was actually updated; false when the caller does not own it.

Remarks

Ownership is re-asserted inside the UPDATE rather than checked beforehand, so an authorisation that went stale between the caller's roster read and this write updates nothing instead of flipping another party's dungeon.

SetReadyAsync(long, long, long, string, int, CancellationToken)

Sets the loading scene identified by sceneId to ready status, recording which scene server hosts it and under which runtime handle.

public Task<DatabaseResult> SetReadyAsync(long sceneId, long sceneServerId, long worldServerId, string sceneName, int sceneHandle, CancellationToken cancellationToken = default)

Parameters

sceneId long

Database ID of the scene row being made ready. This is the row the caller dequeued.

sceneServerId long

Scene server ID.

worldServerId long

World server ID.

sceneName string

Scene name, validated against the row as a consistency check.

sceneHandle int

The hosting process's scene-manager handle, recorded for diagnostics only. Instances are identified across processes by sceneId.

cancellationToken CancellationToken

Cancellation token.

Returns

Task<DatabaseResult>

DatabaseResult indicating success or error information on failure.

Remarks

Only updates the named scene while it is in Loading status; the update is idempotent for a row already made ready by the same server and handle (in-call retry safety).

The row is addressed by ID rather than by (world, name) ordering. Ordering was ambiguous whenever two rows for the same scene name were loading at once, so the server/handle of one load could be written onto the other row. For instanced scenes that row also carries character_id, so the mix-up handed a character the scene instance created for somebody else.

UpdateStatusAsync(long, SceneStatus, CancellationToken)

Updates the status of a scene.

public Task<DatabaseResult> UpdateStatusAsync(long sceneId, SceneStatus status, CancellationToken cancellationToken = default)

Parameters

sceneId long

Scene ID.

status SceneStatus

New scene status.

cancellationToken CancellationToken

Cancellation token.

Returns

Task<DatabaseResult>

DatabaseResult indicating success or error information on failure.

Remarks

Uses ExecuteSqlRawAsync with execution strategy wrapping to ensure transient database failures are automatically retried. Returns entity not found exception if scene doesn't exist.