Multi-threaded SQL statements that do not repeatedly read data from SQL Server tables

Multi-threaded SQL statements that do not repeatedly read data from SQL Server tables

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

C # How to implement multi-threaded reading of the same data table so that users can obtain non-Repeated Records!

This can be determined by the data modification time.
For example:
The table has a field read time.
When this customer reads 10 data records, the update read time is the current time.
Then, when the next customer reads the data, it determines that there is no updated data within half an hour. OK.
However, this is also a bad thing, that is, it is likely that the first customer is still using the 10 data records in half an hour.

If you modify the data, you can use the lock function to lock the database table so that you do not have to worry about repeated submission.

Hope the landlord can solve the problem as soon as possible ~~~

SQL server database query records that are not repeated

I don't understand what you want to implement...

Supplement:

Select replyorder. *, revisitlist. * from replyorder
Inner join (select Document No., return visit no. = min (return visit no.) from revisitlist group by document No.) as a on a. Document No. = replyorder. Document No.
Inner join revisitlist on revisitlist. Document No. = replyorder. Document No. and revisitlist. Return Visit No. = a. Return Visit No.
This method is not recommended for large data volumes. It is best to partially redundant data.