SQL Server table data 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, and then transfer the data to the history table, so that the data to be sent to the table is not usually 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 front-end receives a request to send a message fails because the backend sometimes sends too slowly. such as the business of sending text messages, sometimes due to the carrier's gateway to send too slow, so that the front-end can first send the user's request all placed in the table to be sent by the backend to slowly send.

Typically used in back-end send processes

Select Top * from SMS Where status=0; This SQL fetches data that has not been read.

In order to improve the back-end sending capability. It is necessary to deploy multiple processes at the same time to fetch data from the table to be sent, which sometimes causes the same record to be fetched by multiple processes at the same time. and send the case.

Check out the MSDN for SQL Server today. Discovery can be removed by first updating the same time through the deleted table (as if it were used in a trigger). To ensure that each record is read only once.

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

a lot of other attention Fuyi technology blog: Http://blog.csdn.net/fuyifang

SQL Server table data is not read repeatedly under multithreading