use pgsql in a big project, but i found pg has some big problem on concurrency write operation, maybe a joke for myself !

От: wyx6fox@sina.com
Тема: use pgsql in a big project, but i found pg has some big problem on concurrency write operation, maybe a joke for myself !
Дата: ,
Msg-id: 20100202043959.7C1354B1C26@mail175-236.sinamail.sina.com.cn
(см: обсуждение, исходный текст)
Ответы: Re: use pgsql in a big project, but i found pg has some big problem on concurrency write operation, maybe a joke for myself !  (Greg Stark)
Список: pgsql-performance

hi, first, thanks u for make so good opensource db .

 

recently maybe half an years ago ,i begin to use pg in a big project for insurance project, belong as the project go on ,and

i found some performance problem on concurrency write situation , then i do a research on concurrency write strategy on postgresql ,

 

i found a joke ,maybe this joke concurrency strategy is the designer's pround idea, but i think it is a joke , next let me describe the problems:

 

* joke 1:  insert operation would use a excluse lock on reference row by the foreign key .  a big big big performance killer , i think this is a stupid design .

 

* joke 2: concurrency update on same row would lead to that other transaction must wait the earlier transaction complete  , this would kill the concurrency performance in some long time transaction situation .  a stupid design to ,

 

  this joke design's reason is avoid  confliction on read committed isolation , such as this situation:

UPDATE webpages SET hits = hits + 1 WHERE url ='some url ';

 when concurrency write transaction on read committed isolation , the hits may result wrong .

 

 this joke design would do seriable write , but i think any stupid developer would not write this code like this stupid sample code ,  a good code is

use a exclusive lock to do a seriable write on this same row , but the joker think he should help us to do this , i say ,u should no kill concurrency performance and help i do this fucking stupid sample code ,  i would use a  select .. for update to do this :

 

select 1 from lock_table where lockId='lock1' for update ;

UPDATE webpages SET hits = hits + 1 WHERE url ='some url ';

 

 

 

 

 

* joke 3: update 100000 rows on a table no any index , it cost 5-8 seconds , this is not acceptable in some bulk update situation . 

 

at last, sorry about my angry taste.

 

 

 


В списке pgsql-performance по дате сообщения:

От: Yeb Havinga
Дата:
Сообщение: Re: Slow query: table iteration (8.3)
От: Scott Marlowe
Дата:
Сообщение: Re: the jokes for pg concurrency write performance