157 lines
5.5 KiB
Markdown
157 lines
5.5 KiB
Markdown
# Transactions and Disposal
|
|
|
|
DynamORM manages connections, command pools, and transaction stacks internally.
|
|
|
|
## Connection and Transaction Options
|
|
|
|
`DynamicDatabaseOptions` controls behavior:
|
|
|
|
- `SingleConnection`
|
|
- `SingleTransaction`
|
|
- `ConnectionPooling`
|
|
- `SupportSchema`
|
|
- `SupportStoredProcedures`
|
|
- `SupportNoLock`
|
|
- `SupportTop` / `SupportLimitOffset` / `SupportFirstSkip`
|
|
|
|
Typical setup:
|
|
|
|
```csharp
|
|
var options =
|
|
DynamicDatabaseOptions.SingleConnection |
|
|
DynamicDatabaseOptions.SingleTransaction |
|
|
DynamicDatabaseOptions.SupportLimitOffset |
|
|
DynamicDatabaseOptions.SupportSchema;
|
|
```
|
|
|
|
## Internal Connection Pooling
|
|
|
|
Without `SingleConnection`, DynamORM normally opens and closes a managed connection for each operation.
|
|
|
|
That is fine when the provider already has efficient connection pooling. It is not enough for providers that do not.
|
|
|
|
`ConnectionPooling` enables internal managed connection reuse:
|
|
|
|
```csharp
|
|
var options =
|
|
DynamicDatabaseOptions.ConnectionPooling |
|
|
DynamicDatabaseOptions.SupportLimitOffset |
|
|
DynamicDatabaseOptions.SupportSchema;
|
|
|
|
using (var db = new DynamicDatabase(factory, connectionString, options))
|
|
{
|
|
db.ConnectionPoolingKeepOpenCount = 32;
|
|
db.ConnectionPoolingMaximumOpenCount = 128;
|
|
db.ConnectionPoolingConnectionLifetime = TimeSpan.FromHours(1);
|
|
}
|
|
```
|
|
|
|
Behavior:
|
|
|
|
- idle connections are reused instead of opened and closed repeatedly
|
|
- up to `ConnectionPoolingKeepOpenCount` idle connections are kept open
|
|
- no more than `ConnectionPoolingMaximumOpenCount` managed connections are kept at once
|
|
- if the maximum is reached, callers wait until a connection is released
|
|
- idle pooled connections older than `ConnectionPoolingConnectionLifetime` are retired even if the preferred idle count has not been exceeded
|
|
|
|
Default values:
|
|
|
|
- `ConnectionPoolingKeepOpenCount = 32`
|
|
- `ConnectionPoolingMaximumOpenCount = 128`
|
|
- `ConnectionPoolingConnectionLifetime = 1 hour`
|
|
|
|
## Transaction Usage
|
|
|
|
```csharp
|
|
using (var db = new DynamicDatabase(factory, connectionString, options))
|
|
using (var conn = db.Open())
|
|
using (var tx = conn.BeginTransaction())
|
|
using (var cmd = conn.CreateCommand())
|
|
{
|
|
cmd.SetCommand("UPDATE users SET first = 'Ada' WHERE id = 1").ExecuteNonQuery();
|
|
tx.Commit();
|
|
}
|
|
```
|
|
|
|
Global transaction mode is also available via `db.BeginTransaction()`.
|
|
|
|
## Direct Database Transactions and Thread Ownership
|
|
|
|
`db.BeginTransaction()` is different from `conn.BeginTransaction()` on an arbitrary opened connection.
|
|
|
|
When you call `db.BeginTransaction()`:
|
|
|
|
- DynamORM binds one managed connection to the current thread
|
|
- commands executed through that `DynamicDatabase` on the same thread reuse that connection and transaction
|
|
- other threads do not join that transaction automatically
|
|
- other threads use another managed connection or wait for one to become available
|
|
|
|
This is important because it prevents unrelated work from another thread from accidentally running inside the transaction started on the original thread.
|
|
|
|
Example:
|
|
|
|
```csharp
|
|
using (var tx = db.BeginTransaction())
|
|
{
|
|
db.Execute("UPDATE sample_users SET code = 'A' WHERE id = 1");
|
|
db.Execute("UPDATE sample_users SET code = 'B' WHERE id = 2");
|
|
tx.Commit();
|
|
}
|
|
```
|
|
|
|
All commands above run on the same transaction-bound connection because they execute on the same thread.
|
|
|
|
If another thread uses the same `db` instance while that transaction is active, it does not participate in this transaction unless user code explicitly routes work back to the owning thread.
|
|
|
|
## Single Connection and Single Transaction Locking
|
|
|
|
`SingleConnection` and `SingleTransaction` are now guarded by internal execution serialization.
|
|
|
|
That means:
|
|
|
|
- only one command executes at a time per `DynamicDatabase` instance when either option is enabled
|
|
- if another thread tries to execute a command at the same time, it waits until the current execution finishes
|
|
- this reduces the amount of user-side locking needed for providers such as SQLite
|
|
|
|
Important limit:
|
|
|
|
- a reader keeps the execution slot until the reader is disposed
|
|
|
|
So if one thread executes `ExecuteReader()` and holds the reader open, another thread waits until that reader is closed or disposed before executing the next command on the same `DynamicDatabase` instance.
|
|
|
|
## Disposal Guarantees
|
|
|
|
Current disposal behavior includes idempotent guards on key objects:
|
|
|
|
- `DynamicDatabase`
|
|
- `DynamicConnection`
|
|
- `DynamicCommand`
|
|
- `DynamicTransaction`
|
|
- `DynamicTable`
|
|
- builder and parser helpers
|
|
|
|
This prevents repeated cleanup from throwing or re-disposing lower-level resources.
|
|
|
|
## Pooling and Rollback Behavior
|
|
|
|
Behavior validated by `DynamORM.Tests/Helpers/PoolingTests.cs`:
|
|
|
|
- Disposing the database invalidates active commands.
|
|
- Open transactions are rolled back during disposal when not committed.
|
|
|
|
Behavior validated by `DynamORM.Tests/Helpers/ConnectionPoolingAndLockingTests.cs`:
|
|
|
|
- pooled connections are reused
|
|
- pooled connections wait when the configured maximum is reached
|
|
- expired idle connections are retired
|
|
- direct database transactions are isolated to the owning thread
|
|
- `SingleConnection` and `SingleTransaction` serialize command execution across threads
|
|
|
|
## Practices
|
|
|
|
- Prefer `using` blocks for `DynamicDatabase`, connections, commands, transactions, and builders.
|
|
- Do not manually re-dispose the same object from multiple ownership paths unless `IsDisposed` is checked.
|
|
- Keep transaction scope short and explicit.
|
|
- Use `db.BeginTransaction()` when you want DynamORM-managed transactional flow across multiple commands on the current thread.
|
|
- Use `ConnectionPooling` when the underlying provider does not give you adequate pooling on its own.
|