Talking about the lock of database

Database is no stranger to program apes, but how much do you know about database locks? Database lock directly affects data performance, how to ensure data is not deadlocked and improve database performance under the premise of large concurrency? How to lock, when to lock, plus what locks, you can manually forcibly specified by hint, but most of the database system is automatically determined. That's why we can write SQL without the lock.

Let's briefly talk about the locks in the database, taking SQL Server as an example:

Types of database locks:

1. Share lock (Shared lock)

What is a shared lock, as the name implies, is the sharing of resources, so there is no time between shared locks can execute one or more query statements, shared locks are used to read

The database stipulates that shared and exclusive locks cannot coexist on the same resource at the same time. If a shared lock must be completed before a shared lock can be added to an exclusive lock

2. Exclusive Lock (Xlock)

An exclusive lock can be simply understood as a conditional update or query if you force an exclusive lock on a table you can use the Handwriting keyword (xlock) to include: SELECT * from #t1 (xlock) This forces the resource to be given a single lock, and the advantage is that we are doing some updated SQL Statements (including things, storage, etc.), excluding the possibility of deadlocks (Holdlock)

3. Update Lock (Updlock)

To resolve deadlocks, an update lock is introduced. The new lock means: "I just want to read it now, you can read it, but I'll probably do the update in the future, I've got the qualification from the shared lock (for reading) to an exclusive lock (to update)." A thing can only have one update lock to qualify for this.

T1:beginselect * FROM table (Updlock)      (plus update lock) Update table set column1= ' Hello '  (focus: Here T1 do update, there is no need to wait for T2 to release anything, Instead, upgrade the update lock to an exclusive lock and then perform an update) T2:beginselect *               from table (T1 plus updates lock does not affect T2 Read) Update table set column1= ' World '  ( T2 update needs to wait for T1 update to finish before executing)

Shared and update locks can be on the same resource at the same time. This is known as shared locks and update locks are compatible.

4. Deadlock (Holdlock) Holdlock means to add a shared lock until the end of the thing is released.

5. Exclusive Lock (Exclusive Locks)

This simple, that is, other transactions can not read, and can not change the exclusive lock lock resources
Column 1

T1:    Update table set column1= ' Hello ' where id<1000t2:    Update table set column1= ' World ' where id>1000 assume T1 , T2 Subsequently, the process T1 an exclusive lock on the id<1000 record. But the T2 update will not be blocked.

Example 2 (assuming IDs are self-growing and continuous) T1:    Update table set column1= ' Hello ' where id<1000t2:    Update table set column1= ' World ' Where id>900 as an example 1,T1, T2 immediately, T1 plus the exclusive lock will block the T2 update.

6. Intent Lock (Intent Locks)

   An intent lock is a sign at the door of a house (for example, a table) that indicates that someone in the room (for example, some record) is locked. Another person wants to know if someone in the house is locked, do not go into one of the house to check, look directly at the door logo on the line.

   When a row in a table is added to an exclusive lock, the table can no longer be added to the table lock. How does the database program know that the table cannot be added to the table lock? One way is to determine whether each record of the table has an exclusive lock, and the other way is to check whether the table itself is intentionally locked at the table level, and does not need to be judged. Obviously the latter is highly efficient.

----------------------------------------T1:    BEGIN tran       SELECT * FROM table (xlock) where id=10  --meaning to Id= 10 This line imposes an exclusive lock T2:    BEGIN tran       SELECT * FROM table (TABLOCK)     -meaning to add a table-level lock       assuming T1 first execution, T2 after execution, T2 execution, to add a table lock, In order to determine whether the table lock can be added, the database system to determine the table every row of records whether there is an exclusive lock, if it is found that one row has an exclusive lock, it is not allowed to add a table lock. It's just that the efficiency is too low. In fact, the database system does not work this way. When T1 's select executes, the system adds an exclusive lock to the table's id=10 line, and also silently adds an intent exclusive lock (IX) to the entire table, and when T2 executes the table lock, only to see that the table has an intent exclusive lock exists, it waits directly, without needing to check the resources.

7. Program Lock (Schema Locks)

----------------------------------------ALTER TABLE .... (Add schema locks, called schema Modification (SCH-M) LOCKSDDL statement will add SCH-M lock this lock does not allow any other session to connect to the table. I can't even get to the table, not to mention what SQL statements to execute on the table. Example:----------------------------------------use JDBC to send a new SQL statement to the database, the database must be compiled first, during compilation, also lock, called: Schema stability ( SCH-S) Locksselect * FROM TableA compiling this statement, other sessions can do anything to the table TableA (Update,delete, add exclusive locks, etc.), but cannot do DDL (such as ALTER TABLE) operations.

8.Bulk Update Locks

Mainly used in bulk data-conducting (for example, bcp commands similar to IMP/EXP in Oracle). Not difficult to understand, programmers often do not need to care, do not repeat.

Talking about the lock of database