The Task Management API has a small convenience for development. When I want a clean set of demo data, I can turn on a setting and the app wipes the tables and rebuilds the seed on startup. One line in a config file, and the next run gives me fresh users, categories, and tasks.
For a while I read that feature as a single step. Reset the data. It turned out to be two steps that only looked like one, and the gap between them was the interesting part.
Resetting the Data Was Actually Two Operations
The reset lives in a seeder class, and the earlier version of it looked like this:
public async Task RefreshAsync()
{
await _dbContext.Database.ExecuteSqlRawAsync(
"TRUNCATE TABLE tasks, users RESTART IDENTITY CASCADE;");
await SeedAsync();
}Read it slowly and it is clearly two things. First it clears the tables with TRUNCATE. Then it calls SeedAsync() to insert the demo data again. The method is named RefreshAsync, which made it feel like one action, but inside there are two database operations in sequence.
SeedAsync() is not trivial either. It creates the admin user, the regular user, categories, and then tasks for each user, saving along the way. So the “second step” is itself a small chain of inserts.
The Failure Window
The problem is what happens between those two steps if something goes wrong.
Step one clears the tables. That step is allowed to succeed. If step two then fails partway through, nothing is there to undo step one. The result is a database that is empty or only partly rebuilt, and the previous data is already gone:
TRUNCATE -> success
SeedAsync() -> fails partway
result -> empty or partial dataThe failure does not have to be exotic. It could be a constraint that rejects one of the seeded rows, a database error during an insert, or any exception thrown in the middle of seeding. The point is not the specific cause. The point is that a later step can fail after an earlier step has already made an irreversible-looking change.
I want to be careful about what I actually observed here, because the title of this article uses the word “could” on purpose. I did not see the database go empty during real use. There was no incident and no lost data. This is a local development convenience, and RefreshOnStartup is off by default, so the reset only runs when someone turns it on. What I found was a potential failure mode while looking at how the reset worked, not something that had already happened. The risk was real in the code, but it was latent.
Why This Is a Partial-State Problem
It helps to separate the failure from the state it leaves behind. A transaction does not prevent the second step from failing. Seeding can still throw. What a transaction changes is the state you are left with afterward.
Without a shared boundary, the two steps are independent from the database’s point of view. The TRUNCATE is committed when it runs. The inserts are committed as SeedAsync() saves. If seeding fails in the middle, the earlier commits are already permanent, and there is no clean way back.
With a shared boundary, both steps sit inside one transaction. If seeding fails, the whole thing is rolled back, and the database looks the way it did before the reset started:
BEGIN
TRUNCATE
seed users
seed categories
seed tasks
... fails
ROLLBACK
result -> previous state preservedEither the reset completes, or the previous valid state stays. That property is atomicity, and it is the one this story is really about. I am not going to walk through the other letters of ACID, because the reset only needed this one.
Finding the Right Boundary
The part that took a moment was deciding what belonged inside the transaction. It is tempting to think of TRUNCATE as the dangerous step and the seeding as the safe one, and then to only worry about the clear. But that framing misses the point.
TRUNCATE and seeding were not independent operations in this workflow. Together they meant “reset the development data”. The clear only exists so that the seed has a clean slate, and the seed only makes sense right after the clear. You would not want one without the other. So the transaction boundary should match that logical unit, not the individual statements.
Once I saw it that way, the boundary was easy. It starts before the clear and ends after the seed, because that whole span is the single thing the caller asked for.
Making the Reset Atomic
The final version wraps both steps in one transaction:
public async Task RefreshAsync()
{
await using var transaction = await _dbContext.Database.BeginTransactionAsync();
await _dbContext.Database.ExecuteSqlRawAsync(
"TRUNCATE TABLE refresh_tokens, task_categories, tasks, categories, user_profiles, users RESTART IDENTITY CASCADE;");
await SeedAsync();
await transaction.CommitAsync();
}The shape is worth noticing. The transaction is created before the TRUNCATE, SeedAsync() runs inside it, and the commit is the last line. If execution reaches the commit, everything that happened inside is made permanent together. If execution never reaches it, the transaction is never committed.
There is no explicit RollbackAsync() call, and I think that is worth being honest about rather than adding one for looks. The rollback happens because the transaction is disposed without being committed. The await using at the top guarantees the transaction is disposed when the method exits, including when it exits because of an exception. A transaction that is disposed while uncommitted is rolled back. So the failure path is handled by the disposal, not by a catch block that calls rollback by hand. That is the behavior in the code, and it is the behavior the test confirms.
The TRUNCATE statement itself names all the tables and uses RESTART IDENTITY CASCADE. RESTART IDENTITY resets the identity counters, so the next inserted rows start from id 1 again. CASCADE also clears tables that depend on the named ones through foreign keys. None of that changed; the only change in this commit was the transaction around it.
What Happens When Seeding Fails Now
With the transaction in place, the failure path is different in an important way. If seeding throws, the exception propagates out of SeedAsync(), the commit line is never reached, and the transaction is disposed in an uncommitted state, which rolls it back. The TRUNCATE is undone along with everything else.
That detail is specific to the database, and it matters here. In PostgreSQL, TRUNCATE is transactional, so it can be rolled back like any other statement. That is not true in every database. Because this project runs on PostgreSQL, the clear and the seed can share one transaction and be undone together. I would have had to think differently on a database where clearing data commits on its own.
What the Tests Prove
There are two tests around this behavior, and the second one is the interesting one.
The first runs the reset normally and checks the result: after RefreshDatabaseAsync(), there are two users and six tasks, and the smallest user id is 1. That last assertion is what proves RESTART IDENTITY did its job, since the ids were reset and the seed started over from 1.
The second test is the one that makes the atomicity claim concrete. It sets up a deliberate failure and checks that the previous state survives:
await context.Database.ExecuteSqlRawAsync(
"ALTER TABLE users ADD CONSTRAINT test_seed_failure " +
"CHECK (\"Email\" <> 'admin@mail.com') NOT VALID;");That constraint makes the seeded admin row invalid, so seeding is guaranteed to fail when it tries to insert admin@mail.com. The test then calls the reset and expects it to throw:
await Assert.ThrowsAsync<DbUpdateException>(() => seeder.RefreshAsync());The important part is what it checks afterward. It opens a fresh scope, reads the database, and asserts that the user it created before the reset is still there and that the task count is unchanged:
Assert.True(await verifyContext.Users.AnyAsync(user => user.Email == "preserved@mail.com"));
Assert.Equal(taskCountBeforeRefresh, await verifyContext.Tasks.CountAsync());If the transaction were missing, this test would fail in the most direct way possible. The TRUNCATE would have run, the seeding would have failed, and preserved@mail.com would be gone. The test would have found the empty database the article title describes. Because the transaction is there, the row survives and the assertion passes.
This is why I think the test is the real evidence here. The transaction in the source shows the intent, but the test shows the outcome: after a failed reset, the old data is still present.
The Trade-off
A transaction is not free, and wrapping things in one is not automatically better. A transaction holds resources while it runs and keeps the connection busy until it commits or rolls back. For a long workflow, a large transaction can be the wrong choice, and it is not something I would add around arbitrary multi-step work just because it is multi-step.
What made it right here is that the steps were genuinely one operation. The clear had no meaning without the seed, and the seed had no meaning without the clear. They were two statements that belonged to one intent, so they deserved one boundary. For a workflow where the steps really are independent, and where a partial result is acceptable, adding a transaction would add cost without changing anything that needed to change.
What I Learned
The lesson was not really about calling BeginTransactionAsync(). That is one line, and it is easy to write. The lesson was recognizing that what I had been reading as one operation was actually several, and that those several belonged together.
Once that was clear, the boundary almost chose itself. The reset either finishes or it does not, and the previous state should be the thing you are left with when it does not. That framing, more than the API call, is what I took away. A transaction is just the tool that expresses a decision I had not made yet: which operations form a single unit, and what should remain if any part of that unit fails.