Re: 'locking' the SELECTs based on indices... - Mailing list pgsql-sql

On Thu, 2006-02-23 at 13:56 +0200, Achilleus Mantzios wrote:

> The intersection of  rows that satisfy BOTH 
> "code_id = 1 AND code_value = 'abla'"
> and
> "code_id = 1 AND code_value = 'eble'"
> is ZERO!!!
> 
> Why would you want irrelevant rows to wait for one another??

It was a bit silly representation of what I actually want :) I'll
explain it later, down there.

> > First connection:
> > BEGIN;
> > SELECT * FROM bla WHERE code_id = 1 FOR UPDATE -- this is just for lock
> > SELECT * FROM bla WHERE code_id = 1 AND code_value = 'abla' FOR UPDATE
> > ...
> > 
> > Second connection:
> > BEGIN;
> > SELECT * FROM bla WHERE code_id = 1 FOR UPDATE -- here it'll wait
> > SELECT * FROM bla WHERE code_id = 1 AND code_value = 'eble' FOR UPDATE
> > 
> > Since I'm actually doing this inside of a function, i'll use PERFORM for
> > the first select. The problem is that SELECT COUNT(*) FROM bla WHERE
> > code_id = 1 will return some 10M rows (on live data, my test data has
> > some 100k rows, and I don't see any performance impact - yet).
> > 
> > Is this a right way to go? 
> >
> 
> I'll repeat the first question...
> 
> Lets say you have 100 rows with id=1, and from them 10 have 
> code_value = 'eble' and 20 have code_value = 'able',
> 
> so you got 10 rows with id=1 and code_value = 'eble'
> and 20 rows with id=1 and code_value = 'able'
> 
> So in the first case you deal with 10 rows, in the second with 20 rows.
> Why take into account the rest 90 and 80 rows respectively???
> 
> If for some reason you want INDEED the 
> "SELECT * FROM bla WHERE code_id = 1 FOR UPDATE" effect,
> and the cardinalities are as you describe (more or less),
> then go with the LOCK TABLE solution as Tom said.

I have a actuall table, called code_values. There I have stored codes
for the various prize-winning games. The table looks like this:

CREATE TABLE code_values (code_id int4,service_id int4,code_value int4
)

code_id is, of course, PK for the table.

Now, when a 'player' sends an SMS, or makes a phone call, I need to
check if that code is valid, and then, if that code has allready been
played. If it's invalid, or if it has been played, the user is
discarded. Since the number of prize-winning games (or services) is
around 50, and each service has around 1M codes, I didn't want to lock
entire table, so ALL the services wait, but just the portion of the
table that contains data for the desired service. So, when I check for
the code:

SELECT code_value FROM code_values WHERE service_id = 1 AND code_value =
'KFH1A' FOR UPDATE

I lock just that particular row, which is no good. I need to have all
the codes for the service 1 locked, so if it happens that two users send
the very same code, one has to fail. Therefore, from within plpgsql I
first do:

PERFORM * FROM code_values WHERE service_id = 1 FOR UPDATE

and then, later in code, i check for the actuall code.

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.

I could go with the LOCK TABLE, but that seems to expensive. Or not?
Mario

> 
> P.S. 
> Ti si Hrvatski???

:) Yes! :) Ja sam Hrvat, govorim hrvatski :)

> Dobro Jutro druga!!!

Dobro jutro i tebi! :)