Talk about how to ensure that only one user is editing a certain record when processing WEB data updates?

rcloud

When using the with (UPDLOCK) option when querying, we added an update lock to the record when querying the record, indicating that we are about to update the secondary record. Note that the update lock and shared lock are not in conflict, that is, other Users can also query the contents of this table, but it conflicts with update locks and exclusive locks. So other update users will block. If we execute this code in another window, the waifor delay clause is also not added. Execute on both sides After the completion, we found that two cards were successfully registered. Perhaps we have discovered the disadvantage of pessimistic locking: when a user performs an update transaction, other update users must wait in line, even if that user does not update the same record.

rcloud

Optimistic locking solution

-First, we add a column F_TimeStamp to the Card table, which is of type varbinary (8). However, this value will automatically increase when updated.

alter table Card add F_TimeStamp timestamp not null


declare @CardNo varchar (20)
declare @timestamp varbinary (8)
declare @rowcount int

Begin Tran

       -Get the card number and original timestamp value
        select top 1 @ CardNo = F_CardNo,
                     @ timestamp = F_TimeStamp
        from Card
        where F_Flag = 0
        
        -Delay 50 seconds to simulate concurrent access.
        waitfor delay '000: 00: 50'

        -Register the card, but compare whether the timestamp has changed. If there is no change. The update is successful. If it changes, the update fails.

        update Card
        set F_Name = user,
            F_Time = getdate (),
            F_Flag = 1
        where F_CardNo = @ CardNo and F_TimeStamp = @ timestamp
        set @rowcount = @@ rowcount
        if @ rowcount = 1
        begin
                print 'Update succeeded!'
                commit
        end
        else if @ rowcount = 0
        begin
                if exists (select 1 from Card where F_CardNo = @ CardNo)
                begin
                        print 'This card has already been registered by another user! '
                        rollback tran
                end
                else
                begin
                        print 'This card does not exist!'
                        rollback tran
                end
        end

rcloud

In another window, execute the code without waitfor. After the registration is successful, return to the original window. We will find that the prompt it displays is the prompt for this card to be registered by another user. Obviously, so we can also To avoid the phenomenon of two users registering a card at the same time. At the same time, another advantage of using this method is that no update lock is used, which increases the concurrent processing capacity of the system.

everever30

You can first put the id of this record into a table. When editing, first determine whether there is already an id in the table. If there is, it means that the record is being edited. After editing, delete the id from the table. Of course, in order to prevent abnormal situations, you must have an unlock function. For example, when the id cannot be deleted from the table when an exception occurs, then you must provide a mechanism to delete the id from the table.

Another way is to add a version column, which is incremented by 1 for every update. Of course, this case allows simultaneous editing, but you can choose to overwrite or refuse to update when updating.

qdpoplee

declare @CardNo varchar (20)
Begin Tran

       -Choose an unused card
        select top 1 @ CardNo = F_CardNo
        from Card with (UPDLOCK) where F_Flag = 0
        
        -Delay 50 seconds to simulate concurrent access.
        waitfor delay '000: 00: 50'

       -Register the card you just selected.

        update Card
        set F_Name = user,
            F_Time = getdate (),
            F_Flag = 1
        where F_CardNo = @ CardNo

commit

missun

thanks for your replies,
First of all, it is processed under WEB, not WINFORM
My requirement is that when other people are updating a record, other users are not allowed to update this record. For the transaction that everyone said, if you use a lock, then when the customer quits abnormally (refers to closing the browser or shutting down), it cannot be returned. Roll the transaction.

江南小色

Both sql server and oracle use version control to implement transactions.
'Other people are not allowed to update this record while others are updating a record' This can only be controlled by the transaction (depending on the specific isolation level), for example, the update process is unexpectedly long, and another person updates ,How to do. How does he know that this record is being updated.

missun

Any other good suggestions?

sukdevils

I have a stupid way to build another table in the database, with ID, TABLENAME, TABLEID, if you want to edit the class, first determine whether there is in this table, if not, insert a class, and write a trigger , Delete a certain article after 5 minutes, so that it will not deadlock, and delete this article when you are finished editing, remember to give it points

wewe0618

What is the purpose?
Why not add [user] to [time]