Assume I have User entities, Role entities, and a n:m relationship between User and Role, supported by the UserRole entity. Assume User entities have a .Roles collection holding the user's roles.

Assume the business layer gives me a User entity, wich I must save. Following several posts in the forums, here is how we do it, wrapped in a transaction:

adapter.SaveEntity(user) adapter.DeleteEntitiesDirectly("UserRole", new RelationPredicateBucket(UserRoleFields.UserId == user.Id)); foreach (Role r in user.Roles) { UserRole ur = new UserRole(); ur.UserId = user.Id; ur.RoleId = r.Id; adapter.SaveEntity(ur); }

However, since SaveEntity(user) may not actually trigger an UPDATE in the database (if no fields have changed), the following scenario is possible:

Thread #1 updates the user, actually trigger an UPDATE Thread #2 updates the user, does not trigger an UPDATE Thread #2 deletes roles Thread #2 inserts new roles (wait...) Thread #1 deletes roles (deadlock)

There is a way to prevent this, by adding a timestamp field to User and implementing an optimistic concurrency check. This would ensure that an UPDATE is always triggered, thus causing Thread #2 to wait when trying to update the user.

Any way to achieve the same result without adding a timestamp (bearing in mind that we can not select "for update" with LLBL)?

Would wrapping your calls in a Transaction solve the problem?

If not, I think the timestamp would be a valid solution, if you don't want to use it, then a work around is to set IsChanged to true for some field in the entity, would make sure it gets saved.

Walaa wrote:

Would wrapping your calls in a Transaction solve the problem?

They are already, yet even with a SERIALIZABLE isolation level the sequence above can happen.

Walaa wrote:

If not, I think the timestamp would be a valid solution, if you don't want to use it, then a work around is to set IsChanged to true for some field in the entity, would make sure it gets saved.

The timestamp is a valid solution, though I'd rather find another solution--mainly because a timestamp implies that I then have to manage optimistic concurrency issues, which I was happy to ignore (last write wins).

I must admit I did not think about setting IsChanged to true to force a database UPDATE... yet I think this will cause problems with MySql. MySql returns the number of actually impacted rows, e.g. UPDATE foo SET name='bar' WHERE id=1 will report 0 rows updated if name's value is 'bar' already. I assume that "0 rows updated" will trigger an ORMConcurrencyException.

A common workaround is to implement some sort of flip-flop to make sure the row is actually updated, e.g. UPDATE foo SET ff=NOT(ff), name='bar' WHERE id=1. But I do not quite see how LLBL could handle that transparently?

Looks like I'm going to use a timestamp after all.

A timestamp or maybe using a critical section in the code in order to avoid database locks.