Re: Avoiding any locks in SQL Servers - read and understand....its magic.

Christopher Browne <cbbrowne_at_acm.org> writes:

>>>If we have two instances (say T1 and T2) of the following transaction
>>>running concurrently:
>>>
>>>BEGIN;
>>>if SELECT COUNT(*) FROM table1 is < 10 then
>>> INSERT INTO table1 VALUES (...);
>>>COMMIT;

>And note that this scenario involves not one, but TWO misreadings of
>SQL.
>
>1. It is treating aggregates as if they were attributes. They are
>not.
>
>2. It assumes that there is a serialization problem.

It takes the normal definition of 'serializable': an order of execution is serializable if it is equivalent to some ordering where the transactions run one after the other. In this case, the two possible orderings are T1 then T2, and T2 then T1. But the ordering in the example matches neither of those, so it is not serializable.

>The proper interpretation of the transactions, with time thrown in as
>a variable, is more or less the following:
>
>T1:
> If the number of entries in table 1 when Transaction T1 starts is <
> 10 then insert into table1 values (...);
>
>T2:
> If the number of entries in table 1 when Transaction T2 starts is <
> 10 then insert into table1 values (...);

Indeed, but if you chose some serial ordering - either T1,T2 or T2,T1 - then the table will not get more than 10 elements. What happens (in the example) is not equivalent to any serial ordering.

>If T1 and T2 each start before the other one finishes, with 9 values
>in the table, then there will indeed be 2 entries inserted, and there
>is nothing mis-serialized about it.

Well it's not mis-serialized; it just isn't serialized at all, by the definition above.

>COUNT(*) is not an attribute.

But normally when talking about transaction safety we don't qualify it by saying that it applies only to attributes and not aggregate functions. Perhaps we should do that. Explanations of what ACID means do not normally hedge it by explaining that certain types of query don't have to be atomic.

>In the absence of a script that actually demonstrates the behaviour
>that was discussed, it seems appropriate to remain more than a mite
>skeptical.

Indeed, we are talking about a castle in the air at the moment. But you seem to be saying that even if the behaviour were as described - with the two transactions not isolated from each other, and not running in any order that corresponds to a serial order - then it would still count as serializable. Have I misunderstood?

-- 
Ed Avis <ed_at_membled.com>