My situation is, when a user requests a table a record, the record locking, and update a field identity, to show the record has been used, now stored procedure realization method below

Declare @ t nvarchar (20)
Declare @ error int=0
The begin tran
Select the top 1 @ t=telNo from table with (rowlock) where isUser=0 and DATEDIFF (ss, inDt, getdate ()) & lt; Desc=60 order by id
Update top table set isUser=1 (1) the where telNo=@ t
Set @ error +=@ @ error
If (@ error<> 0)
The rollback tran
The else
Commit tran

Troublesome everybody have a look at what's right or wrong, thank you

CodePudding user response:

Single table, individual records with affairs, not too big necessary,

Tell from the business, you this kind of lock is of no use, when two people (assuming that A and B) at the same time to A record, A former B after modified, see A, B is the same change the contents of the former, but the result of the modification completion, A is A face of meng,

Suggestion:
This kind of situation, it is ok to add a timestamp,
Take a record, record id and the timestamp,
Update record, update tableName set c=c where id=@ @ id and the timestamp=@ t
If the update number of lines is 0, that someone has been changed, the front tip: please refresh the page, review the content again after the modification,

A little bit more simple, can need not timestamp, increase updateTime, a field in a DateTime types,
Update, by the way the updateTime=GETDATE (), the current time,
Take record, write down the id, updateTime,
Update record,
If not the exists (select 1 from tableName where id=@ id and updateTime=@ updateTime)
Means someone has changed, the front tip are the same as above,