X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id BA4269DC85B for ; Wed, 25 Jan 2006 12:46:16 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 17035-09 for ; Wed, 25 Jan 2006 12:46:15 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.192]) by postgresql.org (Postfix) with ESMTP id 5FF369DC837 for ; Wed, 25 Jan 2006 12:46:14 -0400 (AST) Received: by wproxy.gmail.com with SMTP id 57so134408wri for ; Wed, 25 Jan 2006 08:46:13 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=DWD1VbOLTe0toDaMc/3yh7oyYSMgWVzCuVoCUqUegfeC3NbxzGIrSyWyiQphSkKo+85rvcz0UvqnrYYvNieRMbVgWTGRvb3vBGJXoAZ2nHkwuE2VHsbmOejCU55GglCXbaLOcXZS9xgJX+qR5dtYzISvaRojso1j1ZrQdyVUiek= Received: by 10.54.80.5 with SMTP id d5mr718057wrb; Wed, 25 Jan 2006 08:46:12 -0800 (PST) Received: by 10.54.93.8 with HTTP; Wed, 25 Jan 2006 08:46:12 -0800 (PST) Message-ID: Date: Wed, 25 Jan 2006 11:46:12 -0500 From: Jaime Casanova To: mario.splivalo@mobart.hr Subject: Re: Changing the transaction isolation level within the stored procedure? Cc: pgsql-sql@postgresql.org In-Reply-To: <1138198348.8828.32.camel@localhost.localdomain> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <1138198348.8828.32.camel@localhost.localdomain> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.092 required=5 tests=[AWL=0.092] X-Spam-Score: 0.092 X-Spam-Level: X-Archive-Number: 200601/166 X-Sequence-Number: 23955 > 1. java got the message via http (whatever) > 2. java does: begin; > 3. java does: select * from create_message(...) > 4. java does some checking > 5. java does: select * from set_message_status(...) > 6. java does some more checing > 7. java does commit; (under rare circumstances java does rollback). > > > Another thread (thread B) does this: > > 1. java got the update_status_request via http (whatever) > 2. java does: begin; > 3. java does: select * from set_message_status(...) > 4. java does: commit; > > As I've said, I'm using 'read commited', the default isolation level. > > Now, sometimes it happens that steps 4 or 6 take more time, and thread B > steps are executed before steps in thread A have finished. So I would > like the UPDATE in set_message_status to 'hold', until the transaction > that previoulsy called the set_message_status have commited or rolled > back. > > Is there a way to do so withing the postgres, or I need to do 'SET > TRANSACTION ISOLATION LEVEL TO SERIALIZABLE' before step 2 in both > thread A and thread B? > you need to set the transaction level after the begin and before every other statement... after the begin you have a select that invoke your function so that set is not the first statement... -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;)