Optimistic Affairs

Optimistic transaction principle

When an optimistic transaction starts, there will be no conflict detection or locking operations. The current relevant data will be cached to the corresponding memory area, and the data will be added, deleted and modified.

After completing the modification and entering the submission phase, it will be submitted in two steps:

Step 1: Use a column in the data to be written as a primary key column, lock the column and create a timestamp. Writes to the relevant rows after this time stamp are determined to be write conflicts.

Step 2: Write data, record the timestamp at this time, and unlock it.

Optimistic transaction model

MatrixOne Intelligence supports an optimistic transaction model. You do not lock a row when you read it using optimistic concurrency. When you want to update a row, the application must determine whether other users locked the row after reading it. Optimistic concurrent transactions are often used in environments where data contention is low.

In an optimistic concurrency model, if you receive a value from the database and another user modifies the value before you try to modify the value, an error occurs.

Model Example

The following is an optimistic concurrency example, 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 changes the “Bob” in the FirstName column to “Robert” and updates it to the database.

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

4. As shown in the table above, the value in the database during update matches the original value of user 2, indicating that the update is successful.

5. 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	Robert

6. At this time, user 1 encounters an optimistic concurrency conflict because the value “Robert” in the database no longer matches the original value “Bob” expected by user 1, and the concurrency conflict prompts that the update fails. The next step needs to decide whether to overwrite User 2’s changes with User 1’s changes or cancel User 1’s changes.