pgsql-function called twice in the same second -> Double Insert -> Error
От | Peter Alberer |
---|---|
Тема | pgsql-function called twice in the same second -> Double Insert -> Error |
Дата | |
Msg-id | 001301c29553$736861c0$5be0d089@ekelhardt обсуждение исходный текст |
Ответы |
Re: pgsql-function called twice in the same second -> Double Insert -> Error
Re: pgsql-function called twice in the same second -> |
Список | pgsql-general |
Hi there, I am administering an online (web) learning portal (OpenACS + Postgres) where students can solve multiple choice exercises online. Every result of a exercise usage is saved to the database. Students can see which exercises they have solved successfully, and other stats. The whole exercise and testing module works quite good since weeks, but from time to time I noticed error messages. "cannot insert duplicate key into unique index..." Now I think have found the problem: There is a db-function that saves the result of every exercise usage, and this function is sometimes called twice in the exact same second (when the server is extremely busy). Probably the user clicked two times on the form submit button in the browser. The exercise usage process consists of 2 steps: When the student starts the exercise the corresponding entry is "locked". (the id of the exercise is inserted into a locking table) Every exercise usage generates a usage_id that is recorded in the locking table, so the exercise can not be deleted while used. When an answer to the exercise is received, the exercise usage data is inserted into another table and the locking entry is removed. So the function first tries to retrieve data from the locking table, saves (inserts) that data in another table and deletes the entry from the locking table. If this function is called twice at the same time, both runs get the data from the locking table and both try to do the insert. in the first run, the commit succeeds, but in the second run an error is generated and the function fails. Is this something I can circumvent on the Postgres (DB) Level or do I have to deal with that at the application level (Aolserver - TCL) ? Many TIA! peter PS: The db-function I mentioned is attached.
Вложения
В списке pgsql-general по дате отправления: