Concurrent access on same table from two servers

I am accessing a SQL Server 2005 table from java J2EE using preparedStatements. The jars are deployed on two servers and run in parallel. The process is as follows:

1. select from event table new user events
2. process them (send emails to users and other)
3. delete processed events from event table (if step开发者_开发知识库 2 wasn't executed ok no delete should be done)

In some cases, a users receives 2 emails (one from each server) because the 2 selects are executed simultaneously before the delete statement. I don't have admin rights on the table, just access it from the java application.

How can I lock the table at the first select and unlock it after the delete? Do you see another solution to this?

If you pick up work in a transactional way, only one server can pick it up:

set transaction isolation level repeatable read

update  top 1 tbl
set     ProcessingOnServer = HOST_NAME()
from    YourWorkTable tbl
where   ProcessingOnServer is null
        and Done = 0

Now you can select the details, knowing the work item is safely assigned to you:

select  *
from    YourWorkTable tbl
where   ProcessingOnServer = HOST_NAME()
        and Done = 0

The function host_name() returns the client name, but if you think it's safer you can pass in the hostname from your client application.

We usually add a timestamp, so you can check for servers that took too long to process an item.