2

I have table with autoInc primary key('id') but i need to control uniqueness of records in the table by another column as well (column 'code').

I tried to make it this way:

def findByCode(code: String): Future[Option[A]] =
  try db.run(tableQuery.filter(_.code === code).result.headOption)


def insert(entity: A): Future[Int] = 
  try db.run(tableQuery += entity)


def insertIfNotExists(code: String, entity: A): Future[Int] = {

  findByCode(code).flatMap {

    case None => insert(entity)
    case _ => Future(-1)
  }
}

Methods findByCode and insert returns Future[A] and Future[Int] respectively. When i ran this code on some amount of records to be inserted i realised that findByCode doesnt find records which have been inserted and i got massive records duplication. As a workaround i built a constraint in my db (driven by postgres), but i would like to know if i'm doing smth wrong in the code or it is potentialy cannot be guarantee to check if record exists when i just was inserted in another concurrent transaction?

What can be the optimal recommended way for my purpose?

1) build uniqueness constraint and wrap insert int try block? (i use this now)

2) implement insertOrUpdate using plainsql with one query (insert into where not exists (select where ...))

3) write a synchronisation wrapper for such queries (with Await) and run them synchronously in one thread

4) smth else

Thanks in advance for any advices.

elm
  • 20,117
  • 14
  • 67
  • 113
Alexander Larin
  • 21
  • 2
  • I like 1, 2 - you can also use MERGE/UPSERT. Other cases are needed only if for some reason unique constraint is not possible or MERGE does not work – yǝsʞǝla Jun 09 '15 at 09:47
  • Thank you, @AlekseyIzmailov. But any ideas why my code doesnt work the right way? I've read in slick docs that concurrency consistence safe is not guaranteed. Is that my case or i'm just missing smth? – Alexander Larin Jun 09 '15 at 10:17
  • It can't guarantee that the record does not exist and can lead to duplicates. If another thread inserts a record after you checked with `findByCode` and before you inserted it the dup will get created. If your check is quite slow chances will increase. – yǝsʞǝla Jun 09 '15 at 10:32
  • In fact it also depends whether you are committing after each insert and what is your MVCC support/settings in DB. All in all it's not a good way to avoid duplicates unless you allow only a single process to do inserts, i.e. no Futures – yǝsʞǝla Jun 09 '15 at 10:34
  • just found very similar question, my bad. http://stackoverflow.com/questions/30706193/insert-if-not-exists-in-slick-3-0-0 – Alexander Larin Jun 09 '15 at 11:33

0 Answers0