Databases
StackOverflow2013
Postsdbo
POHow to save the order of a table and prevent concurrency issues
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to save the order of a table and prevent concurrency issues
    primarykey
    data
    text
    <p>I have the following problem I have to save the order of some elements in a table. This is done by a order number. So the first element has ordering 1 and the tenth the ordering 10 etc. Also I have a brand value saved, which means the ordering is only inside all rows of one brand. My configuration is Spring 3.1 with Hibernate 4.1.6 and Spring Data, I also use the JPA interface, underlying is a Mysql Database.</p> <p>I have right now a save method which gets the maximum value by a namedquery. Then increments the value by one. And now sets the value with the object setter. Then saves it.</p> <pre><code> if(tag.getOrdering()==0) { int newOrderingNumber = tagRepository.getMaximumOrderingNumber(); newOrderingNumber++; if(LOGGER.isDebugEnabled()) { LOGGER.debug("Next Ordering Number for Tag is " + newOrderingNumber); } tag.setOrdering(newOrderingNumber); } </code></pre> <p>Here I have a problem what would happen if two threads are doing this at the same time. They would both save the same value. But one would have to increment twice. My question is how could I prevent this. First I tried to do something with the isolation level of the spring transaction, but JPA doenst know transaction levels and the ways I found how I could extend the jpadialect like here (<a href="http://amitstechblog.wordpress.com/2011/05/31/supporting-custom-isolation-levels-with-jpa/" rel="nofollow">http://amitstechblog.wordpress.com/2011/05/31/supporting-custom-isolation-levels-with-jpa/</a>) But that didnt work, my Connection was always readonly the readOnly=false flag was not set correctly and I didnt find out what was wrong. Also I think that wouldn`t solve my problem.</p> <p>Then I tried to work with a @Version Property, but this doesnt work because I do not work on one object instance. The two threads are working on two different rows, so the versioning doesnt solve the problem.</p> <p>So now I see four different approaches. </p> <p>First: I could use a synchronize lock object inside my code to let one thread wait for the other to free the lock on the object</p> <p>Second: I could acquire a full table lock, which I dont know how to do with hibernate and it also seems oversized to block the complete table.</p> <p>Third: When I wouldnt have the brand column inside which the ordering could be the same, I could add a Unique constraint which would throw an Exception which I can use to increment it once again, til it works.</p> <p>Fourth: Right now I found an additional way, which could work. I dont save an ordering number. Instead I add a column which contains the id of the next element in order. This would mean that there is a Join in every request with the order, but I know that this table has not many rows, so the performance doesnt really matter. Also the updating when the order changes would be much easier. </p> <p>So what would be the right way to solve this problem. I also have to solve the problem when I have to update all objects when the ordering is changed. </p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USLogarith
    1. USLogarith
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to save the order of a table and prevent concurrency issues
      1. This table or related slice is empty.
      1. VTUpMod
    1. COYou *need* DB transactions, everything else is a bad work around. I'd sugest to rephrase your question in the direction of how to get transactioning working in your environment.
      singulars
      1. POHow to save the order of a table and prevent concurrency issues
      1. USBetaRide
    2. COI use mysql, but as I understand the provided isolation levels (read uncommited, read repeatable, serializable, read commited) always are row locking. As I do work on two different rows and getting my maximum value by select max(ordering) from table even the isolation levels wouldnt prevent two concurrent reads, because the read is not on a specific row, as I see it (but my understanding of it is also very poor right now)
      singulars
      1. POHow to save the order of a table and prevent concurrency issues
      1. USLogarith
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload