Shulou(Shulou.com)06/02 Report--

The concept of 1SQL SERVER lock

Shared lock: used to read the lock added by the resource. Resources with shared locks cannot be modified. By default, the shared lock is read and the resource is released immediately.

Exclusive lock: incompatible with other locks, including other exclusive locks, which are used to modify data. When an exclusive lock is added to a resource, other requests to read or modify the resource are blocked until the exclusive lock is released.

Update lock: it is a combination of shared lock and exclusive lock, which is used to update data. when updating data, you first need to find the updated data, which can be understood as a shared lock on the searched data. When you find the data that needs to be modified, you need to put an exclusive lock on the modified resource.

Sql server avoids the problem of deadlock by updating locks, because shared locks and shared locks are compatible. By updating locks and sharing locks, data lookup is not affected when updating locks, while update locks and update locks are incompatible, thus reducing the possibility of deadlocks.

Give an example of the lock described above:

1.1. Create an environment

-create a table

Createtable student

(

Studentidint

Sname nvarchar (10)

Sex nchar (1)

)

-- insert students

Insert student values (1 'Zhang Fei', 'female')

Insert student values (2) 'Zhen Ji', 'male')

Insert student values (3Jing 'recruiting', 'female')

Insert student values (4) 'Wang Wu', 'female')

Insert student values (5Jing'Li Si', 'male')

1.1. Exclusive lock

. Create two new connections

Execute the following statement in the first connection

Begintran

Update dbo.student set sname=' Wang Jingzheng 'where studentid=1

Waitfordelay'00:00:30'

Committran

Execute the following statement in the second connection

Begintran

Select * from dbo.student where studentid=1

Committran

If the above two statements are executed at the same time, the select query must wait for update to finish execution for 30 seconds.

1.1. Shared lock

Execute the following statement in the first connection

Begintran

Select * from dbo.student with (holdlock)

Where sex=' girl'

Waitfordelay'00:00:30'

Committran

Execute the following statement in the second connection

Begintran

Select studentid,sname from dbo.student where sex=' girl'

Update dbo.student set sname=' Han Xu 'where sex=' female'

Committran

If both statements are executed at the same time, the select query in the second connection can be executed, and UPDATE must wait for a thing to be released

The shared lock can not be executed until it is converted to an exclusive lock, waiting for 30 seconds.

1.1. Deadlock

1.1. Update lock

Update lock

Execute the following statement in the first connection

Begintran

Select * from dbo.student with (updlock) where sname=' Huarong'

Waitfordelay'00:00:30'

Update dbo.student set sname=' Bai Nannan'

Committran

Select * from sys.dm_tran_locks

In the second connection

Begintran

Select * from dbo.student with (updlock) where sname=' Huarong'

Waitfordelay'00:00:30'

If an update lock is also added to the second connection, the current query is blocked until the connection releases the update lock.

If the update lock is not added, the record of the update lock can be read directly.

Sometimes I need to control that a record is not allowed to be updated after I read it, so I can add an update lock to all queries that want to process the current record to prevent it from being modified by other transactions. Minimize the impact of transactions.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.