Forum OpenACS Development: Re: Re: Simplification of Assessment

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.