for it the destroy instance method. The semantic difference is subtle, but becomes clear when before_destroy callbacks are specified or dependent associations are created, that is, child objects that need to be automatically destroyed along with the parent.

Lock Database

The term locking refers to a technique that prevents multiple concurrent users from updating the same records. When loading table rows into a model, ActiveRecord does not apply locking at all by default. If in some Rails application Only one user can update data at any given time, so there is no need to worry about locks.

If there is a chance that several users can simultaneously read and update data, then you must take care

competitiveness. Ask yourself, what collisions or race conditions might occur if two users try to update the model at the same time?

There are several approaches to accounting for concurrency in database applications. ActiveRecord has built-in support for two of them: optimistic And pessimistic blocking. There are other

Other options, such as locking entire tables. Each approach has many strengths and weaknesses, therefore, for the most reliable operation of the application, it makes sense to combine them.

Optimistic lock

An optimistic blocking strategy is to detect and resolve conflicts as they arise. It is usually recommended for use in cases where collisions occur infrequently. Optimistic locking does not lock database records at all, so the name is just confusing.

Optimistic locking is a fairly common strategy because many applications are designed so that any user only changes data that is conceptually theirs alone. Therefore, contention for updating the same record is unlikely. The idea behind optimistic blocking is that since collisions are rare, they need to be handled only if they actually occur.

If you control the database schema, then implementing optimistic locking is quite simple. It is enough to add an integer column to the table with the name lock_version and a default value of 0:

Database lock
	AddLockVersionToTimesheets< ActiveRecord::Migration
add_column:timesheets, :lock_version, :integer, :default => 0

remove_column:timesheets, :lock_version end

The very presence of such a column changes the behavior of ActiveRecord. If some record is loaded into two instances of the model and saved with different meanings attributes, then the update of the first instance will be completed successfully, and when updating the second, the use of

Including ActiveRecord::StaleObjectError.

To illustrate optimistic locking, let's write a simple standalone test:

class TimesheetTest< Test::Unit::TestCase

fixtures:timesheets, :users

def test_optimistic_locking_behavior first_instance = Timesheet.find(1) second_instance = Timesheet.find(1)

first_instance.approver = users(:approver) second_instance.approver = users(:approver2)

assert first_instance.save, "The first instance was successfully saved"

assert_raises ActiveRecord::StaleObjectError do second_instance.save

The test passes because we expect an ActiveRecord::StaleObjectError exception when we call save on the second instance. Note that

that the save method (without exclamation point) returns false and does not raise exceptions if the save fails due to data control errors. Other issues, such as a write lock, may result in an exception. If you want the column containing the version number to be called something other than lock_version , change this setting using the set_locking_column method. To make this change global, add the following line to your environment.rb file:

ActiveRecord::Base.set_locking_column "alternate_lock_version"

Like other ActiveRecord settings, this one can be set at the model level by including the following declaration in the model class:

class Timesheet< ActiveRecord::Base set_locking_column "alternate_lock_version"

Handling StaleObjectError Exception

Once you add optimistic locking, you certainly don't want to stop there, because otherwise the user who ends up on the losing end of the collision resolution will simply see an error message on the screen. We must try to handle the StaleObjectError exception with the least possible losses.

If the data being updated is very important, you might want to spend time building a user-friendly system that somehow stores the changes the user tried to make. If the data is easy to re-enter, then at least inform the user that the update did not take place. Below is the controller code that implements this approach:

def update begin

@timesheet = Timesheet.find(params[:id]) @timesheet.update_attributes(params[:timesheet])

# redirect rescue ActiveRecord::StaleObjectError somewhere

flash[:error] = "The report card was modified while you were editing it." redirect_to:action => "edit", :id => @timesheet

Optimistic locking has a number of advantages. It does not require special DBMS mechanisms, and it is relatively simple to implement. As you can see from the example, very little code was required to handle the Stale ObjectError exception.

The disadvantages are mainly due to the fact that update operations take a little longer, since the lock version must be checked. In addition, users may be unhappy because they learn about the error only after sending data, which would be extremely undesirable to lose.

Pessimistic lock

For pessimistic blocking special support is required from the DBMS (however, the most common DBMSs have it). During the update operation, some rows in the tables are locked. This prevents other users from reading the records that will be updated, thereby preventing the potential for working with outdated data.

Blocking and Unblocking

If you prefer to use some other utilities to create database backups or just make a regular copy of the database as a backup, then nbackup's lock/unlock mode comes into play. "Block" in in this case means that the main database file is temporarily frozen, not the inability to make changes to the database. As in backup mode, changes are committed to a temporary delta file; when unlocked, the delta file is merged with the main database file.

As a reminder: nbackup.exe is located in the bin subfolder of the folder where the Firebird DBMS is installed. Typical locations, for example, are C:\Program Files\Firebird\Firebird_2_0\bin (Windows) or /opt/firebird/bin (Linux). The utility does not have GUI; You launch it from command line(or from batch file, or from another application).

Database locking and self-backup

A typical scenario is the following:

Lock a database using a parameter -L(Lock):

nbackup[-U <пользователь> -P <пароль> ] -L <база_данных>

1. Now you can create a backup copy, compress the database file (and much more you can do with the contents of the database file) using your favorite programs. Simply copying the file is also acceptable.

   Unlock database using parameter -N(uNlock):

   nbackup[-U <пользователь> -P <пароль> ] -N <база_данных>

The last command will also merge the delta file, where all changes to (meta)data during the lock were written, with the main database file.

The created backup will contain data that was current at the time the blocking began; this data does not depend on the duration of the blocking, as well as on the period of time that has passed from the start of the blocking to the start of the backup.

Attention

What applies to backup/restore also applies to lock/unlock: do not use lock/unlock on multi-file databases. Until the situation changes, keep nbackup away from multi-file databases!

Restoring from a backup made after running "nbackup -L"

A copy of a locked database is also locked, so you cannot simply use the copy as a working database. If your original database is damaged or lost, and you need to restore the database from a copy you made yourself, proceed as follows:

1. Unzip/copy/restore the database file using the utilities you use.

   Now unlock the database, but Not with parameter -N, and with the parameter -F(Fixup):

   nbackup -F <база_данных>

Why are there two command line options, -N And -F?

• When using the parameter -N first determines whether there have been any changes since the database was locked (after using the -L) and the temporary delta file and the main database file are merged. After this, the database is put into normal read/write mode and the temporary file is deleted.

  When using the parameter -F only changes the status flag of a self-restored database to the "normal" value.

So you use:

parameter -N after creation backup copy on your own (to return the status flag after previously blocking a file with the parameter -L);

parameter -F after independent recovery from such a backup.

Comment

It didn't work out very well that the last parameter -F named after the word Fixup: its purpose is not to fix anything, but only unlock database. Parameter -N(uNlock, unlock), on the other hand, not only unlocks the database, but also makes some changes to it (implements the changes made to the database). However, we will have to work with what we have.

Today we will talk about blocking both at the 1C 8.3 and 8.2 level, and at the DBMS level. Data locking - required element any system in which the number of users is more than one.

Below I will describe how blocking works and what types they are.

A lock is information that a system resource has been seized by another user. There is an opinion that blocking is a mistake. No, locking is an inevitable measure in a multi-user system to share resources.

Only redundant (“unnecessary”) locks can cause harm to the system; these are the locks that block unnecessary information. You need to learn how to eliminate such blockages; they can lead to suboptimal system operation.

Locks in 1C are conventionally divided into object and transactional.

Objective ones are, in turn, optimistic and pessimistic. And transactional ones can be divided into managed and automatic.

Object locks 1C

This type of locking is completely implemented at the 1C platform level and does not affect the DBMS in any way.

Get 267 video lessons on 1C for free:

Pessimistic locks

This blocking is triggered when one user has changed something in the directory form, and the second is trying to change an object in the form in the same way.

Optimistic locks

This lock compares versions of an object: if two users opened a form, and one of them changed and wrote down the object, then when writing to the second, the system will give an error that the versions of the objects are different.

Transactional locks 1C

The 1C transactional locking mechanism is much more interesting and more functional than the object locking mechanism. This mechanism actively involves locking at the DBMS level.

Incorrect operation of transaction locks can lead to the following problems:

• lost change problem;
• dirty reading problem;
• uniqueness of reading;
• reading phantoms.

These problems were discussed in detail in the article about.

Automatic transaction locks 1C and DBMS

IN automatic mode The DBMS is entirely responsible for locking. The developer in this case is absolutely not involved in the process. This makes the work of a 1C programmer easier, but creating information system For large quantity users per automatic blocking undesirable (especially for PostgreSQL DBMS, Oracle BD - when modifying data, they completely lock the table).

For different DBMSs, different degrees of isolation are used in automatic mode:

• SERIALIZABLE for the entire table – file mode 1C, Oracle;
• SERIALIZABLE on records – MS SQL, IBM DB2 when working with non-objective entities;
• REPEATABLE READ on records – MS SQL, IBM DB2 when working with object entities.

Managed mode of transactional locks 1C and DBMS

The developer of the application solution at the 1C level takes full responsibility. In this case, the DBMS installs enough high level isolation for transactions - READ COMMITED (SERIALIZABLE for a file DBMS).

When performing any operation with the database, the 1C lock manager analyzes the possibility of blocking (seizing) a resource. Locks of the same user are always compatible.

Two locks are NOT compatible if: they are installed by different users, they are of incompatible types (exclusive/shared), and they are installed on the same resource.

Physical implementation of locks in a DBMS

Physically, locks are a table that is located in the database called master. The lock table itself is named syslockinfo.

The table conventionally has four fields:

1. Blocking session ID SPID;
2. what exactly is blocked by RES ID;
3. lock type - S,U or X MODE(in fact, there are 22 types in MS SQL, but only three are used in conjunction with 1C);
4. blocking state - can take a value GRANT(installed) and WAIT(waiting his turn).

When working with object data (directories, documents, charts of accounts, etc.), the 1C:Enterprise system provides two types of object locks: pessimistic and optimistic. They allow you to perform holistic changes to objects while multiple users work simultaneously.

Object pessimistic locking

A pessimistic object lock is designed to prevent changes to an object's data until the lock is released. The system (with the help of appropriate object form extensions) automatically sets a pessimistic lock at the moment when the user tries to change the object data. If another user then tries to edit the same object, for example, they will receive a message indicating that the object could not be locked. When the form is closed by the user, this blocking will be removed.

Let's look at an example.
Let’s enter the training information database under the user “Vasiliev V.V.”, open the form of the document “Receipt of goods 00000000001 dated 06/01/2016” and make changes in the comment field (Fig. 1.3).

Without saving the document, let’s enter the information database under the user “Ivanov I.I.”, open the same document and try to make changes to any of the document details. The system will not allow us to make changes and will display an error message (Fig. 1.4).

It follows that pessimistic locking guarantees that the user, having started changing object data, will be able to write these changes to the information base.

The developer, using the built-in language, can use pessimistic locking. Using the “Lock()” method, a pessimistic object lock is set, and the “Unlock()” method is used to remove it.

Let's look at another example. Under the user “Vasiliev V.V.” in the “Regulatory and Reference Information” section, open the directory element “Warehouses” with the name “Warehouse No. 1” and make changes to the name (Fig. 1.5).

Without saving, switch to the window information base, which was launched under the user “Ivanov V.V.”, in the “Regulatory and Reference Information” section we will open the “Object Deletion” processing. Let us select as the object to be deleted, select the directory element “Warehouses” with the name “Warehouse No. 1” and click “Delete object” (Fig. 1.6).

As a result, the system will allow you to delete this element directory and the system will not display an error message. The point is that a locking operation does not prevent an operation to modify or delete an object in the database.

In order to ensure that a locked object cannot be changed or deleted, it is necessary to check whether the object is locked.

There are two ways to check:

1. The Locked() method is used to check whether a database object is locked by the current session. This method does not provide the ability to check whether an object is blocked at all.
2. To check if a database object is locked, the “Lock()” method is generally used. An attempt to lock a locked object causes an exception, which can be handled by the “Try…..Exception…..EndTry” construct.

Pessimistic blocking in controlled forms

When working with managed forms, the "Lock()", "Unlock()" and "Locked()" methods may not be suitable due to the specifics of the managed application.

The point is that these methods are used for database objects. The database object exists only on the server. It turns out that the developer will have to make a server call, obtain a database object by converting the main form attribute using the form method “Form AttributesValue”. Next, one of the object’s methods “Lock()”, “Unlock()” or “Locked()” is called. But this method locking will be useless if the goal is for the object to be locked while the form is open, since the resulting object will live until the end of the server call.

To work with locks from a managed form, you must use the following methods: “LockFormDataForEditing()” and “UnlockFormDataForEditing()”. These methods are used to block or unlock data of the main form details.

Let's look at an example. In the “Regulatory and Reference Information” section, we will open any element of the “Nomenclature” directory under the user “Vasiliev V.V.”, make changes to the name and without saving under the user “Ivanov I.I.” Let's open the same directory element. When you try to make changes, the system will display an error message.

Further in the form of a directory element under the user “Vasiliev V.V.” Let’s click on the “Unblock” button (Fig. 1.7) and try again to make changes to this directory element under the user “Ivanov I.I.” In this case, the system will allow you to make changes and record the directory element.

To disable pessimistic locking in managed forms, in the main attribute property, you must clear the “Stored data” flag. This flag determines whether the main attribute data will be blocked during interactive editing or not (Fig. 1.8).

Object Optimistic Locking

An optimistic lock is a check that is performed before an object is written to the database. The object has a property “DataVersion”, which, together with the object, is read from the database. Optimistic locking performs, before writing, a comparison of the value of the "DataVersion" property of the object that is in random access memory with the value of the “DataVersion” property of an object located in the database. If the values ​​of the “DataVersion” property of objects are different, then optimistic locking prevents the object from being written to the database and displays an error message.

Let's look at an example.

In the “Regulatory and Reference Information” section, we will open any element of the “Nomenclature” directory under the user “Vasiliev V.V.”, then without closing the element form under the user “Ivanov I.I.” in the “Regulatory and Reference Information” section we will open the “Change Object” processing.

In processing, select the same item and click the “Change Object” button. This command will add “!!!” at the end of the name (Fig. 1.9).

After the change, let's try to write open element nomenclature directory under the user “Vasiliev V.V.” The system will issue a warning that these objects have been changed or deleted and will not allow recording this object(Fig. 1.10).

To disable optimistic locking, before writing an object in RAM, you must compare the version with the version of the database object. If the data versions are different, then we get the object from the database and transfer the changes to it, then write it.

Also in some DBMSs you can set a time interval timeout– exceeding the time limit. When such an interval is entered, the SQL statement fails and returns an error code if it fails to acquire the required lock within the specified amount of time.

The database administrator can manually set lock types, levels, and times timeout depending on the application program.

Lock modes for specific application(programs) can also be installed programmatically using the appropriate object methods Recordset VBA language. In this case, in a specific case, the program settings will already be in effect, and not the general window settings Options. This option provides more flexible blocking options, but is only mentioned in this course and not discussed in detail.

Methods for managing locks in MS Access

Microsoft Access is a multi-user DBMS. It has certain locking mechanisms to maintain sharing to data and resolving conflicts when accessing data.

There are three types of record locking in an Access database.

· Lock absent. Icon for this mode. If two users made changes to a record at the same time, the one who saves the changes first can do so. When the second user tries to save his changes, the Write Conflict dialog box appears, asking him to either save his record and destroy the first user's changes, copy his changes to the buffer, or discard his changes. This option is called optimistic blocking, since it is based on the assumption that when editing a conflict with its alternative outcomes will not occur.

· Lock changeable record.
Access locks what is being changed in this moment entry without allowing other users to change it. Records located nearby on the disk may also be blocked. If another user tries to change a locked entry, they will not be able to

A marker for a blocked entry will appear.

This option is called pessimistic blocking, since it is assumed that a conflict will definitely occur. Disadvantage: the duration of the lock is not limited, the lock is released only after the transaction is completed.

· Lock all entries.
Microsoft Access Locks all records of a form or object in Sheet view so that other users cannot change or lock the records. This setting imposes severe restrictions and clearly reduces performance.

To set record blocking parameters:

1. Select a team Office → OptionsAccess. A dialog box will appear OptionsAccess.

2. Expand the tab Additionally, chapter Additionally.

In Group Default opening mode you can select the mode General access or mode Exclusive access- opening an existing database for exclusive use by one user.

In Group Default lock the required switch is installed.

There is a choice of one of three blocking levels:

• Absent.

• Locking editable records. Only the entry being edited is blocked.
• Blocking all entries. All table records displayed in the form or table are locked.

Update period (s) The number of seconds after which Microsoft Access will automatically update records in Datasheet or Form view.

Number of update retries The number of times Microsoft Access tries to save a modified record that is locked by another user. Possible values ​​are from 0 to 10. Default value: 2.

The configured options will take effect when the database is reopened using the command File, Open.

If you need locking at the record level when opening a database, then you need to check the box Opening databases using record-level locking. If default blocking at the page level is required, then this check box must be cleared.

In addition to these options for blocking records, there is another method that consists of setting desired mode working with data in the form. To do this, you need to open the form in design mode and on the tab Data Properties windows for a property Blocking records select one of the blocking options: missing, all records, modified record.

Test questions and exercises

1. Define a transaction. Give examples of transactions.
2. How are transactions performed in SQL?
3. Name and explain the meaning of transaction parameters? How to set parameter values?
4. Describe each transaction isolation level:
   READ INCOMMITED, READ COMMITED, REPEATABLE READ, SERIAIZABLE.
5. What is a transaction log? What fields does it contain? What and how is it used?
6. Why do DBMSs use data locks when processing transactions?
7. Name and describe the locking levels used?
8. How are data blocking modes set in the MS ACCESS DBMS?