[Original] Reading SQL server data without repeating multiple threads

When sending text messages or emails, we often use a table to store the data to be sent, multiple threads in the background constantly read the data to be sent from the table and send the data to the History Table after sending the data. This ensures that the data in the table to be sent is usually not too much. If the structure of the table to be sent is:

Create Table SMS (ID int not null identity (1024), content varchar (), status int not null, createtime datetime );

Status value: 0 not read 1 read

The advantage of this design is that the frontend will not receive messages due to slow sending at the backend, such as sending text messages, or sending too slowly due to the gateway of the carrier, in this way, the front-end can put all user sending requests in the table to be sent, and the backend will send them slowly.

 

Generally

Select Top 100 * from SMS where status = 0; this SQL statement extracts unread data.

To improve the backend sending capability, You need to deploy multiple processes to retrieve data from the table to be sent at the same time, which sometimes results in the same record being retrieved by multiple processes at the same time, and sent.

 

Today, I checked the msdn of SQL Server and found that the data can be retrieved through the ed table (just like the one used in the trigger) first, to ensure that each record is read only once.

Declare @ rowid table (rowid INT );
Begin
Set rowcount 100; -- number of rows read at a time
-- The status of the record to be read is updated first.
Update SMS set [Status] = 1 Output deleted. ID into @ rowid where [Status] = 0;
-- Read the record of the updated status
Select * from SMS where ID in (select rowid from @ rowid );
End

 

-----------------------

Retained the source for forwarding