Refactor code for proper use of SQLAlchemy transactions

Registered by Nikolay Markov

We may run into some strange race conditions or deadlocks now because of bad implementation of transaction management in Nailgun.

We should refactor code to use select for update in cases of modification or deleting objects from DB. For deadlock prevention all selections with locking must be with same ordering. Also selecting with locking from chains of tables must be ordered. For example all selections with locking must be in following way:
BEGIN
SELECT * FROM Tasks WHERE cluster_id=NN ORDER BY ID FOR UPDATE;
SELECT * FROM Clusters WHERE cluster_id=NN ORDER BY ID FOR UPDATE;
COMMIT;

Refactoring steps:
1. Implement monitoring of order of rows locking in tables;
2. Refactor handlers from DB models to nailgun objects. Use locking in handlers;
3. Refactor tasks management from DB models to nailgun objects. Use locking in task management

Locking objects rules:
1. Allowed locking order of records from different tables defined in deadlock_detector.py. If code is written with wrong locking order - exception is raised. Checking of locking order is enabled only in DEVELOPMENT mode.
2. All selected objects with locking must be ordered by the same field and in the same ordering direction in all code. For example we order objects by id in ASC order when select them with locking.

Blueprint information

Status:
Started
Approver:
Evgeniy L
Priority:
Medium
Drafter:
Nikolay Markov
Direction:
Needs approval
Assignee:
Alexander Kislitsky
Definition:
Discussion
Series goal:
Accepted for future
Implementation:
Good progress
Milestone target:
milestone icon next
Started by
Nikolay Markov

Related branches

Sprints

Whiteboard

(?)

Work Items

This blueprint contains Public information 
Everyone can see this information.

Subscribers

No subscribers.