Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save kiennt/eef20c08ce9541014fcf0208bbe52292 to your computer and use it in GitHub Desktop.
Save kiennt/eef20c08ce9541014fcf0208bbe52292 to your computer and use it in GitHub Desktop.
_Concurrent n Distributed systems reading lists

Papers

Time clock and the ordering of events in a distributed system

Consensus on Transaction Commit

An empirical study on the correctness of formally verified distributed systems

Byzantizing Paxos by Refinement

Fast Byzantine Paxos

Impossibility of Distributed Consensus with One Faulty Process

an asynchronous consensus algorithm cannot be guaranteed to be both safe and live. This is called the FLP Impossibility Result

Blockchains from a Distributed Computing Perspective ht

Seeing is Believing: A Client-Centric Specification of Database Isolation

CASPaxos: Replicated State Machines without logs

On Designing and Deploying Internet-Scale Services

link collection

Composition: A Way to Make Proofs Harder mentioned here

Posts

Strong consistency models

Because serializability allows arbitrary reordering of operations (so long as the order appears atomic), it is not particularly useful in real applications. Most databases which claim to provide serializability actually provide strong serializability, which has the same time bounds as linearizability. To complicate matters further, what most SQL databases term the SERIALIZABLE consistency level actually means something weaker, like repeatable read, cursor stability, or snapshot isolation.

Consistency means linearizability, and in particular, a linearizable register.

Serializability, linearizability, and locality

Difference between Linearizability and Serializability

Linearizability and Serializability in context of Software Transactional Memory

Linearizability versus Serializability

Serializability is the traditional “I,” or isolation, in ACID. If users’ transactions each preserve application correctness (“C,” or consistency, in ACID), a serializable execution also preserves correctness. Therefore, serializability is a mechanism for guaranteeing database correctness.1

Granted, serializability is (more or less) the most general means of maintaining database correctness. In what’s becoming one of my favorite “underground” (i.e., relatively poorly-cited) references, H.T. Kung and Christos Papadimitriou dropped a paper in SIGMOD 1979 on “An Optimality Theory of Concurrency Control for Databases.” In it, they essentially show that, if all you have are transactions’ syntactic modifications to database state (e.g., read and write) and no information about application logic, serializability is, in some sense, “optimal”: in effect, a schedule that is not serializable might modify the database state in a way that produces inconsistency for some (arbitrary) notion of correctness that is not known to the database.

Combining serializability and linearizability yields strict serializability: transaction behavior is equivalent to some serial execution, and the serial order corresponds to real time. For example, say I begin and commit transaction T1, which writes to item x, and you later begin and commit transaction T2, which reads from x. A database providing strict serializability for these transactions will place T1 before T2 in the serial ordering, and T2 will read T1’s write. A database providing serializability (but not strict serializability) could order T2 before T1.2

As Herlihy and Wing note, “linearizability can be viewed as a special case of strict serializability where transactions are restricted to consist of a single operation applied to a single object.”

Linearizability, serializability, transaction isolation and consistency models

Linearizability

Serializability

Please stop calling databases CP or AP

Moreover, databases with snapshot isolation/MVCC are intentionally non-linearizable, because enforcing linearizability would reduce the level of concurrency that the database can offer. For example, PostgreSQL’s SSI provides serializability but not linearizability, and Oracle provides neither. Just because a database is branded “ACID” doesn’t mean it meets the CAP theorem’s definition of consistency. Is this quote really correct?

Seriablizable but not linearizable

Distributed transactions

How does two phase commit recover from a participant's failure?

How ACID is the two-phase commit protocol?

Jepsen

Notes on distributed systems for youngbloods some additional commentary

Testing Distributed Systems for Linearizability

Scalability Cheatsheet: The Road to Paxos

The Limits of the CAP Theorem HN

The only time that a CAP-Available system would be available when a CAP-Consistent one would not is when one of the datacenters can’t talk to the other replicas, but can talk to clients, and the load balancer keeps sending it traffic.

the 'A' in CAP is boring. It does not mean what you think it means. Lynch et al. probably chose the definition because it's one for which the 'theorem' is both true and easy to prove. This is not the impossibility result with which designers of distributed systems should be most concerned.

The CAP Theorem - Foundation DB

Clarifications On The CAP Theorem And Data-Related Errors

Heisenbugs and Bohrbugs

paxos vs. 2PC

Paxos actually solves a more genreal problem than 3PC.

Consensus on Transaction Commit

Delivering Billions of Messages Exactly Once HN

I don't want to ever see the phrase "Exactly Once" without several asterisks behind it. It might be exactly once from an "overall" point of view, but the client effectively needs infinitely durable infinite memory to perform the "distributed transaction" of acting on the message and responding to the server.

how do you cut a monolith in half? HN

Exactly-once Semantics are Possible: Here’s How Kafka Does it reddit nope

Providing API for building applications that have transactions and help with idempotent producing is really exciting. This will help lower a lot of pains associated with building stream processing systems. Doing it with very little performance overhead is amazing.

Indeed. Idempotent operations is the mainstay of managing "at least once" message delivery. If you had a true "exactly once" system, idempotency would be unnecessary.

Manage Kubernetes Clusters on AWS Using Kops

Docker & AWS

The problem with renting boxes is the hidden costs if you want to do it right. First of all, if you have anything mission critical, you need to run it in a high availability config, this is easy for stateless microservices, but when it comes to running your DB, you start renting three boxes instead of one or two and configuring them accordingly. And then you setup your Backup Infrastructure for disaster recovery, Glacier needs a replacement after all. No problem, just more disks(?) on a few more boxes(?) and bacula(?), better in a different Datacenter just to be on the safe side, it would be nasty if you whole rack gets fried and your data with it. Don't forget to backup your configuration, all of it. Loadbalancers, Server Environment Variables, Network (do you have an internal DNS?), Crontabs, some businesses need their audit logs stored etc... On the infrastructure level there is lots and lots of stuff you can do and you won't ever really need AWS, you'll just spend significantly more time finding and administering the right solutions than just using the AWS Solutions where you'll find a treasure trove of great tutorials and can relatively cheaply pay for support. If you then pay someone on top for 24/7 management/monitoring of your dedicated stack so that your team doesn't have to get up at 3 am because one of your VMs disk fills because some stray logfile is filling the disc, many of the savings you had by setting it up on a dedicated server go out of the window because the management partner needs to train their people to look into your infrastructure. AWS only Management Partners are just light-years cheaper because they can streamline their processes much better. You could also hire your own team of admins... Sure AWS is a beast with its own surprises, but overall the cost/benefit ratio is still very fair even if you factor in all the "surprises"(many of which your management partner will probably know about). Having layered support is really something beneficial aswell. If something is wonky with RDS, you get to call your management partner if he didn't detect it before you, who if he can't tackle it himself can call AWS technicians. This gets you much much further than you would get elsewhere. The outside the world is paying for (for example) perconas consultants or someone similar if the problems grow over their team's head. Sure, at some point in a companies growth, depending on how technical the operation is, there might be a time where an admin team and colocation/dedicated boxes make sense, where AWS technicians will scratch their heads etc., especially if you have some very very specific tasks you need to do. But for most people this is far off if ever.

Serializability, linearizability, and locality

Recall that strict serializability is essentially serializability plus linearizability’s real-time constraint: transactions cannot be arbitrarily re-ordered, but must appear to take place atomically at some time between their invocation and completion. When we add real-time constraints to sequential consistency, we get linearizability: a local property. Why can’t we add real-time constraints to serializability and obtain locality? Why don’t real-time multi-object transactions compose?

We can view strict serializability as linearizability plus the multi-object transactions of serializability. But in another sense, linearizability is strict serializability with the constraint that transactions are constrained to act on a single object, because that restriction provides locality.

Visualizing Linearizability

Linearizability: A Correctness Condition for Concurrent Objects (1990)

Sequential consistency is equivalent to linearizability without condition L2. Serializability is analogous to sequential consistency with transactions, and strict serializability is analogous to linearizability with transactions. Sequential consistency, serializability, and strict serializability do not have the same locality and non-blocking properties as linearizability. Moreover, serializability and linearizability are for different domains. Serializability works well for databases because application developers should be able to easily express complex transactions. Linearizability is better for infrastructure in which the developer is willing to spend considerable effort to maximize concurrency.

Consensus Protocols: Two-Phase Commit

Consensus Protocols: Three-Phase Commit

Consensus Protocols: Paxos

3PC works very well when nodes may crash and come to a halt – leaving the protocol permanently when they encounter a fault. This is called the fail-stop fault model, and certainly describes a number of failures that we see every day. However, especially in networked systems, this isn’t the only way in which nodes crash. They may instead, upon encountering a fault, crash and then recover from the fault, to begin executing happily from the point that they left off (remember that, with stable storage that persists between crashes, there’s no reason that a restarted node couldn’t simply pick up the protocol from where it crashed). This is the fail-recover fault model, which is more general than fail-stop, and therefore a bit more tricky to deal with.

Similarly, heavy network load can delay the delivery of a message for an arbitrary period. In a synchronous network model, there is a bound on the amount of time a remote host can take to process and respond to a message. In an asynchronous model, no such bound exists. The key problem that asynchronicity causes is that time outs can no longer be reliably used as a proxy for failure detection

There are other ways that Paxos can go wrong. Acceptors need to keep a record of the highest proposal they have agreed to, and the value of any proposals they have accepted, in stable storage. If that storage should crash then the acceptor cannot take part in the protocol for fear of corrupting the execution (by effectively lying about the proposals it has already seen). This is a kind of Byzantine failure, where an acceptor deviates from the protocol.

Rather than sacrifice correctness, which some variants (including the one I described) of 3PC do, Paxos sacrifices liveness, i.e. guaranteed termination, when the network is behaving asynchronously and terminates only when synchronicity returns.

In distributed systems, what is a simple explanation of the Paxos algorithm?

3PC is fail-stop resilient, but not fail-recover resilient. Unfortunately real life requires fail-recover and hence we need a more general solution. This is where Paxos comes in.

What happens if we mandate only one Leader at a time in Paxos, and also mandate that instead of majority, all nodes must vote? You are right – we get 2PC. 2PC is a specific case of Paxos.

Byzantine faults, Paxos, and consensus

Paxos is in a sense sort of like RSA. RSA is expensive, so you use it as a wrapper to a cheaper protocol. With Paxos you elect a leader to be in charge of a large-scale event. The coordinator is a single point of failure, so you can use paxos to come up with a new coordinator. Most of the time you don’t run paxos, you only run it when something bad happens to prevent any one thing to be a single point of failure.

Version vector

Why is memory reclamation so important? HN

You Can’t Sacrifice Partition Tolerance

Fear and Loathing in lock-free programming

Google's Cloud Spanner: how does it stack up?

Spanner was originally built by Google to handle workloads like AdWords and Google Play, that were, according to Google, previously running on massive, manually sharded MySQL implementations

3PC. 2PC, Paxos SO question

So Paxos is a better, more general version of 2PC, and unlike 3PC, is has been proved correct?

A Comparison of Advanced, Modern Cloud Databases

Concurrent ACID: Whether the database supports ACID (atomicity, consistency, isolation, and durability) guarantees across multiple operations. ACID is a powerful tool for system correctness, and until recently has been a long sought but illusive chimera for distributed databases. I use the term “concurrent ACID” because technically Cosmos guarantees ACID, but only within the confines of a single operation.

modern techniques can achieve CP while still keeping availability that’s incredibly good. Like five or more 9s of good. This result is so optimal that modern databases seem to be converging on it. Every database on the list above is CP with varying levels of A

Time-based consistency. Sophisticated distributed systems like Spanner and CockroachDB tend to need a little more time to coordinate and verify the accuracy of the results that will be returned from any given node, and this makes them less suitable for low latency operations.

Spanner, TrueTime and the CAP Theorem HN

Why you should pick strong consistency, whenever possible

Paxos derived

distributed systems reading list

A History of Transaction Histories

Data Laced with History: Causal Trees & Operational CRDTs

another collection of links

Standing on Distributed Shoulders of Giants

Tweets about distributed transactions

Slides

Distributed systems course

Distributed Systems, Failures, and Consensus

another one

CIS 505: Software Systems Lecture Note on Consensus

Courses

Principles of Distributed Computing (lecture collection)

Another course

Distributed Systems W4995-1 Fall 2014 Roxana Geambasu lecture 17

3PC trades safety for liveness, 2PC trades liveness for safety. Paxos is "largely live" and blocks only on exceptional circunstances

Books

Replication theory and practice

Videos

Our concurrent past, our distributed future

Distributed Systems vs Compositionality—Roland Kuhn

Consistency without consensus in production systems https://www.youtube.com/watch?v=lsKaNDj4TrE Martin Kleppmann - Conflict Resolution for Eventual Consistency GOTO 2015 • Coordination-Free Computations GOTO 2014 • How the Bitcoin Protocol Actually Works

Distributed Systems Theory for Practical Engineers

Applied Distributed Research in Apache Cassandra

Four Distributed Systems Architectural Patterns by Tim Berglund

What Came First: The Ordering of Events in Systems

Four Distributed Systems Architectural Patterns by Tim Berglund

[17:00] If you are a service, like Slack, which is composed of a series of organizations which are not as big...

Distributed Systems in One Lesson by Tim Berglund

Think Twice before Dropping ACID

Videos

Modern SQL algorithms

Transactions and Concurrency Control Patterns by Vlad Mihalcea

High-performance Hibernate

The Bits of Postgres You Haven't Found Yet

Transactions: myths, surprises and opportunities

distributed transactions lecture

Slides

Row Pattern Matching in SQL:2016

Pagination Done the Right Way

SQL Transactions - What they are good for and how they work

Linearizability

Papers

Serializable Snapshot Isolation in PostgreSQL

An Optimality Theory of Concurrency Control for Databases

Granted, serializability is (more or less) the most general means of maintaining database correctness. In what’s becoming one of my favorite “underground” (i.e., relatively poorly-cited) references, H.T. Kung and Christos Papadimitriou dropped a paper in SIGMOD 1979 on “An Optimality Theory of Concurrency Control for Databases.” In it, they essentially show that, if all you have are transactions’ syntactic modifications to database state (e.g., read and write) and no information about application logic, serializability is, in some sense, “optimal”: in effect, a schedule that is not serializable might modify the database state in a way that produces inconsistency for some (arbitrary) notion of correctness that is not known to the database.

Serializability of concurrent database updates

Serializable Isolation for Snapshot Databases

Highly Available Transactions: Virtues and Limitations

A Critique of ANSI SQL Isolation Levels

Why do computer systems stop?

Spanner: Becoming a SQL System hackernews

Amazon Aurora: Design Considerations for High Throughput Cloud-Native Relational Databases

saner event sourcing HN

I have worked on, or cleaned up, 4 different CQRS/ES projects. They have all failed. Each time the people leading the project and championing the architecture were smart, capable, technically adept folks, but they couldn't make it work. There's more than one flavor of this particular arch, but Event Sourcing in general is simply not very useful for most projects. I'm sure there are use cases where it shines, but I have a hard time thinking of any. Versioning events, projection, reporting, maintenance, administration, dealing with failures, debugging, etc etc are all more challenging than with a traditional approach.

Posts

Oracle serializable is not serializable

Quantifying isolation anomalies

Distributed Consistency and Session Anomalies

In the database systems community, the gold standard is serializability. [...] In the distributed systems community, the gold standard is linearizability.

Both serializability and linearizability require (potentially expensive) coordination. Therefore just as we consider weaker isolation levels than serializable, we can also consider weaker consistency levels than linearizable. At weaker consistency levels non-linearizable phenomena (anomalies) can be observed.

Serialisability is not stronger than SI or causal consistency. They are mutually incomparable. Serialisability allows the folowing anomaly w.r.t. Causality: user performs transaction T1 followed by T2; database executes them in the order T2 followed by T1. SI allows the write-skew anomaly, which SER disallows. The Strict Serialisability model is the only one stronger than both SI and CC (and Serialisability for that matter).

The many faces of consistency

A Beginner’s Guide to the True Order of SQL Operations

A beginner’s guide to database locking and the lost update phenomena

Quantified Comparison Predicates – Some of SQL’s Rarest Species

NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: MySQL

A True SQL Gem You Didn’t Know Yet: The EVERY() Aggregate Function

serializable (for Oracle)

A beginner’s guide to read and write skew phenomena

A beginner’s guide to the Phantom Read anomaly, and how it differs between 2PL and MVCC

group commit

info on correlated subqueries correlated correlated vs non-correlated in from

semijoins Semi Join and Anti Join Should Have Their Own Syntax in SQL

Non-semi-join Subquery Optimizations in MariaDB mysql Optimizing Subqueries with the EXISTS Strategy

What is semi-join in database?

lateral join

How to Calculate Multiple Aggregate Functions in a Single Query

The Sakila Database

"The key, all the key, and nothing but the key"

UPSERT MERGE

SQL Select only rows with Max Value on a Column greatest n per group

Mode Analytics on SQL Window Functions

transaction-log at DBA stack exchange

PostgreSQL anti-patterns: read-modify-write cycles HN discussion more on race conditions more more more more more on DBA stack exchange Do database transactions prevent race conditions?

select .. for update more more

Clustered and Nonclustered Indexes Described

PostgreSQL doesn't have the concept of clustered indexes at all. Instead, all tables are heap tables and all indexes are non-clustered indexes.

Slow Postgres updates if OTHER columns are indexed? Efficient Use of PostgreSQL Indexes

When this happens, a sequential scan is actually most likely much faster than an index scan, so the query planner has in fact correctly judged that the cost of performing the query that way is lower.

Postgres will decide to perform a sequential scan on any query that will hit a significant portion of a table. If you do have an index on that column, it will be a dead index that’s never used - and indexes are not free: they come at a cost in terms of storage and maintenance.

Batch Writing, and Dynamic vs Parametrized SQL, how well does your database perform?

Why Use Postgres (Updated for Last 5 Years)

Upsert was a work in progress for several years. It was one of those features that most people hacked around with CTEs, but that could create race conditions.

you can create constraints to ensure only unique ranges in a table

Use row constructors to compare several values at once

Do you need a database transaction for reading data?

rank in mysql

Understanding Weak Isolation Is a Serious Problem

SQL/MED Hacker News

UUID or GUID as Primary Keys? Be Careful! hacker news

Testing PostgreSQL for Fun

Postgres Transactions Aren’t Fully Isolated (clickbaity)

Is my implementation of type/subtype design pattern (for mutually exclusive subclasses) correct?

Inventory management in MongoDB: A design philosophy I find baffling HN

How do you update your production codebase/database schema without causing downtime?

How to handle unexpected schema changes to production database

Zero Downtime Deployment with a Database

Customizing My Postgres Shell

Practical Guide to SQL Transaction Isolation

Falsehoods programmers believe about geography

Using ENUM vs other types

JOIN Elimination: An Essential Optimiser Feature for Advanced SQL Usage

The best way to soft delete with Hibernate

Deferrable Constraints in SQL Server

How to update only a subset of entity attributes using JPA and Hibernate

Hibernate: save, persist, update, merge, saveOrUpdate

Database Decay and What To Do About It

10 Cool SQL Optimisations That do not Depend on the Cost Model

Storing JSON in database vs. having a new column for each key

sql optimization & performance on SO.

Retrieving n rows per group

apply so

Note: Informix 12.10 xC2+ has Lateral Derived Tables and Postgresql (9.3+) has Lateral Subqueries which can be used to a similar effect.

lateral clauses in SQL

SQL;2003 allows a subquery in the from clause that is prefixed by lateral key word to access the attributes of preceding tables or sub queries in the from clause. Example SQL is given in the question. Without the lateral clause, the sub query cannot access the correlation variable I1 from the outer query. Currently only few SQL implementations such as IBM DB2 support the lateral clause.

Optimize GROUP BY query to retrieve latest record per user

There are things that a LATERAL join can do, but a (correlated) subquery cannot (easily). A correlated subquery can only return a single value, not multiple columns and not multiple rows

Correlated subquery

The effect of correlated subqueries can in some cases be obtained using joins.

Correlated subqueries may appear elsewhere besides the WHERE clause

Taking a look at CROSS APPLY

I think the easiest way to think of CROSS APPLY is that it is like doing a CROSS JOIN with a correlated sub-query instead of a derived table. Let's see if I can explain that .... A derived table is "self-contained", in that all tables and columns in the parent SELECT are not accessible (though variables and parameters can be referenced).

Subqueries

A subquery can represent a fixed value, a correlated value, or a list of values. You can use any number of subqueries. You can also nest subqueries inside other subqueries.

lateral lateral as alternative to group by more lateral even more lateral yet even more lateral and more on JOOQ

10 Common Mistakes Java Developers Make when Writing SQL

If you’re UPSERTING by chaining INSERT and UPDATE or by chaining SELECT .. FOR UPDATE and then INSERT or UPDATE, think again. Apart from risking race conditions, you might be able to express a simpler MERGE statement.

Managing concurrency when using SELECT-UPDATE pattern SQL Server 2012 potential race condition on select or insert PostgreSQL anti-patterns: read-modify-write cycles Handling race conditions in PostgreSQL

While SERIALIZABLE transaction isolation is a clean solution, it is also rather expensive.

A (Probably Incomplete) Comprehensive Guide to the Many Different Ways to JOIN Tables in SQL

This of course has nothing to do with relational algebra anymore, because it imposes a JOIN order (from left to right). But sometimes, that’s OK and sometimes, your table-valued function (or subquery) is so complex, that’s the only way you can actually use it.

Postgres LATERAL JOIN follow-up

other options: Use GROUP BY in a subquery to get the latest inspection date, then use that date to get the rest of the inspection info. / Use a window function. / Use DISTINCT ON.

Results for me take about 25ms. I’ve gotta say I love window functions, but it looks like in this case at least lateral join is a better choice.

What is the difference between LATERAL and a subquery in PostgreSQL? [https://stackoverflow.com/questions/1139160/when-should-i-use-cross-apply-over-inner-join]

For returning more than one column, a LATERAL join is typically simpler, cleaner and faster.

CROSS APPLY works better on things that have no simple JOIN condition.

When there is a LIMIT in the subquery.

You could probably do something like that using CTE's and window function [...] but this is less readable and probably less efficient.

See the end of Ariel's link. A row_number() query is just as nice and doesn't even require a join. So I don't think I should use cross apply for this situation (select top 3, partition by t1.id).

What's the Hi/Lo algorithm?

Managing database schemas without downtime HN

We're using FlywayDB [0] and many of the ideas in Martin Fowler's Evolutionary DB article [1]. When database changes are breaking, we use a pre-deploy and post-deploy "pair" to create an intermediate DB state that both application versions will run against.

Evolutionary Database Design

Podcasts

Database Choices and Uber with Markus Winand

Courses

Introduction to databases

  • Manual / Version 11.1.0

  • IBM DB2 doesn't have a separate JSON datatype. It stores BSON BLOBs and provides conversion & manipulation functions. docs.

  • Schemas

A schema is a collection of named objects; it provides a way to group those objects logically. A schema is also a name qualifier; it provides a way to use the same natural name for several objects, and to prevent ambiguous references to those objects.

IBM DB2 supports the ON DELETE and ON UPDATE clauses in referential integrity constraints. Oracle only supports the ON DELETE clause in referential integrity constraints.

In IBM DB2 identity columns provide a way to automatically generate unique, sequential and recoverable values for each row in a table. [...] When you define it with the GENERATED ALWAYS command, the database manager always generates the values used.

FROM FINAL TABLE

The aggregation group of a given row is a set of rows that is defined in relation to the given row (in the ordering of the rows in the partition of the given row). window-aggregation-group-clause specifies the aggregation group. If this clause is not specified and a window-order-clause is also not specified, the aggregation group consists of all rows of the window partition. The aggregation group of all rows of the window partition can be explicitly specified using the RANGE or ROWS clauses.

If window-order-clause is specified, but window-aggregation-group-clause is not specified, the window aggregation group consists of all rows that precede a given row of the partition of the given row or all rows that are peers of the given row in the window ordering of the window partition that is defined by the window-order-clause

db2 lateral

CROSS APPLY (similar to a row-based INNER JOIN) and OUTER APPLY (similar to a row-based LEFT JOIN) are not supported in DB2 for i, although sometimes the same thing can be accomplished. For example, a table function that is participating in a join in DB2 can have the input parameters vary (say, from a column in another table) from row to row, whereas SQL Server table functions can't be used in this manner without using one of the APPLY operators. So in the case of table functions, the APPLY operators aren't even required in DB2.

Additionally, LATERAL correlation can be used to mimic the behavior of CROSS APPLY. However, I'm not aware of any way to use LATERAL to mimic OUTER APPLY.

db2 pdf docs for Windows and Linux SQL reference

How to Limit Query Results for DB2 Databases

Opensource databases such as MySQL and PostgreSQL supports non standard SELECT extension for limiting number of returned rows from query. You can see description in PostgreSQL manual. Look for keywords LIMIT and OFFSET. LIMIT specifies the maximum number of rows to return, while OFFSET specifies the number of rows to skip before starting to return rows. When both are specified, OFFSET rows are skipped before starting to count the LIMIT rows to be returned.

Returning TOP N rows (emulating LIMIT) from query is easy. This functionality is in every database but syntax is database specific. DB2 follows SQL2008 standard and syntax for TOP N rows in DB2 is SELECT * FROM T FETCH FIRST 10 ROWS ONLY.

Starting with DB2 9.7.2, LIMIT and OFFSET are supported in DB2

Seven Surprising Findings About DB2

Imagine you've already fetched a bunch of rows and need to get the next few ones. For that you'd use the time_stamp value of the last entry you've got for the bind value (?). The query then just return the rows from there on. But what if there are two rows with the very same time_stamp value? Then you need a tiebreaker: a second column—preferably a unique column—in the order by and where clauses that unambiguously marks the place till where you have the result.

For all forms of SUBSTRING(), the position of the first character in the string from which the substring is to be extracted is reckoned as 1.

example custom function

DELIMITER ;;

CREATE FUNCTION hello (s CHAR(20))
RETURNS CHAR(50) DETERMINISTIC
BEGIN
    RETURN CONCAT('Hello, ',s,'!');
END;;

DELIMITER ;

Applications using this level must be prepared to retry transactions due to serialization failures.

The Serializable isolation level provides the strictest transaction isolation. This level emulates serial transaction execution, as if transactions had been executed one after another, serially, rather than concurrently. However, like the Repeatable Read level, applications using this level must be prepared to retry transactions due to serialization failures. In fact, this isolation level works exactly the same as Repeatable Read except that it monitors for conditions which could make execution of a concurrent set of serializable transactions behave in a manner inconsistent with all possible serial (one at a time) executions of those transactions. This monitoring does not introduce any blocking beyond that present in repeatable read, but there is some overhead to the monitoring, and detection of the conditions which could cause a serialization anomaly will trigger a serialization failure.

Declare transactions as READ ONLY when possible.

A-ha! It seems that SERIALIZABLE has race conditions into account.

PostgreSQL anti-patterns: read-modify-write cycles

PostgreSQL anti-patterns: Unnecessary json/hstore dynamic columns

Solving Schema Bloat with Postgresql's JSONB counterpoints

Postgres Indexes Under the Hood

The internals of Postgres

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment