Locking database edit by key name

Question

I need to prevent simultaneous edits to a database field. Users are executing a push operation on a structured data field, so I want to sequence the operations, not simply ignore one edit and take the second.

Essentially I want to do

synchronized(key name)
{
  push value onto the database field
}

and set up the synchronized item so that only one operation on "key name" will occur at a time. (note: I'm simplifying, it's not always a simple push).

A crude way to do this would be a global synchronization, but that bottlenecks the entire app. All I need to do is sequence two simultaneous writes with the same key, which is rare but annoying occurrence.

This is a web-based java app, written with Spring (and using JPA/MySQL). The operation is triggered by a user web service call. (the root cause is when a user sends two simultaneous http requests with the same key).

I've glanced through the Doug Lea/Josh Bloch/et al Concurrency in Action, but don't see an obvious solution. Still, this seems simple enough I feel there must be an elegant way to do this.

Answer 1

I think you may be looking for transactions http://www.jpox.org/docs/1_2/jpa/transaction_types.html and http://static.springsource.org/spring/docs/2.0.x/reference/transaction.html

Answer 2

There may be a simple way to let your database take care of this for you. I am admittedly weak in knowledge when it comes to databases. In lieu of that, here is an approach that involves creating an individual lock for each key name. There is a single repository that manages the creation/destruction of the individual locks that requires a one-for-the-entire-application lock, but it only holds that lock while the individual key-name lock is being found, created, or destroyed. The lock that is held for the actual database operation is exclusive to the key name being used in that operation.

The KeyLock class is used to prevent simultaneous database operations on a single key name.

package KeyLocks;

import java.util.concurrent.locks.Lock;

public class KeyLock
{
    private final KeyLockManager keyLockManager;
    private final String keyName;
    private final Lock lock;

    KeyLock(KeyLockManager keyLockManager, String keyName, Lock lock)
    {
        this.keyLockManager = keyLockManager;
        this.keyName = keyName;
        this.lock = lock;
    }

    @Override
    protected void finalize()
    {
        release();
    }

    public void release()
    {
        keyLockManager.releaseLock(keyName);
    }

    public void lock()
    {
        lock.lock();
    }

    public void unlock()
    {
        lock.unlock();
    }
}

The KeyLockManager class is the repository that is responsible for the lifetimes of the key locks.

package KeyLocks;

import java.util.HashMap;
import java.util.Map;
import java.util.concurrent.locks.Lock;
import java.util.concurrent.locks.ReentrantLock;

public class KeyLockManager
{
    private class LockEntry
    {
        int acquisitionCount = 0;
        final Lock lock = new ReentrantLock();
    }

    private final Map<String, LockEntry> locks = new HashMap<String, LockEntry>();
    private final Object mutex = new Object();

    public KeyLock getLock(String keyName)
    {
        synchronized (mutex)
        {
            LockEntry lockEntry = locks.get(keyName);
            if (lockEntry == null)
            {
                lockEntry = new LockEntry();
                locks.put(keyName, lockEntry);
            }
            lockEntry.acquisitionCount++;
            return new KeyLock(this, keyName, lockEntry.lock);
        }
    }

    void releaseLock(String keyName)
    {
        synchronized (mutex)
        {
            LockEntry lockEntry = locks.get(keyName);
            lockEntry.acquisitionCount--;
            if (lockEntry.acquisitionCount == 0)
            {
                locks.remove(keyName);
            }
        }
    }
}

Here is a sample of how you would use a key lock.

package test;

import KeyLocks.KeyLock;
import KeyLocks.KeyLockManager;

public class Main
{
    private static final String KEY_NAME = "TEST_KEY";

    public static void main(String[] args)
    {
        final KeyLockManager keyLockManager = new KeyLockManager();
        KeyLock keyLock = null;
        try
        {
            keyLock = keyLockManager.getLock(KEY_NAME);
            keyLock.lock();
            try
            {
                // Do database operation on the data with the specified key name
            }
            finally
            {
                keyLock.unlock();
            }
        }
        finally
        {
            if (keyLock != null)
            {
                keyLock.release();
            }
        }
    }
}

Answer 3

Even if you lock the key, you cannot ensure it is the same (not only an equal) key next time. What you need is something like select for update done by the database. If this is not possible, you need to lock the key yourself programatic using a synchronized set of locked keys as a member of your repository/dao.