Hi all!

The other day at work I encountered a locking problem, namely, the message “There is a lock conflict while executing a transaction. The maximum waiting time for granting a lock has been exceeded.”

Obviously, there is no deadlock problem here, just some session put a lock and “forgot” to remove it. At the same time, the problem threatened with serious consequences - the document Sales of goods and services was not carried out. About 100 people work in the database at a time, and it is impossible to perform a typical and frequent operation!

There were two solutions - reboot the server or search for a failed session. The first solution is simple and quick, but, as someone already wrote here, you can reboot the server until you are fired. I decided to take the second path.

The first day - the problem appeared during the day; at first it seemed that the problem was with a remote user who had logged into the Configurator. It looked like the execution simply stopped at a point, and the blocking, of course, was not removed. After a couple of hours, we managed to release the configurator, but the problem did not go away. It was extremely undesirable to forcibly kill the configurator; perhaps they were working in it. After that, Google came into play. I found an article on this site that says how to find locks in an MS SQL DBMS, checked, there were no locks at the DBMS level. Strange. Next there were attempts to set up tech. magazine. I've set it up, now what? In 15 minutes, a couple of gigs of logs! How to read them, what to look for? Unknown.

I found an article on how to see what is blocked using SQL Trace. Even if I find it, what next? I need a session!

Closer to 16:00, when I realized that I couldn’t wait any longer, I made a reboot. Hoping that this would not happen again (and this was the first time in six months of work), I breathed a sigh of relief, everything worked. But in vain... The second day - the same situation. I dug for an hour and a half, again incomprehensible attempts to google and so on. No results. Reboot. At the end of the day it happened again. Well, I think it’s great, I’ll calmly come home and sit and dig deeper. I come home, everything is fine. Sadly.

On the third day I watched a webinar, they talked about an interesting and effective way to find a problem. I remembered, but the problem did not arise again. A week has passed and here it is - lockdowns again! I rub my hands and begin to act.

First, set up the journal. Yes, I can’t do without it, but now I know how to read it. We set two events: the first is TLOCK, the second is TTIMEOUT. The first displays all blocking events, the second shows blocking events that could not be installed within the allotted time. In fact, most likely just TTIMEOUT is sufficient.

We copy the technical log file to the designated place, fly into the program, call the blocking, receive a message and remove or rename the technical log file. We don’t need tons of information about other blockings!

Go to the rphost_PID folder, find the text files and search for the word TTIMEOUT. We see the line:

53:16.789126-0,TTIMEOUT,5,process=rphost,p:processName=*****,t:clientID=16536,t:applicationName=1CV8,t:computerName=ASUSM,t:connectID=17272,SessionID= 2242,Usr=*******,WaitConnections=8239

By the way, there can be several rphost_PID folders, it all depends on how many worker processes are running on the server.

And then everything is simple: look at the end of the line - WaitConnections = 8239, this is our CONNECTION number. We go to the server console, go to Connections, find this number and look at the session number. In my case, there were two sessions per user - the failed one and some other one. The session that the technical journal was pointing to crashed. And lo and behold! Everything worked, joy knows no bounds! But, as it turned out later, the session was not frozen :), they were working in it. Therefore, in the future, it is advisable to contact the user and warn.

In my opinion, a fairly standard solution to a fairly standard problem. It is not known why I didn’t come across it, perhaps because I had to look for it out of alarm, and when users didn’t press it, it wasn’t possible to carry out tests - there was no error.

It is not uncommon when working in 1C to receive the error “Lock conflict when executing transactions: The maximum waiting time for granting a lock has been exceeded.” Its essence lies in the fact that several sessions are trying to simultaneously perform similar actions, affecting the same resource. Today we will figure out how to fix this error.

A large number of operations performed

The first step when looking for reasons is to clarify how many concurrent users are in the information base in which such an error is generated. As we know, their maximum number can be quite large. This is both a thousand and five thousand.

The mechanism of locking and transactions is described in the developer's guide. They are used when multiple sessions access the same data simultaneously. It is logical that the same data cannot be changed by different users at the same time.

You should also check whether any of the users are running mass data change processing. This could be like, month end and the like. In this case, after the processing is completed, the error will disappear on its own.

Scheduled tasks

It is not uncommon for the cause of an error to lie in a system that processes large amounts of data. It is recommended to do such things at night. Set a schedule for performing such routine tasks outside of work hours.

In this way, users will work in a stable system, and the routine tasks themselves will be completed successfully, since the likelihood of conflicts with user sessions will be reduced.

"Hung sessions"

The problem of “stuck sessions” of users is familiar to almost everyone who has encountered 1C maintenance. The user could have left the program a long time ago, or closed a document, but his session still remains in the system. The problem is most often isolated and it is enough to end such a session through the administrator console. The same problems can arise with background jobs.

According to numerous comments on the Internet, such situations are more common when using network security keys. If the situation with “freezing sessions” is repeated systematically, this is a reason to thoroughly check and maintain the system and servers (if the database is client-server).

Errors when writing configuration

All standard configurations are developed by qualified specialists and experts. Each system is thoroughly tested and optimized for faster and more correct operation.

In this regard, the cause of the error may lie in suboptimal code written by a third-party developer. This could be a "heavy" request that will block data for a long period of time. There are also frequent cases of constructing algorithms with low performance and violation of logic.

There is a high probability that the locking conflict arose precisely because of developer errors if it arose after a program update. To check, you can simply “roll back” the improvements, or refactor the code.

When hundreds of users simultaneously work with programs and data, problems arise that are characteristic only of large-scale solutions. We are talking about problems caused by data blocking.

Sometimes users learn about blocking from messages indicating that they cannot write data or perform some other operation. Sometimes due to a very significant drop in program performance (for example, when the time required to perform an operation increases tens or hundreds of times).

Problems caused by blocking do not have a general solution. Therefore, we will try to analyze the causes of such problems and systematize options for solving them.

REASONS FOR TRANSACTION BLOCKINGS

Let's first remember what locks are, and at the same time figure out whether they are necessary. Let's look at a couple of classic examples of blockages that we encounter in life.

Example 1: buying a plane or train ticket. Suppose we voiced our wishes to the cashier. The cashier tells us the availability of available seats, from which we can choose the one we like best (if there are several of them, of course). While we select and confirm our agreement with the proposed option, these seats cannot be sold to anyone else, i.e. are temporarily “blocked”. If they were not blocked, then by the time of confirmation there could be a situation where the tickets we selected had already been sold. And in this case, the selection cycle may have an unpredictable number of repetitions. While we are choosing places, they have already been sold!.. While we are choosing others, and they are no longer there...

Example 2: buying something in a store or at a bazaar. We approached the counter and chose the most beautiful apple from the hundreds available. They chose it and reached into their pockets for the money. What will it look like if, at that moment, while we are counting the money, the apple we chose will be sold to a buyer who came up later than us?

Thus, blocking in itself is a necessary and useful phenomenon. It is thanks to blocking that we guarantee that actions are completed in one step. And what often leads to negativity is not the most successful software implementation, when, for example:

• an excessive number of objects (tickets, apples) are blocked;
• The blocking time is unreasonably extended.

EXCESSIVE BLOCKING IN TYPICAL 1C CONFIGURATIONS

On large projects, as a rule, we use 1C:Enterprise. Therefore, we will try to describe practical recommendations for solving blocking problems using the example of the 1C:Enterprise + MS-SQL combination.

The 8th generation of 1C:Enterprise provides very, very good parallelism of use. A huge number of users can work simultaneously with one configuration (that is, on one base) with normal servers and communication channels. For example, hundreds of storekeepers process the issuance or receipt of goods, economists simultaneously calculate labor costs for various departments, accountants carry out calculations and payroll, etc.

But there is a reason why there is an opinion to the contrary - the myth that with intensive simultaneous use, working with solutions based on 1C:Enterprise is uncomfortable or impossible. After all, as soon as standard solutions for 1C:Enterprise begin to be used by hundreds of users on an industrial scale, more and more often a window appears on the screen with a proud inscription: “Error when calling the context method (Write): Locking conflict when executing a transaction: ..." and further in depending on the type of SQL server used, something like “Microsoft OLE DB Provider for SQL Server: Lock request time out period exceeded. ...".

Almost all standard solutions in the proposed out-of-the-box implementation are configured for automatic lock management. “Automatic” here can be perceived as “paranoid”. Just in case, when processing any document, we block everything that may be somehow connected with it. So it turns out that when one user does something (and sometimes just writes it down), the rest can only wait.

I will express my opinion why 1C decided not to configure its standard solutions for high parallel use. The labor costs for such modification are not high - several “man months”, which is not significant on the scale of 1C. It seems to me that the reason is different.

Firstly, such a modification significantly complicates the processing of all documents. This means that for those consumers who use 1C for small tasks, without any gain there will be only a drawback - the difficulty of modifying the standard configuration will become more complicated. Statistics at the same time suggest which category of clients is the main feeding trough for 1C...

The second reason is buried in the typical basic settings of SQL servers, for example, MS-SQL, which is still used more often than others. It just so happened that priorities in the settings were given to saving server RAM, rather than reducing blocking. This leads to the fact that, if it is necessary to lock several rows, the SQL server makes a “memory- and processor-saving” decision - to lock the entire table at once!..

These shortcomings in standard solutions or the specifics of the database server setup used are often identified with problems caused by blocking. As a result, technical deficiencies lead to very significant organizational problems. After all, if an employee is given a reason to be distracted from work or to justify why the work could not be done, a minority will work effectively. Well, a message about blocking transactions or a “braking” program is an ideal justification for why something could not be done.

RECOMMENDATIONS FOR ELIMINATING EXCESSIVE BLOCKINGS FOR 1C:ENTERPRISE

What to do if solving the problems of excessive locking is so important?

At the final stage of implementation of all large complexes, it is necessary to carry out fine-tuning to eliminate unnecessary transaction blocking. It is critical to resolve problems that may arise due to insufficiently developed blocking conditions or implementation techniques.

Because This operation is extremely important and must be performed constantly. Therefore, to simplify such modifications, we have developed a number of basic recommendations that we try to adhere to. Recommendations received and tested through the experience of a significant number of large-scale implementations.

1. If the DBMS or development system you are using (for example, 1C:Enterprise) uses the automatic data locking mode by default, refuse automatic lock management. Configure blocking rules yourself, describe the criteria for blocking entire tables or individual rows.
2. When developing a program, whenever possible, access the tables in the same order.
3. Try not to write to the same table multiple times within the same transaction. If this is difficult, then at least minimize the time interval between the first and last write operation.
4. Analyze the possibility of disabling lock escalation at the SQL server level.
5. Clearly inform users about the reasons why they cannot perform any actions if they are due to blocking. Give accessible and understandable recommendations on what to do next.

If you look carefully at the recommendations, it becomes clear that such testing is appropriate not only for 1C:Enterprise, but for any systems. It doesn’t matter at all what language they are written in and what database server they work with. Most of the recommendations are universal in nature, and therefore are equally valid when using 1C:Enterprise and for “home-written” programs or other “boxed” ERP systems.

P.S. Did you know that we offer professional assistance with updating 1C at the best price?

Tags to search:

• Transaction locks
• Removing blockages
• 1C locks
• Blocking
• Lock conflict
• Lock contention during transaction

I couldn’t write down changes to transfer to the distributed database, so I contacted 1C support and offered the following. I solved it simply by rebooting the application server and the SQL server. In general, you can check the box “Blocking regulatory
tasks included"
It also helped without rebooting.

Routine operations at the DBMS level for MS SQL Server

Instructions for performing routine operations at the DBMS level.

The information applies to the client-server version of 1C:Enterprise 8 when using the MS SQL Server DBMS.

General information

One of the most common reasons for suboptimal system operation is incorrect or untimely execution of routine operations at the DBMS level. It is especially important to carry out these regulatory procedures in large information systems that operate under significant load and serve a large number of users simultaneously. The specificity of such systems is that the usual actions performed by the DBMS automatically (based on settings) are not enough for effective operation.

If any symptoms of performance problems are observed in a running system, you should check that the system is correctly configured and regularly performs all recommended routine operations at the DBMS level.

The implementation of regulatory procedures should be automated. To automate these operations, it is recommended to use the built-in MS SQL Server tool: Maintenance Plan. There are also other ways to automate these procedures. For each regulatory procedure, this article provides an example of how to configure it using the Maintenance Plan for MS SQL Server 2005.

It is recommended to regularly monitor the timeliness and correctness of these regulatory procedures.

Update statistics

MS SQL Server builds a query plan based on statistical information about the distribution of values ​​in indexes and tables. Statistical information is collected based on a part (sample) of data and is automatically updated when this data changes. Sometimes this is not enough for MS SQL Server to consistently build the most optimal plan for executing all queries.

In this case, query performance problems may occur. At the same time, the query plans show characteristic signs of non-optimal operation (non-optimal operations).

In order to guarantee the most correct operation of the MS SQL Server optimizer, it is recommended to regularly update the MS SQL database statistics.

To update statistics for all database tables, you must run the following SQL query:

exec sp_msforeachtable N"UPDATE STATISTICS ? WITH FULLSCAN"

Updating statistics does not lead to table locking and will not interfere with the work of other users. Statistics can be updated as often as necessary. It should be taken into account that the load on the DBMS server will increase while updating statistics, which may negatively affect the overall performance of the system.

The optimal frequency for updating statistics depends on the size and nature of the load on the system and is determined experimentally. It is recommended to update statistics at least once a day.

The above query updates statistics for all tables in the database. In a real-life system, different tables require different statistics update rates. By analyzing query plans, you can determine which tables are most in need of frequent statistics updates, and set up two (or more) different routine procedures: for frequently updated tables and for all other tables. This approach will significantly reduce the statistics update time and the impact of the statistics update process on the operation of the system as a whole.

Setting up automatic statistics updates (MS SQL 2005)

Launch MS SQL Server Management Studio and connect to the DBMS server. Open the Management folder and create a new maintenance plan:

Create a subplan (Add Subplan) and name it “Updating statistics”. Add the Update Statistics Task to it from the taskbar:

Set up a statistics update schedule. It is recommended to update statistics at least once a day. If necessary, the frequency of statistics updates can be increased.

Configure task settings. To do this, double-click on the task in the lower right corner of the window. In the form that appears, specify the name of the database (or several databases) for which statistics will be updated. In addition, you can specify for which tables statistics should be updated (if you don’t know exactly which tables you need to specify, then set the value to All).

Statistics must be updated with the Full Scan option enabled.

Save the created plan. When the date specified in the schedule arrives, the statistics update will start automatically.

Clearing the procedural cache

The MS SQL Server optimizer caches query plans for re-execution. This is done in order to save time spent on query compilation if the same query has already been executed and its plan is known.

It is possible that MS SQL Server, based on outdated statistical information, will build a non-optimal query plan. This plan will be stored in the procedural cache and used when the same query is called again. If you update statistics but do not clear the procedure cache, SQL Server may choose an old (suboptimal) query plan from the cache instead of building a new (more optimal) plan.

To clear the MS SQL Server procedural cache, you need to run the following SQL query:

This query should be run immediately after the statistics are updated. Accordingly, the frequency of its execution must coincide with the frequency of statistics updates.

Setting up procedural cache clearing (MS SQL 2005)

Since the procedural cache must be cleared every time statistics are updated, it is recommended to add this operation to the already created subplan “Updating statistics”. To do this, open the subplan and add the Execute T-SQL Statement Task to its schema. Then you should connect the Update Statistics Task with an arrow to the new task.

In the text of the created Execute T-SQL Statement Task, you should specify the request “DBCC FREEPROCCACHE”:

Defragmentation of indexes

When working intensively with database tables, the effect of index fragmentation occurs, which can lead to reduced query performance.

sp_msforeachtable N"DBCC INDEXDEFRAG (<имя базы данных>, ""?"")"

Defragmenting indexes does not lock tables and will not interfere with the work of other users, but it does create additional load on SQL Server. The optimal frequency of performing this routine procedure should be selected in accordance with the load on the system and the effect obtained from defragmentation. It is recommended that you defragment indexes at least once a week.

You can defragment one or more tables, rather than all tables in the database.

Setting up index defragmentation (MS SQL 2005)

In the previously created maintenance plan, create a new subplan named “Reindexing”. Add the Rebuild Index Task to it:

Set the execution schedule for the index defragmentation task. It is recommended to perform the task at least once a week, and if the data in the database is highly variable, even more often - up to once a day.

Reindexing database tables

Table reindexing involves a complete rebuild of the database table indexes, which leads to significant optimization of their performance. It is recommended to regularly reindex your database tables. To reindex all database tables, you need to run the following SQL query:

sp_msforeachtable N"DBCC DBREINDEX (""?")"

Reindexing tables locks them for the entire duration of their operation, which can significantly affect the user experience. In this regard, it is recommended to perform reindexing during minimal system load.

After reindexing is completed, there is no need to defragment the indexes.

Setting up table reindexing (MS SQL 2005)

In the previously created maintenance plan, create a new subplan named Index Defragmentation. Add the Rebuild Index Task to it:

Set the execution schedule for the table reindexing task. It is recommended to perform the task during minimal system load, at least once a week.

Set up the task by specifying a database (or several databases) and selecting the required tables. If you don't know exactly which tables should be specified, then set the value to All.