[learning] SQL Basics-012-Lock

1. Type

Exclusive locks: record, row level

Shared locks: table level, preventing table structure from being modified using DDL

Shared lock [S lock]

Also known as read lock, if transaction T adds an S lock to the data object A, transaction T can read A but cannot modify A. other transactions can only add S lock to An instead of X lock until T releases the S lock on A. This ensures that other transactions can read A, but no modifications can be made to A until T releases the S lock on A.

Exclusive lock [X lock]

Also known as write lock. If transaction T adds an X lock to the data object A, transaction T can read An or modify A, and other transactions can no longer lock A until T releases the lock on A. This ensures that other transactions cannot read and modify A until T releases the lock on A.

2 、 select for update

Select * from emp for update; returns the result after waiting for row lock to be released

Manual lock protection to lock only specified row records

Select for update nowait returns an error if it is locked

3. Deadlock

Popular explanation: an is waiting for the resource release of B, and B is waiting for the resource release of A.