pessimistic lock pattern c#

Smoggy

I'm looking for a pessimistic lock pattern. Let me try and explain how it's envisaged to work.

I have a set of entities such as client, address , accounts. Client being the top level entity.

When a user reads any of these tables under client to perform an edit. The top level entity is locked preventing any other users editing data in the hierarchy.

So I'm editing client 1. Another user tries to edit the address of client 1. However a lock check on the top entity is performed and we see a lock. The user is informed that the data is locked and to return when the data has been updated.

Has anyone seen such a pattern? It's not locking at a record level. But more a top level entity.

Giblet

You can request the top level entity with a lock hint in SQL inside a transaction with the appropriate isolation level when requesting any of the children, how you find which is the correct entity depends on your design pattern. If you are using a DDD style aggregate root pattern, you could only request the root to access the children, so it becomes easier.

Smoggy

I will have to play around with these and see what I can achieve.

Would this work if the transaction is disconnected?

I.e
read data. Apply locks. Disconnect connection. Return data.
Client side edit data.
Update data. Drop locks.

Giblet

Dropping a transaction usually means a rollback which releases the lock, or a timeout after x seconds depending on how you set it up. This doesn't seem appropriate in your case. You need a semaphore and this could be difficult to achieve depending on your setup. Distributed system = distributed semaphore / mutex. You could try redis or memcached to set a key and use CAS/Transactions if you need a distributed lock. (Memcached won't persist it though, and makes no guarantees about keeping the key either, but it works in a pinch.)

Smoggy

This sounds like the problem I want to solve isn't common?

Maybe there is a different way to achieve what I want? Less square peg / round hole :P

Giblet

Smoggy wrote: »

This sounds like the problem I want to solve isn't common?

Maybe there is a different way to achieve what I want? Less square peg / round hole :P

It's not that it isn't common, it's just there are hundreds of ways to do it. Locking is always detrimental to performance though, which is why optimistic locking is preferred. Simple versioning could be enough to accomplish what you want. You request a record at X version, and so does another client, who ever finishes first succeeds and increments the version, the other guy sees a different version in the database than they read, so fails but can be given the option to update their local copy or overwrite it. Using a semaphore would mean the semaphore key is read. If it exists, you are given a warning to say the semaphore was set, if not, you atomically set the key and edit the records as needed, other users will see the semaphore until you finish. A semaphore could be anything from a file, to a database key or a key in memcached.