There are a couple of possible scenarios which are easy to solve, and a pernicious one that isn't.
For a user that enters a value, then enters the same value some time later a simple SELECT before the INSERT will detect the problem. This works for the case where one user submits a value and some time later another user submits the same value.
If the user submits a list of values with duplicates - say {ABC, DEF, ABC} - in a single invocation of the code the application can detect and filter the duplicates, perhaps throwing an error. You'll also need to check the DB does not contain any of the unique values before the insert.
The tricky scenario is when one user's write is inside the DBMS at the same time as another user's write, and they're writing the same value. Then you have a race a condition between them. Since the DBMS is (most likely - you don't say which one you're using) a preemptive multitasking system any task can be paused at any point in its execution. That means user1's task can check there's no existing row, then user2's task can check there's no existing row, then user1's task can insert that row, then user2's task can insert that row. At each point the tasks are individually happy they're doing the right thing. Globally an error occurs, however.
Ordinarily a DBMS would handle this by putting a lock on the value in question. In this problem you're creating a new row so there is not yet anything to lock. The answer is a range lock. As it suggests this locks a range of values, whether they currently exist or not. Once locked that range cannot be accessed by another task until the lock is released. To get range locks you have to specify and isolation level of SERIALIZABLE. The phenomenon of another task sneaking in a row after your task has checked is knows as phantom records.
Setting the isolation level to Serializable across the whole application will have implications. Throughput will be reduced. Other race conditions which worked well enough in the past may start to show errors now. I would suggest setting it on the connection which executes your duplicate-inducing code and leaving the remainder of the application as is.
A code-based alternative is to check after the write rather than before. So do the INSERT, then count the number of rows that have that hash value. If there are duplicates rollback the action. This can have some perverse outcomes. Say task 1 writes then task 2. Then task 1 checks and finds a duplicate. It rolls back even though it was first. Similarly both tasks may detect the duplicate and both rollback. But at least you'll have a message to work with, a retry mechanism and no new duplicates. Rollbacks are frowned on, much like using exceptions to control program flow. Note well that all work in the transaction will be rolled back, not just the duplicate-inducing write. And you'll have to have explicit transactions which may reduce concurrency. The duplicate check will be horribly slow unless you have an index on the hash. If you do you may as well make it a unique one!
As you have commented the real solution is a unique index. It seems to me like this should fit into your maintenance window (though of course you know your system best). Say the hash is eight bytes. For one hundred million rows that's about 1GB. Experience suggests a reasonable bit of hardware would process these many rows in a minute or two, tops. Duplicate checking and elimination will add to this, but can be scripted in advance. This is just an aside, though.
