After switching to PostgreSql 9.1 serializable transaction level for all transactions during posting single document errors
40001:ERROR: could not serialize access due to read/write dependencies among transactions
Reason code: Canceled on identification as a pivot, during write.;
started to appear in log file.
Code which causes them is below.
Code involves only single document (in this example id 95162) . Is document is probably not accesed by others.
How to fix or diagnose this error ?
tasutud1 is temporary table created in transaction earlier:
CREATE TEMP TABLE tasutud1 (dokumnr INTEGER, tasutud NUMERIC(1)) ON COMMIT DROP
Other tables are permanent tables updated and accessed by 7 users concurrently.
Code where exception occurs is :
CREATE TEMP TABLE ids(dokumnr INT) ON COMMIT DROP;
INSERT INTO ids VALUES(95162);
analyze ids;UPDATE DOK set
kinnitatud = TRUE,
doksumma=CASE WHEN TRUE THEN COALESCE(doksumma.doksumma,0) ELSE 0 END,
tasumata =CASE WHEN TRUE AND dok.krdokumnr IS NULL and
dok.doktyyp IN ('G','O') THEN
doksumma.doksumma-COALESCE(doksumma.tasutud,0) ELSE 0 END
FROM
(SELECT
ids.dokumnr,
SUM( CASE WHEN rid.toode is NULL OR LENGTH(RTRIM(rid.toode))>2 OR toode.grupp<>'S' or
(STRPOS(toode.klass,'T')!=0 AND STRPOS(toode.klass,'E')=0)
THEN
ROUND(COALESCE(rid.hind,0)*CASE WHEN COALESCE(rid.kogus,0)=0 THEN 1 ELSE rid.kogus END*CASE WHEN COALESCE(rid.kogpak,0)=0 THEN 1 ELSE rid.kogpak END,2) ELSE 0 END ) AS doksumma,
max(tasutud1.tasutud) as tasutud
FROM ids
JOIN dok USING(dokumnr)
JOIN rid USING(dokumnr)
LEFT JOIN toode USING(toode)
LEFT JOIN tasutud1 ON tasutud1.dokumnr=ids.dokumnr
WHERE not rid.fifoexpens and not rid.calculrow
and (not dok.inventuur or rid.kogus<>0 )
GROUP BY 1
) doksumma
left join bilkaib on bilkaib.dokumnr=doksumma.dokumnr and bilkaib.alusdok='LO'
WHERE dok.dokumnr=doksumma.dokumnr
Should this code split into multiple commands to find which part causes exception or other idea ?
Andrus.