With CSV logging, that is always the case, but with stderr logging, you have to change log_line_prefix: In order to enable us to trace statements, we have to make sure that the process ID and transaction number are logged with each statement. Debugging deadlocks by logging on the database side The information can be anything that allows you to tell which transaction the statement belongs to. Pdata FROM parent WHERE pid = 1 FOR UPDATE SELECT /* function "add_data", source file "app.c", line 1234 */ ![]() The trick here is to add a comment to the query, like This is also a method that requires that you can modify the application. Debugging deadlocks by annotating queries on the application side You are not always in that lucky position. However, that requires that you either have control over the application code, or that the application has sufficiently well-designed tracing facilities. If the application logs information about each error and each database transaction started, it is easy to determine the transactions that caused the problem. There are several approaches: Debugging deadlocks by logging on the application side But how do we get the statements from the transactions? Techniques for debugging deadlocks Great, with our complete knowledge we could fix the problem. The lock name FOR UPDATE is counter-intuitive, since it suggests that this is the correct lock for an UPDATE, and it is a common trap for PostgreSQL beginners. Note that a FOR UPDATE row lock is only required if you want to DELETE a row, or if you intend to update a column with a unique or primary key constraint on it (a key). FOR NO KEY UPDATE“, and there will be no more lock conflicts and consequently no deadlock. Once we know that, the solution is simple: change “ SELECT. FOR UPDATE statements conflict with the FOR KEY SHARE locks from the other transaction, which leads to the deadlock.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |