0

Have table for storing some unique identifiers. My first idea was to have incremental counter column for every single identifier prefix like this:

ID IDENTIFIER COUNTER
 1 B-1        1
 2 A-1        1
 3 A-2        2
 4 A-3        3
 5 B-2        2
 6 BBA-1      1
 7 BBA-2      2

Every identifier is prefixed by letter or combination of letters and extended by some incremental number. For example, if I want to add a new identifier with A- prefix, have to select max value of counter where IDENTIFIER starts with 'A-', returned value increment by one, concatenate it with searched prefix and persist to database. With this approach I'm afraid of concurrency issues when two parallel threads grab the same value of counter, uniqueness rule would be violated.

How can I prevent this? Is it better approach to have two tables where one would only take care of individual counter for every identifier base, like this:

 BASE COUNTER
 A    3
 B    2
 BBA  2

Does Spring or JPA have some mechanism for dealing with this? First idea I have at the moment is optimistic locking and @Version annotation. Would it be useful because I already have JPA mappings in my project?

shx
  • 1,068
  • 1
  • 14
  • 30
  • Could you explain why your identifiers are in the [letters]-[number] format? What is the significance of this? What is the relationship between record A-1 and record A-2? What do they have in common? – Solace Jun 14 '17 at 03:07
  • Format of "identifier" is not so important for this problem. Let's say that those letters are some types of something. Generating them with sequential suffix and database concurrency is the point. – shx Jun 14 '17 at 03:20
  • I asked because the version annotation feels like overkill for the problem you're describing. You can use the Id annotation for multiple columns in your entity. So I would suggest that you divide the letters and the number into two separate columns and mark them both with the Id annotation. You can use the GeneratedValue annotation to automatically increment that number column. Do note that using multiple Id annotations is allowed in Hibernate, but is not JPA compliant. If you want to stick with JPA rules, you can look into the EmbeddedId annotation. – Solace Jun 14 '17 at 03:27
  • This answer may help you. you can safely increment counter [https://stackoverflow.com/a/30156346/10400410](https://stackoverflow.com/a/30156346/10400410) – naimul Jul 04 '22 at 05:27

1 Answers1

-1

You can use CAS on MySQL.

  1. select COUNTER from $your_table where IDENTIFIER = $your_IDENTIFIER

here got $count

  1. update $your_table set COUNTER=($count + 1) where IDENTIFIER=$your_IDENTIFIER and COUNTER = $count
teaey
  • 47
  • 6