Data in SQL Server tables is not read repeatedly under multithreading

In some of the business, such as sending text messages, mail, we often use a table to store the data to be sent, from the background multiple threads constantly read from the table to send data to send, the completion of the transfer of data to the history table, so that the data to send the table is generally not too much. If the table structure to be sent is:

Create Table SMS (ID int NOT null identity (), Content varchar (1024x768), Status int not null,createtime datetime);

Status Value: 0 Unread 1 Read

The advantage of this design is that the backend will not be sent too slow to cause the front-end to receive the request to send a message, such as the business of sending SMS, sometimes due to the operator's gateway to send too slow, so that the front-end can first send the user's request to send all the table, the backend to slowly send.

Typically used in back-end send processes

Select top the From SMS Where status=0; SQL takes out the data that was not read.

In order to improve the back-end sending capability, multiple processes need to be deployed to send data from the table to be sent, which can sometimes cause the same record to be fetched simultaneously by multiple processes and sent.

Today, we looked at the MSDN of SQL Server and found that it was possible to ensure that each record was read only once by updating it at the same time through the deleted table (as if it were used in a trigger).

Declare @Rowid table (Rowid int);
BEGIN
SET ROWCOUNT 100; --Number of rows read at one time
--record status updates to be read first
Update Sms Set [status]= 1 output deleted.id into @Rowid Where [status] = 0;
--Read the record of the newly updated state
SELECT * from Sms where ID in (select Rowid from @Rowid);
END

Data in SQL Server tables is not read repeatedly under multithreading