DB concurrency issues with domain model IDs

Question

So I've done a good job separating the DAL from the BLL. I use repositories for each aggregate root. The issue I'm having is database concurrency with IDs in the domain.

I don't want to use the surrogate key in the domain layer (e.g. GUID). So the aggregate root I have now is a Journal and that Journal has a list of transactions. The first table is Journals and the second table is Transactions that map back to the first table.

It looks like this:

Journals

+----------------------------------------+
|  ID  | JournalID | LastTransactionID   |
+----------------------------------------+
|{a-as}| 1         | 8                   |
|{ase1}| 2         | 1                   |
|......|...........|.....................|
|{1sas}| 45        | 4                   |
+----------------------------------------+

Transactions

+----------------------------------------------------+
| ID   | JournalID | TransactionID | Account | Amount|
+----------------------------------------------------+
|{a2ba}| 1         | 1             | xxxxxxx | 10.20 |
|{2a4a}| 1         | 2             | xxxxxxx | 1.10  |
|{20s9}| 2         | 1             | xxxxxxx | 93.12 |
|......|...........|...............|.........|.......|
|{9eka}| 45        | 4             | xxxxxxx | 291.10|
+----------------------------------------------------+

So I use the LastTransactionID from the Journals column to figure out how to assign a domain ID (TransactionID) to new transactions. The issue I'm having is if more than one desktop app instance is accessing the same Journal then the LastTransactionID could be stale and causes issues with trying to insert new transactions.

My domain classes look like:

class Journal
{
    public int JournalID { get; private set; }
    public int LastTransactionID { get; private set; }
    public IEnumerable<Transaction> Transactions { get; private set; }

    public Journal(int journalID, int lastTransactionID, IEnumerable<Transaction> transactions)
    {
        // do work......
    }
 }

class Transaction
{
    public int JournalID { get; private set; }
    public int TransactionID { get; private set; }
    public Account Account { get; private set; }
    public Money Amount { get; private set; }

    public Journal(int journalID, int transactionID, Account account, Money amount)
    {
        // do work......
    }
 }

I'm using EF and have this encapsulated in my repository. Maybe I'm using a poor database design?

score 1 · Accepted Answer · answered Apr 18 '18 at 14:20

I don't want to use the surrogate key in the domain layer

I have no idea why you wouldn't want to use this. Your BLL is just reinventing the wheel by trying to manage and create it's own unique ID. Let the database do the tasks it does best.

By having a database that can create and enforce a unique value and make it available to other users of the system, that's how you manage concurrency.

Otherwise, you need to store the ID created in the BLL somewhere other parts/instances of the application can access it. Even if you're holding it in memory instead of on a disk, it's just another part of your data storage.

The only advantage to what you're doing is if you switched to a database that did not have the ability to create its own unique ID's.

DB concurrency issues with domain model IDs

1 Answers1