How to ensure that only table row will be created with a particular state ?

Question

I have a domain object that looks something like this:

class UserStuff 
{
    String userid;
    boolean primordial;
}

In the table, primordial is a TINYINT. A User can have possibly many UserStuff. What I want to ensure is that only one row (the first row created) will have primordial == 1. All subsequent rows will have primordial == 0. At a glance, there is nothing to worry about. Let's suppose multiple creation requests for UserStuff may happen at the same time, how can I guarantee this constraint ?

My initial idea is to sync on a string (in the domain service)

class DomainService
{
    public void create( UserStuff stuff )
    {
        synchronized( stuff.userid + "/userstuff/lock" )
        {
            ...

        }
    }
}

I would love to hear comments about this & alternative methods. Thanks

Answer 1

The SQL approach, generally speaking, is to make (userid, primordial) a unique compound key, and use the INSERT statement with a clause of ON DUPLICATE KEY UPDATE -- which sets primordial otherwise (but I'll admit I'm not sure what happens if the UPDATE also hits a duplicate, as it would when you insert the second non-primordial UserStuff).

Alternatively, with suitably isolated transactions, you could first SELECT to check if that userid is already present, then within the same transaction INSERT with appropriate values of primordial.

The SQL approach would be more solid if you ever have updates from multiple processes (maybe on different client nodes), but maybe if you're 100% sure that will never happen your Java-sychronization based ideas might get better performance... I won't guess one way or another, but recommend you measure things both ways (if you're willing to bet your shirt on never, never getting updates from multiple client nodes, that is;-).

`