Forum OpenACS Development: Re: Simplification of Assessment

Collapse
9: Re: Simplification of Assessment (response to 1)
Posted by Dave Bauer on 02/25/06 12:05 AM
Could you give some more detail on what aspects of the content repository cause deadlocks?

I would assume you'd have to try updating the same row to cause a deadlock. What is inherent in the content repository that requires this?

Perhaps it is the way the assessment packages uses the CR and that it can be fixed without totally rewriting it.

At least one example of one query that causes this problem and why it deadlocks would be very helpful.

There are many experts in the OpenACS community. Let us work together to understand this and fix it.

Collapse
10: Re: Re: Simplification of Assessment (response to 9)
Posted by Derick Leony on 02/27/06 03:54 PM
Since in the tests we try to simulate 10 simultaneous users answering an assessment, the deadlocks show up when creating a new session for the third or fourth time in average:

ERROR: deadlock detected
DETAIL: Process 11236 waits for ShareLock on transaction 343145231; blocked by process 11227.
Process 11227 waits for ShareLock on transaction 343145260; blocked by process 11236.
SQL:

select content_item__new('25300952-25302114-5da3ce40-1f35-4da0-9221-6c719d3b3d94','24950685',NULL,NULL,NULL,'25300952',NULL,
'10.0.0.45','content_item','as_sessions',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'file')

but this is in a development instance.

In production, we get deadlocks at several queries:
Creating a new answer to a question:

ERROR: deadlock detected
DETAIL: Process 2988 waits for ShareLock on transaction 3017009599; blocked by process 2989.
Process 2989 waits for ShareLock on transaction 3017009471; blocked by process 2988.
SQL:

select content_item__new('24696117-24696253-25620217','25516644',NULL,NULL,NULL,'24303066',NULL,
'10.0.0.20','content_item','as_item_data',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'file')

Inserting a session revision:

ERROR: deadlock detected
DETAIL: Process 2975 waits for ShareLock on transaction 3017009950; blocked by process 3015.
Process 3015 waits for ShareLock on transaction 3017010057; blocked by process 2975.
SQL: insert into as_sessionsi
(revision_id,object_type,creation_user,creation_date, creation_ip, title, description, item_id, mime_type )
values('25620811','as_sessions','24266584',NULL,
'10.0.0.20','24266584-25620119-1ba075ea-2d08-4f81-b35f-2b8baca8d210',NULL, '25620807', NULL )

Inserting a item_data revision:

ERROR: deadlock detected
DETAIL: Process 2975 waits for ShareLock on transaction 3017009950; blocked by process 3015.
Process 3015 waits for ShareLock on transaction 3017010057; blocked by process 2975.
SQL: insert into as_sessionsi
(revision_id, object_type,creation_user, creation_date, creation_ip,title, description, item_id, mime_type )
values ('25620811', 'as_sessions', '24266584', NULL, '10.0.0.20', '24266584-25620119-1ba075ea-2d08-4f81-b35f-2b8baca8d210', NULL, '25620807', NULL )

and all of the above were from the same day (last Saturday).

As many people had said, I don't know why a simple insert could cause a deadlock, I wonder if it being an insert to a materialized view could be related, or if indexes like these:

"as_item_data_pk2" btree (session_id, section_id, as_item_id)
"as_item_data_pk3" btree (as_item_id, section_id, session_id)

could affect a DML operation performance. Some help from PG experts would be really appreciated 😊

I agree on the possibility of the Assessment package misusing the CR, because everything related to the CR (File-Storage, Evaluation, LORS) was working great here until Assessment was installed.