oracle select and concurrent insert :: To check email availability

Question

We have simple case, We have a table with column emailId i.e. unique.....using oracle DB

Question#1 Multiple concurrent user can check if some email id is available or not. Like 2 user that same time check availability of: abc@test.com

session1: select emailid from user_table; //If not present allow user to complete rest of the process & insert info

session2: select emailid from user_table;

Now both session will get that this email id (abc@test.com) is available & both try to insert, I know one of them will get error upon insertion BUT how we can make sure only 1 user get availability & other get not available upon select ??

Question#2 Also in case both sessions inserted the same value, then first will succeed, is there ways that 2nd session update that row instead of throwing error. Like we have another column for timestamp & want that 2nd session instead of throwing error simple update the timestamp column ?

Answer 1

As this is a rather abstract question, here are only some general guidelines:

1. To deal with concurrent insert in a table, you need an unique index, and be prepared in your code to deal with ORA-00001 error unique constraint violated. Never rely only on check before insert
   ^{(unless you have somehow exclusive access to your table -- and even if so ... as of myself, I would add an unique index: doesn't cost much and make me sleep better)}
   
   
2. Oracle has a MERGE statement that allows you update or insert based on a condition. This operation is sometimes called an upsert. By using that keywork you should be able to find more informations
   See Oracle: how to UPSERT (update or insert into a table?) for example.

---

Now for, some thoughts about you specific case (maybe):

The only way for the system to work as you suggested, would be to make some kind of reservation when you check for availability (i.e.: immediately insert the row, instead of just select). And then update the row when the user confirm. But that means: (1) you will have to somehow deal with never-confirmed reservations (2) that doesn't dispense you to have an unique index, and to deal with ORA-00001.