This week's book giveaway is in the Cloud/Virtualizaton forum.
We're giving away four copies of Mesos in Action and have Roger Ignazio on-line!
See this thread for details.
Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How to design the db isolation/concurrency for shared collection

 
atul khot
Ranch Hand
Posts: 67
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dear all,
Greetings.

We are developing a Spring MVC application - it uses hibernate talking to mysql...

There is a shared persistent collection - shared across two / or more clients (browsers).
The back end needs to take an element from this collection and assigns it to client
(it should not assign the same element to two client, any point of time)
I have written the assignment algorithm which works well for a single client...

Our Service layer is made of Spring beans (using interfaces + @Service + @Autowiring) -
and as these beans are singletons - we need to think of concurrency at two level.

In memory concurrency and DB concurrency...
I understand multi threading - have written threaded code - and have used java threading patterns like Executor in the past...

I found that Spring supports it (http://static.springsource.org/spring/docs/3.0.5.RELEASE/reference/scheduling.html)...
I know how to code using this and handle the In Memory Concurrency...
About DB concurrency, we started using Spring's @Transactional support - this is where I am not sure how to go about the design...
I am looking for Spring to help me with ACID - I don't want to use hibernate locking itself - Rather would want Spring to handle this itself

In particular, assuming there are two concurrent clients A and B...
Shared persistence collection has [M, N, O, P]

1. Request from A comes
2. Assignment algorithm pulls the collection in memory (in its own hibernate session)
3. Select M - and assigns it to A
... [Time Window]
4. Persists the relation that A owns M

My question is w.r.t. the time window

1. Request from B comes
2. Assignment algorithm pulls the collection in memory (in its own hibernate session)
(Assuming the flow for A is at the time window)
3. Select M (this is possible - M is still pristine) - and assigns it to B

I want only one assignment to succeed - the other should fail - the service should retry the assignment (by that time the db state would
have changed) - and reattempt the assignment...

I think we will need READ_COMMITTED as isolation level.

Is there some way Spring can help me solve this? I suspect this problem is common / solved multiple times in the past - and there are best
practices around it...

Thanks in advance for any ideas/suggestions/pointers/nudge in the right direction...

- thanks atul
 
atul khot
Ranch Hand
Posts: 67
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Guys,
Any ideas, pointers?

- thanks atul
 
Mark Spritzler
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well READ_COMMITED won't have the database do any real locking. So you might need to go to REPEATABLE_READ, which locks the rows when the user gets them. So the first user loading the Collection will lock all the objects in the collection.

The other option is just to use a version field, and let Hibernate do optimistic locking. Basically both users can run that use case, but the first one wins. A version field can be a number or a timestamp.

Those are your two options.

Mark
 
atul khot
Ranch Hand
Posts: 67
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dear Mark,
Thank you so much - will check along the lines you suggested...

- thanks atul
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic