Implement similar railway ticket lock function on the database

To achieve the effect of the railway ticket, if there is a ticket to lock the ticket, if the guests do not buy and then put the ticket back to the pool, so as to ensure that the visitors to the ticket will be able to buy tickets as long as the ticket can be bought. We can do this by adding a locked flag field to the database, but here we can try to see if the functionality of the database itself is able to achieve this requirement.

The first thing to think about is a transaction, if a table starts updating, then another session queries the target, it waits for a session of the previous update data to release the transaction, because the normal SQL read operation is actually using the "committed read" transaction isolation level, can guarantee the accuracy of the data. But this does not meet our needs, we hope that another query session will be able to quickly query, while filtering out the "lock ticket" data.

In the update session, execute the following query:

Begin TranUpdate Users  set name= ' AAA '  where uid= 610062

A new query window opens, a new connection session is opened and a query is executed:

At this point, the Discovery query session waits until the update session commits the transaction or rolls back the transaction:

Begin TranUpdate Users  set name= ' AAA '  where uid= 610062rollback

The above update session is executed again, but the transaction is not committed, at which point our query session can use NOLOCK and will not let the query wait.

However, the previous updated data is also queried, but the old data, does not meet our needs. You can use row locks and filter locks at this time:

SELECT * from LocalDB.dbo.Users with  (rowlock,xlock,readpast)

Successful implementation of demand!

In addition, the Netizen listens to the wind to blow the rain also provides the other idea, the detailed look below query code:

Use Mastergo---Creating a test database (snapshot) Create Snapshot_testgo---Activate data row versioning ALTER DATABASE Snapshot_test  set Allow_ Snapshot_isolation ongouse snapshot_testgo--1. Creating a Test Table CREATE table Tbreadlevel (ID int,name nvarchar (20))--2 new record insert Tbreadlevelselect 1, ' Test ' unionselect 2, ' snapshot test ' goselect id,name as ' pre-modification data ' from Tbreadlevelgo--3 open transaction begin TranUpdate Tbreadlevelset name= ' jack_upd_ snapshot ' where id=1--5 open another connection set Transaction isolation Level Snapshotselect * from Tbreadlevel

Implement similar railway ticket lock function on the database