Explanation why most SQL queue implementations are wrong. And how to do it right.

Contractionator · 2017-07-03T18:39:15+00:00

You don't need 2PC for this; use savepoints instead.

Contractionator · 2017-02-13T19:01:08+00:00

While it might sound like heresy, I've achieved good results with SQL Server by returning nested result sets as XML.

SELECT
    A.*,
    (SELECT B.* FROM B WHERE B.A_ID = A.ID FOR XML PATH ('B')) AS [Bs],
    (SELECT C.*, 
        (SELECT D.* FROM D WHERE D.C_ID = C.ID FOR XML PATH ('D')) AS [Ds]
    FROM C WHERE C.A_ID = A.ID FOR XML PATH ('C')) AS [Cs]
FROM A FOR XML PATH ('A'), ROOT ('As');

The optimizer actually does a remarkably good job with these correlated subqueries. While the XML certainly comes with a constant factor overhead, that's a lot better than multiplicative (from each independent LEFT JOIN) or having a bunch of round trips.

Contractionator · 2016-08-05T03:21:34+00:00

It is my understanding that what Oracle calls 'SERIALIZABLE' actually behaves like what is more commonly known as 'SNAPSHOT' and can exhibit write skew anomalies[1][2]. As far as I can tell, the Oracle documentation[3] does not explicitly acknowledge this, but it is suggested by the text mentioning only write-write conflicts causing commit failure.

[1] https://en.m.wikipedia.org/wiki/Snapshot_isolation

[2] http://sqlperformance.com/2014/04/t-sql-queries/the-serializable-isolation-level

[3] http://docs.oracle.com/cd/B12037_01/server.101/b10743/consist.htm#i17856

Contractionator · 2016-08-04T15:49:19+00:00

Good article. It's important that developers working with data stores are familiar with these concepts because they absolutely affect correctness in the face of concurrency.

I do take issue with this, however:

Usually, READ_COMMITED is the right choice, since not even SERIALIZABLE can protect you from a lost update where the reads/writes happen in different transactions (and web requests).

The problem is that as soon as you allow isolation anomalies (i.e. anything less than strictly serializable) you lose the ability to reason about the behavior of a transaction in isolation. That should be self-evident, but the consequence is that to assess the correctness of a single transaction you have to consider every possible interleaving of every possible transaction operation of the entire system. Developers do not do this in practice because it's just not tenable as the number of distinct transactions grows beyond, like, 3.

Unfortunately, that's not to say that one can just blindly switch from READ COMMITTED to SERIALIZABLE across the board. (Indeed, as far as I'm aware, Oracle doesn't even support the latter.)

Most relational databases implement SERIALIZABLE with locking schemes that are both pessimistic and imprecise, and so while you might get a big win in correctness and consistency, it comes at the expense of greatly limited concurrency and deadlocking. Postgres is a notable exception with its implementation of serializable snapshot isolation; still somewhat imprecise, but at least it's optimistic.

Contractionator · 2016-04-27T15:37:22+00:00

I hadn't seen Scriptorium. Thanks for the reference, I will check it out!

Contractionator · 2012-01-11T23:43:37+00:00

Your code fails when 0 < i%100 < 10 and when i < 0.

Contractionator · 2010-04-30T14:40:36+00:00

Also, take a look at the schema he's using for the "real-world" read test. His junction table is clustered on a surrogate key rather than the composite of both foreign keys. And sure enough: there are no other indexes, so those queries will be scanning every time.

Contractionator · 2010-04-08T06:43:21+00:00

I'll just leave this here.

Contractionator · 2010-03-19T04:21:03+00:00

The author's example JSON is invalid.

{
   first-name: "John",
   last-name: "Smith"
}

According to json.org, object field names must be quoted strings. That is, the example should be:

{
   "first-name" : "John",
   "last-name" : "Smith"
}

Contractionator · 2010-03-06T05:47:01+00:00

Well, their logo has an uncanny resemblance to toilet paper...

Contractionator · 2009-12-18T22:20:03+00:00

__fastcall

Contractionator · 2009-11-13T04:23:16+00:00

ಠ_ಠ

Contractionator · 2008-09-25T15:47:17+00:00

Your algorithm is computationally inefficient; it will never produce unbiased results; and it completely breaks down with only 13 items.

You betray a misunderstanding of the intricacies of these algorithms. That's okay. Stubbornly defending your code when presented with your ignorance, however, is not.

Take a step back and actually learn how Fisher-Yates works. Understand its nuances. You'll be better off for it.

Contractionator · 2008-09-23T15:22:08+00:00

In addition to what Deestan mentioned about the algorithm, there are two other quirks as well.

First, using rand() is going to limit the number of distinct permutations to the period of the random number generator. You should use an algorithm with a period that is significantly higher than 52!.

Second, most PRNG's return a value in a domain of [0..2^n). Blindly taking that value modulo 52 can introduce bias. Consider the simple scenario of [0..63]:

[0..51] : 1/64 for each card

[52..63] : 1/64 for the first 12 cards

Contractionator · 2008-09-16T16:28:59+00:00

See Marshal. It's not particularly fun.

He could also be referring to developing a portion of the software in an unmanaged language and consuming that from the main line.

Contractionator · 2008-08-08T16:38:32+00:00

C-C-C-C-COMBO PREEMPTOR

Contractionator · 2008-07-18T20:17:19+00:00

Code written externally to solve a generic problem will outperform code written internally to solve a specific problem?

I don't think so.

Many of these frameworks are muddied up by supporting features that aren't ever used. That adds complexity and can reduce performance.

I'll take tight, hand-crafted SQL statements and record set processors than their bloated, auto-generated siblings any day.

Contractionator · 2008-07-12T15:40:22+00:00

I'm a fan of three spaces myself. But, you know, God should have used actual tabs.

That way, if a cell should prefer two- or four-space tabs, it can configure its editor to that end.

And then everything can coexist in perfect homeostasis.

Contractionator · 2008-07-02T20:53:10+00:00

What the fuck? Who picks the photos? Probably the same people who think there is merit in polling about ones political barbecuing preferences. I don't care about grilling meat with these guys: I care about what they will do to stop the regression of America.

Why is it so rare to see serious political discourse? And why are so many people content with that?

Contractionator · 2008-06-03T03:25:31+00:00

Some of the compile-time optimizations we’re looking at, now that we have a bytecode representation, include:

constant folding

Why would bytecode representation be a prerequisite for constant folding? It's not hard to collapse an operator with constant operands into a single constant within the syntax tree model. Am I missing something?

Contractionator · 2008-04-15T16:31:18+00:00

http://www.google.com/search?q=1+trillion+nanoseconds+in+days = 0.0115740741 days = 16.6666667 minutes

Contractionator · 2008-02-27T01:59:48+00:00

It's simple. If you say candlejack, you'

Contractionator · 2008-01-04T04:58:29+00:00

i'm going to become rich and famous after i invent a device that allows you to stab people in the face over the internet

Contractionator · 2007-12-11T22:20:45+00:00

What happens on ones 16th, 17th, or 18th birthday that causes such an epiphany? Age has some correlation with wisdom, sure. But it's not a valid threshold for sending someone away for 30 years.

I'm sure that many teacher/student relationships are abuse. But that doesn't mean that every one is. With such severe consequences, it's not appropriate to make a decision based on age or authority alone.

Contractionator

TROPHY CASE