[RUBY] Lock_version may be used for tables that tend to access and edit the same record at the same time

Introduction

I read the Rails Guide and was curious, so I actually touched it. That memo.

When Mr. A and Mr. B edit the same record in the app, if the same record is accessed at exactly the same timing, if Mr. A updates the record and then Mr. B updates, Mr. B will edit the contents of Mr. A. It will be overwritten without confirming. If this is an update with the same attribute, and if it is another attribute, the edit will be rewound, which is not a good situation.

Active Record provides lock_version for this issue. lock_version adds a column to the table that acts as an edit history count for that instance, and by referencing its value If you use lock_version, you can generate an error in the above case.

how to use

To use it, create a column called lock_version in the table.

t.integer :lock_version, default: 0

Operation check

p1 = Memo.find(1)
p2 = Memo.find(1)
# => #<Memo id: 1, text: "hello", lock_version: 0>

Of course, lock_version is 0 when it is still called Then update p1.

p1.update(text:'good bye')
# => #<Memo id: 1, text: "good bye", lock_version: 1>

SQL

   (0.3ms)  BEGIN
  Memo Update (13.4ms)  UPDATE `memos` SET `text` = 'good bye', `lock_version` = 1 WHERE `memos`.`id` = 1 AND `memos`.`lock_version` = 0
   (3.9ms)  COMMIT

look_version has been rewritten to 1. You can also see that the SQL condition for update has lock_version = 0. Next, let's update p2. The lock_version of the p2 instance remains 0 because it has not been reloaded.

p2.update(text:'say hello')
# => ActiveRecord::StaleObjectError (Attempted to update a stale object: Memo.)

SQL

   (0.7ms)  BEGIN
  Memo Update (1.0ms)  UPDATE `memos` SET `text` = 'say hello', `lock_version` = 1 WHERE `memos`.`id` = 1 AND `memos`.`lock_version` = 0
   (2.4ms)  ROLLBACK

You're getting an error. I don't think SQL itself will appear even if an error is executed, so it seems that an error is returned with the trigger that there is no line that affected rails. I will not read the source this time, so if you read it, please share it!

Operation check (Web)

As with RailsApi, let's put lock_version as a hidden parameter in the form. Without it, the backend will not be able to compare lock_version and will not generate an error.

<input type="hidden" value="2" name="memo[lock_version]" id="memo_lock_version">

I added the above, opened two tabs to edit the same record and updated each, and the one who updated later got an error safely.

After that, let's handle the error as described in Rails Api. There are various ways to deal with it.

--If two people edited different attributes, update them in order and when they edit the same attribute, display it on the screen and confirm with the user -Update after making sure that other attributes updated by people before you are not affected

And. I wonder if this area depends on how the data is used on the app.

Finally

I haven't used it in practice, so I'd be happy if you could share something like "I'm doing this in my house!"!

Reference article

Rails Guide What is Optimistic Locking