SQL Nightmare

I maintain a web site that allows user to self register. I got a call from one user complaining that she got an error message: "User already exists." But, she is pretty sure there is no such user in our system.

So, I open up SQL Management Studio, try to query the user table. She is right, there is no duplicated user name. I look at the stored procedure, it is very clear that it found a duplicated user name and spit out this error message. What is going on?

It took me at least 20 minutes to suddenly realize that the stored procedure is wrapped in a big transaction. It is possible, a user with the same name is inserted during the transaction, before the complaining code. But, the insertion is canceled when the transaction is rolled back due to the error. I checked the stored procedure again, and did find something like this:

BEGIN TRAN
insert user A
...
... do lot of other things
...
if not exists user A
  insert user A
  COMMIT TRAN
else
  @error="User already exists."
  ROLLBACK TRAN
end

Lesson learned: transactional database can be a strange beast. What you see may not be what you think it is. Be aware!