What is SQL Lock Conflict?

Imagine the database is like a house and the sessions connected to the database are like the people living in that house.

In this house there is a rule:

• At a time, each object in the house can only be used by one person.
• If many people want to use the same item, we will apply the principle of “first come, first use”, the people behind will have to wait.

Eg:

• Two people A and B are present in building C
• Both of them wanted to use the timekeeper located at the door.
• Mr. A is the one who arrives at 9 o’clock and B arrives at 9 o’clock.
• According to the principle of “first in, first served”, now everything will happen as follows:
• A will be allowed to use the timekeeper first
• B will be forced to wait for A to finish using it before using it. This wait can take place LONG or FAST is up to A to decide.

The phenomenon I have just described above is the image of SQL Lock Conflict (or some brothers simply call it SQL Lock or Transaction Lock or Lock Conflict, some people may quickly call it Lock, no matter what they call it). anyway I want people to understand its essence.).

SQL Lock Conflict occurs when multiple transactions change the same record in the same TABLE.

The transaction that comes first will “LOCK” (the term is called LOCK) the changed records (ROWS), this mechanism must be in place to ensure data integrity.

All subsequent transactions will not be able to change the locked records (ROWS).

These “locks” will be released when the transaction holding the “lock” performs a COMMIT or ROLLBACK.

Analysis of SQL Lock Conflict (Transaction Lock Conflict) examples

Example 1

We have 2 sessions connected to the Database Test of Wecommit.

These two sessions both want to update the data of the EMPLOYEES table.

The first session wants to edit the employee’s salary with code 11 (EMP_ID=11)

The second session wants to edit the employee’s salary with code 11 (EMP_ID=10)

Ask that:

• Know that the first session executes the UPDATE command at 9 o’clock, and the second session executes the command 1 minute after the first session (at 9 o’clock).
• Does the second session have to wait for the first session to complete before it can be done?

Answer:

• In the above case, the second session does not have to wait for the first session at all
• The first session when executed will LOCK the record with ROW_ID = 11
• The second session wants to make edits on the record with ROW_ID=10, so it is completely unaffected with the first session.
• The example image when 2 sessions are performed in the Database is as follows

Example 2

We have 2 sessions connected to the Database Test of Wecommit.

These two sessions both want to update the data of the EMPLOYEES table.

These two sessions both want to update the data of the EMPLOYEES table.

The first session wants to edit the employee’s salary with code 11 (EMP_ID=11)

The second session wants to edit the employee’s salary with code 11 (EMP_ID=11)

Ask that:

• Know that the first session executes the UPDATE command at 9 o’clock, and the second session executes the command 1 minute after the first session (at 9 o’clock).
• Does the second session have to wait for the first session to complete before it can be done?

Answer:

• The second session will have to wait for the first session to complete before it can be executed.
• The second session will have the feeling of being “HOLD”, the essence of this “HANG” is because the EMP_ID=11 record is being “LOCKED” by the first session.

Some questions to ponder more deeply

Analyze the following situation and answer the questions

We have 2 sessions connected to the Database Test of Wecommit.

These two sessions both want to update the data of the EMPLOYEES table.

The first session wants to edit the salary of the employee whose code is less than 11 (EMP_ID < 11)

The first session wants to edit the employee’s salary with a small code of 3 (EMP_ID=3)

Ask that:

• Know that the first session executes the UPDATE command at 9 o’clock, and the second session executes the command 1 minute after the first session (at 9 o’clock).
• Does the second session have to wait for the first session to complete before it can be done?

When a Transaction is locking the EMP_ID=11 record to make edits, can another Transaction want to SELECT the EMP_ID = 11 value?

Try Demo of Lock Conflict phenomenon with Insert and Delete statements

Expert notes for readers

When a SQL statement is slow, it is not necessarily because the statement is “bad” or the statement consumes too many resources (CPU, I/O, RAM), sometimes the phenomenon “slow”, “hang” that’s because the statement that is trying to modify a record is “LOCKed”, and must wait for the transaction holding “LOCK” to release.