This question is asked about once a week on SO, and the answers are almost invariably wrong.
Here's the right one.
insert customer (email, count)
select 'foo@example.com', 0
where not exists (
select 1 from customer
where email = 'foo@example.com'
)
update customer set count = count + 1
where email = 'foo@example.com'
If you like, you can insert a count of 1 and skip the update
if the inserted rowcount -- however expressed in your DBMS -- returns 1.
The above syntax is absolutely standard and makes no assumption about locking mechanisms or isolation levels. If it doesn't work, your DBMS is broken.
Many people are under the mistaken impression that the select
executes "first" and thus introduces a race condition. No: that select
is part of the insert
. The insert is atomic. There is no race.
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…