Pessimistic affairs

The principle of pessimistic affairs

When a pessimistic transaction starts, conflict detection or locking operations will be performed. When no conflict or lock is detected, a column in the data to be written will be regarded as the primary key column, and the column will be locked and a timestamp will be created. Writes to the relevant rows after this timestamp are determined to be write conflicts.

Caches the current relevant data to the corresponding memory area, and add, delete and modify the data. If there is a lock in the current table, it enters a waiting state. When the waiting timeout, the waiting transaction will be cancelled.

After completing the modification, enter the submission stage, write the data, and record the timestamp at this time to unlock.

Pessimistic transaction model

MatrixOne Intelligence defaults to pessimistic transactions.

When you read a line using pessimistic concurrency, it will not lock the line. When you want to update a row, the application must determine whether other users have locked the row. Pessimistic concurrent transactions are often used in environments where data contention is high.

In the pessimistic concurrency model, if you receive a value from the database and another user encounters a lock before you try to modify the value, the waiting transaction will be forced to be cancelled after exceeding the transaction waiting time (5 minutes) set by MatrixOne Intelligence.

Deadlock

In pessimistic transactions, there may be a situation where two or more transactions lock the resources required by each other, making each transaction unable to proceed. This situation is called a deadlock. The deadlock can be immediately terminated by artificial intervention in one of the transactions, for example through a manual Kill ​​session, otherwise the transaction can only be waited for the longest waiting time.

The deadlock example is shown in the figure below:

Model Example

Below is an example of pessimistic concurrency, which will show you how MatrixOne Intelligence resolves concurrent conflicts.

1. At 1:00 pm, User 1 reads a row from the database, with the following values:

   CustID LastName FirstName
   101 Smith Bob
   

   Column name	Original value	Current value	Value in database
   CustID	101	101	101
   LastName	Smith	Smith	Smith
   FirstName	Bob	Bob	Bob

2. At 1:01 pm, User 2 reads the same row from the database.

3. At 1:03 pm, User 2 changed the “Bob” of the FirstName column to “Robert”, which is in an uncommitted state.

   Column name	Original value	Current value	Value in database
   CustID	101	101	101
   LastName	Smith	Smith	Smith
   FirstName	Bob	Robert	Bob

4. At 1:05 pm, User 1 changes the “Bob” of the FirstName column to “James” and tries to update.

   Column name	Original value	Current value	Value in database
   CustID	101	101	101
   LastName	Smith	Smith	Smith
   FirstName	Bob	James	Bob

5. At this time, user 1 encounters a pessimistic concurrency conflict because the row where the value “Robert” in the database is already locked and needs to wait for the next operation of user 2.

6. At 1:06 pm, User 1 commits the transaction. At this time, user 2 cancels the waiting state to start the transaction, but because the corresponding FirstName is no longer matched, user 2’s transaction will fail to update.