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

1. Create two simple tables T1 _ deadlock and t2_deadlock, each containing only one field a

SQL > create table t1_deadlock (an int)

The operation has been performed

Elapsed time: 6.906 milliseconds. Execution number: 23.

SQL > create table t2_deadlock (an int)

The operation has been performed

Elapsed time: 3.168 milliseconds. Execution number: 24.

two。 Initialize only one piece of data in each table

SQL > create table t2_deadlock (an int)

The operation has been performed

Elapsed time: 3.168 milliseconds. Execution number: 24.

SQL > insert into t1_deadlock values (1)

Number of rows affected 1

Elapsed time: 0.566 milliseconds. Execution number: 25.

SQL > insert into t2_deadlock values (2)

Number of rows affected 1

Elapsed time: 0.803 milliseconds. Execution number: 26.

SQL > commit

The operation has been performed

Elapsed time: 1.057 milliseconds. Execution number: 27.

3. Update the record "1" in table t1_deadlock to "1000" in the first session session1 and do not submit

SQL > update t1_deadlock set a = 1000 where a = 1

Number of rows affected 1

Elapsed time: 1.608 milliseconds. Execution number: 28.

4. Update the record "2" in table t2_deadlock to "2000" in the second session session2 and do not submit

SQL > update t2_deadlock set a = 2000 where a = 2

Number of rows affected 1

Elapsed time: 3.345 milliseconds. Execution number: 29.

5. At this point, no problems occurred. OK, now notice the following phenomenon. Let's go back to the session session1 and update the record of t2_deadlock.

SQL > update t2_deadlock set a = 2000 where a = 2

The phenomenon of "lock waiting" ("blocking") occurs here, simply because this operation has already been performed on this data in session2, and row-level locks have been added to the row in session2.

Note that this is "lock waiting", not "deadlock". Notice the difference between the two concepts!

6. The "deadlock" we are concerned about is about to make a grand appearance: in the session session2, update the record of t1_deadlock

SQL > update t1_deadlock set a = 1000 where a = 1

Update t1_deadlock set a = 1000 where a = 1

[- 6403]: deadlock.

Elapsed time: 310.980 milliseconds. Execution number: 0.

7. What do all these phenomena mean?

Description: DM will automatically handle deadlocks, not turn a blind eye to them.

8. Summary

Deadlocks differ from blocking in that deadlocks consist of two or more blocked transactions that form a waiting ring, each waiting for the other transaction to release the lock. For example, transaction 1 puts an exclusive lock on table T1 and the second transaction puts an exclusive lock on table T2. When transaction 1 requests an exclusive lock of T2, it will be in a waiting state and will be blocked. If T2 requests the exclusive lock of table T1 at this time, T2 is also in the blocking state. At this point, the two transactions are deadlocked, and the DM database chooses to sacrifice one of the transactions.

Reference: DM8 system Administrator's Manual 19.8Lock waiting and deadlock detection

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.