I have table `Messages` that has fields, and another table called `SentMessages`, which has a one-to-one relationship to the `Messages` table.

`Messages` stores information about the message, including its text, and subject, and the recipient, etc.

```
create table Messages
(
    Id bigint not null identity(1, 1) primary key,
    Text nvarchar(max),
    Subject nvarchar(400)
    -- other fields
)

```

`SentMessages` stores information related to the transportation of the message to the clients.

```
create table SentMessages
(
    Id bigint not null primary key,
    SentOn datetime not null,
    DeliveredOn datetime null,
    -- other fields
)
go
alter table SentMessages with check add constrait [FK_SentMessages_Messages] 
foreign key([Id])
references Messages ([Id])

```

They are designed to be separate, so please no advice on merging them.

Now the logic is to find all messages that are not sent yet, and send them. Basically a simple `not in` clause in a single-threaded scenario would do the trick:

```
select *
from Messages
where Id not in 
(
    select Id 
    from SentMessages
)

```

But this database is used by many concurrent threads, therefore it's possible for a message to be taken by more than one thread, hence be sent twice or even more.

If they were a single table, I could use `update select` statement to select unsent messages in a single transaction.

What options do I have though to make selection transactional, so that a message won't be sent twice?