Re: [SQL] possible row locking bug in 7.0.3 & 7.1
От | Forest Wilkinson |
---|---|
Тема | Re: [SQL] possible row locking bug in 7.0.3 & 7.1 |
Дата | |
Msg-id | 01032722080000.08138@bartok обсуждение исходный текст |
Ответ на | Re: [SQL] possible row locking bug in 7.0.3 & 7.1 (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-hackers |
On Tuesday 27 March 2001 15:14, Tom Lane wrote: > Forest Wilkinson <fspam@home.com> writes: > > session1<< create function nextid( varchar(32)) returns int8 as ' > > session1<< select * from idseq where name = $1::text for update; > > session1<< update idseq set id = id + 1 where name = $1::text; > > session1<< select id from idseq where name = $1::text; > > session1<< ' language 'sql'; > > [ doesn't work as expected in parallel transactions ] [snip] > The workaround for Forest is to make the final SELECT be a SELECT FOR > UPDATE, so that it's playing by the same rules as the earlier commands. > But I wonder whether we ought to rethink the MVCC rules so that that's > not necessary. I have no idea how we might change the rules though. > If nothing else, we should document this issue better: SELECT and SELECT > FOR UPDATE have different visibility rules, so you probably don't want > to intermix them. My, that's ugly. (But thanks for the workaround.) If I remember correctly, UPDATE establishes a lock on the affected rows, which will block another UPDATE on the same rows for the duration of the transaction. If that's true, shouldn't I be able to achieve my desired behavior by removing the initial as follows: create function nextid( varchar(32)) returns int8 as ' update idseq set id = id + 1 where name = $1::text; select id fromidseq where name = $1::text; ' language 'sql'; Or, would I still have to add FOR UPDATE to that final SELECT? Forest
В списке pgsql-hackers по дате отправления: