title

text

Kevin  Grittner
Kevin Grittner EnterpriseDB
15:00 05 February

Everything about transaction isolation in PostgreSQL for application developer

Whenever multiple users, processes, or threads are concurrently modifying data which is shared among them, problems can occur if race conditions are not handled somehow. These problems are particularly acute in a database which provide ACID semantics. A set of changes grouped into a database transaction must appear atomically, both to concurrent transactions and in terms of crash recovery. Each transaction must move the database from one consistent state (with regard to business rules) to another. For programming efficiency, each transaction must be able to be coded independently of what other transactions may happen to be running at the same time. In the event of a crash, all modifications made by transactions for which the application was notified of successful completion, and all modifications which had become visible to other transactions, must still be completed upon crash recovery. Over the years, various strategies have been employed to provide these guarantees, and sometimes the guarantees have been compromised in one way or another. This talk will cover the approaches taken to provide these guarantees or compromised variations of them, with an emphasis on the Serializable Snapshot Isolation (SSI) technique available in PostgreSQL (and so far not in any other production product). While SSI already performs faster and with higher concurrency than any other technique for managing race conditions with most common workloads, there are many opportunities for further enhancing performance, some of which would require the assistance of people expert in the various index access methods; these issues will be discussed. The talk will also present some rough ideas about how SSI techniques might be used with XTM in a distributed system.

Time will be reserved at the end of the talk for group discussion of optimizations and possible application in distributed environments.

Слайды

6_Кевин Гриттнер kevin.ppt

Видео

Другие доклады

  • Алексей Игнатов
    Алексей Игнатов Postgres Professional
    90 мин
  • Jean-Paul Argudo
    Jean-Paul Argudo Dalibo

    Migration to PostgreSQL : reasons... and consequences

    The talk will be articulated around all the traditional arguments to "how chose PostgreSQL over other choices in the database domain"... But also, and that's quite new in the comunity, what are the consequences of this choice. Because the PostgreSQL adoption brings adoption of other things like Linux, but also, Open Source thinking, the fast pace of PostgreSQL will command new methods of validation the company must adapt to... etc.

  • Boris Veryugin
    Boris Veryugin ООО "Диасофт Платформа"
    45 мин

    Automated migration of applications from proprietary DBMS to PostgreSQL

    Diasoft Platform company's technology solutions for migration of applications from proprietary DBMS (on the examples of Oracle and Microsoft SQL Server) to PostgreSQL will be presented in the talk. These solutions are implemented in Diasoft Database Adapter software.

    Our technology solutions allow to automate: 1) migration of database schema (including translation of stored procedure and function code); 2) migration of data; 3) migration of client applications without any change in their source code.

  • Ronan Dunklau
    Ronan Dunklau Dalibo
    45 мин

    Multicorn: writing FDWs in Python

    Multicorn is a generic Foreign Data Wrapper which goal is to simplify development of FDWs by writing them in Python.

    We will see:

    • what is an FDW what Multicorn is trying to solve how to use it, with a brief tour of the FDWs shipping with Multicorn.
    • how to write your own FDW in python, including the new 9.5 IMPORT FOREIGN SCHEMA api.
    • the internals: what Multicorn is doing for you behind the scenes, and what it doesn't

    After a presentation of FDWs in general, and what the Multicorn extension really is, we will take a look at some of the FDWs bundled with Multicorn.

    Then, a complete tour of the Multicorn API will teach you how to write a FDW in python, including the following features:

    • using the table definition
    • WHERE clauses push-down
    • output columns restrictions
    • influencing the planner
    • writing to a foreign table
    • IMPORT FOREIGN SCHEMA
    • ORDER BY clauses pushdown
    • transaction management

    This will be a hands-on explanation, with code snippets allowing you to build your own FDW in python from scratch.