Skip to main content
R
ronalynm4283
February 5, 2026
Question

How to Prevent Deadlocks in a Stored Procedure That Updates a Materialized Table from a View

  • February 5, 2026
  • 5 replies
  • 0 views

I have a stored procedure (called from Appian’s Execute Stored Procedure smart service) that reads from a view and then creates, updates, or deletes rows in a materialized table that is also sourced from that same view.

The view is heavily used by multiple applications, so I created the stored procedure smart service to read the updated values from the view and reflect them in the materialized table. This process is triggered by various create or update forms across different applications.

Most of the time the stored procedure works, but sometimes it fails with the following error:

Deadlock found when trying to get lock; try restarting transaction

How can I reduce or avoid these deadlocks?


5 replies

stefanhelzle0001
stefanhelzle0001
February 5, 2026

I do not understand why you directly modify data in that materialized view. Isn't the idea that it updates itself?

R
ronalynm4283Author
February 5, 2026
We’re currently using MariaDB, which I believe doesn’t support materialized views. Because of this limitation, we created a process to copy the records from the view into a separate database table. I apologize for the confusion
J
jessek855119
February 5, 2026

Is it not possible to use the Data Fabric for your purposes? The Data Fabric is, effectively, a materialized layer on top of your database. It has a small learning curve, and older applications need to be refactored into it, but it serves its purpose extremely well.

J
jessek855119
February 5, 2026

This is said with the caveat that the best answer will depend on your database technology, what exactly your use case is, and how complex the queries are to the materialized tables (not the view). Understanding the latter is not easy, depending on how many queries there are to the tables.

You'll need to understand what the deadlock is actually on - my instinct says that is mostly likely the 'deletes', but the database logs hopefully provide a real answer.

Here are things I have done in the past in similar situations:

  1. I enabled pre-emptive table locks within a transaction. To enable table locking, the query of the view likely needs to be directly called from the stored procedure.
  2. No amount of table locking will help if the stored procedure attempts to update a table is used in the query of the view - this is a circular dependency. It is unlikely that this is part of your issue, but it's worth mentioning.
  3. I had to do a bit of surgical data model refactoring to isolate the locked tables so that I only needed to lock 2 tables rather than 6-ish. I could get away with this because the stored procedure simply needed to create global identifiers for the other tables (and other systems) to use, rather than performing something major like a transaction table update.

With proper locking it was possible to have Appian process an average of 1200 web API requests in a burst during business hours, with global identifier results returning to the caller of the API in under 1000ms and also (eventually, with refinement) without any performance impacts to the users.

shubhama926776
shubhama926776
February 6, 2026

I want to help you with few points to avoid this :
Always SELECT/UPDATE rows sorted by primary key.
Add indexes on view's join/filter columns and copy table keys.
Queue concurrent Appian process triggers.
Add retry logic in process model for deadlock errors.

Terms & ConditionsAccessibility statement