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 2E8689DCA20 for ; Thu, 23 Feb 2006 16:10:07 -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 62297-04 for ; Thu, 23 Feb 2006 16:10:07 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from ls405.htnet.hr (ls405.htnet.hr [195.29.150.97]) by postgresql.org (Postfix) with ESMTP id BD7909DC99F for ; Thu, 23 Feb 2006 16:10:00 -0400 (AST) Received: from ls422.t-com.hr (ls422.t-com.hr [195.29.150.237]) by ls405.htnet.hr (0.0.0/8.12.10) with ESMTP id k1NK9rdK028855; Thu, 23 Feb 2006 21:09:53 +0100 Received: from ls422.t-com.hr (localhost.localdomain [127.0.0.1]) by ls422.t-com.hr (Qmlai) with ESMTP id 6524E988042; Thu, 23 Feb 2006 21:09:53 +0100 (CET) X-Envelope-Sender: mario.splivalo@mobart.hr X-Envelope-Sender: mario.splivalo@mobart.hr X-Envelope-Sender: mario.splivalo@mobart.hr Received: from ls422.t-com.hr (localhost.localdomain [127.0.0.1]) by ls422.t-com.hr (Qmlai) with ESMTP id 6063398803F; Thu, 23 Feb 2006 21:09:53 +0100 (CET) Received: from 83-131-49-92.adsl.net.t-com.hr (83-131-49-92.adsl.net.t-com.hr [83.131.49.92]) by ls422.t-com.hr (Qmlai) with ESMTP id 103178B8065; Thu, 23 Feb 2006 21:09:48 +0100 (CET) Subject: Re: 'locking' the SELECTs based on indices... From: Mario Splivalo Reply-To: mario.splivalo@mobart.hr To: PFC Cc: Achilleus Mantzios , pgsql-sql@postgresql.org In-Reply-To: References: <1140692542.5002.26.camel@localhost.localdomain> Content-Type: text/plain Organization: Mob-Art Date: Thu, 23 Feb 2006 21:07:58 +0100 Message-Id: <1140725278.5002.70.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.4.1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.143 required=5 tests=[AWL=0.143] X-Spam-Score: 0.143 X-Spam-Level: X-Archive-Number: 200602/284 X-Sequence-Number: 24294 On Thu, 2006-02-23 at 17:35 +0100, PFC wrote: > > > Now, If some other user want's his prize, when checking his code, if he > > sends code for some other service then service 1, that's ok. If he's > > sends code for the service 1 the PERFORM will wait untill I'm finished > > with previous user. > > Sorry for the double post. > > If the rows in your table represent associations between codes and > services that are one-use only, you could simply use UPDATE or DELETE, to > mark the row in question as having been "consumed". > Then, you check how many rows were deleted or updated. If it's 1, good. > If it's 0, the code has been used already. > > If the code itself is one-use only, you should have a codes table and a > codes_to_services table, with an ON DELETE CASCADE so that, when you use a > code, you delete it from the codes table and it's "consumed" for all > services. Thank you for the advice, I'll consider it too. The original idea was with UPDATE, so I could mark codes wich are used, but the table with codes will have 10M rows in the begining, for just one game. Later we'll have more games, with like 100M rows in the table. UPDATEing such table, when the server is under normal load (server also hosts some other games) is between 15 and 40 seconds. That is why I tried to eliminate UPDATEs and go with two tables, and INSERTS into the second table, for used codes. Mike