Millions of the amount of data quickly query record and delete? Multiple concurrent terminal into co-CodePudding

There is a table, the table has more than 100 ten thousand records,
The table name: HorseInfo
ID
UserID
Num
CreateTime

Operation:
Article 1: this table, there are millions of data, and at the same time there are new data in the insert;
2: there are more than 1000 terminals, at the same time each terminal to read a new record, delete immediately after read the record, in order to ensure that each record, can only be a terminal read,

If only use the select top 1 ID, userID, Num from HorseInfo where userID desc=1 order by ID, read then delete, then it is very easy to matinee multiple terminals at the same time read the record of the same ID,

In order to avoid a record can only be a terminal read, how to write SQL statements, the highest efficiency, and shall not affect the new data insertion, prevent process deadlock and so on questions,

CodePudding user response:

Multi-purpose trigger, add two fields updateclient update client ID owerclient belonging to the client ID

1 client read immediately sent after the completion of the UPDATE statement to UPDATE updateclient, updateclient field UPDATE triggers to start working, if it updateclient owerclient is null content updates to owerclient, otherwise it is not updated, thus the first UPDATE updateclient is that the owner of the record, may be complicated by many, but other updates only updateclient owerclient once the UPDATE is always the first

2 about deleting, owerclient is not null can be removed, with a trigger or schedule a task can be achieve
3 can also select owerclient don't as empty as the record is not available, the said delete can also, then followed by 100 w records are not many,

CodePudding user response:

Remember: trigger belongs to a last resort, there are other ways, cannot be used until the last moment,

1, add a index,

 the create index ix_HorseInfo_userId on HorseInfo (userId, id desc);

2, read and delete with a transaction,

 BEGIN TRY 
The BEGIN TRAN 
DECLARE @ id INT 
SELECT the TOP 1 @ id=id from HorseInfo WITH (ROWLOCK XLOCK) where userID=1 order by id DESC 
SELECT * FROM HorseInfo WHERE id=@ id 
The DELETE FROM HorseInfo WHERE id=@ id 
COMMIT TRAN 
END the TRY 
The BEGIN CATCH 
The ROLLBACK TRAN. 
DECLARE @ errMsg NVARCHAR (500) 
SELECT @ errMsg ERROR_MESSAGE=(); 
RAISERROR (@ errMsg, 16, 1); 
END the CATCH