What do you get with Print?

Instant access to your digital eBook copy whilst your Print order is Shipped

Paperback book shipped to your preferred address

Download this book in EPUB and PDF formats

Access this title in our online reader with advanced features

DRM FREE - Read whenever, wherever and however you want

Understanding Transactions and Locking

Locking is an important topic in any kind of database. It is not enough to understand just how it works to write proper or better applications; it is also essential from a performance point of view. Without properly handling locks, your applications might not only be slow, it might also be wrong and behave in an insane way. In my judgment, locking is key to performance and having a good overview will certainly help. Therefore, understanding locking and transaction is important for administrators and developers alike.

In this chapter, you will learn:

Basic locking
Transactions and transaction isolation
Deadlocks
Locking and foreign keys
Explicit and implicit locking
Advisory locks

At the end of the chapter, you will be able to understand and utilize PostgreSQL transactions in the most efficient way possible.

...

Key benefits

Your one-stop guide to mastering the advanced concepts in PostgreSQL with ease

Master query optimization, replication, and high availability with PostgreSQL

Extend the functionalities of PostgreSQL to suit your organizational needs with minimum effort

Description

PostgreSQL is an open source database used for handling large datasets (Big Data) and as a JSON document database. It also has applications in the software and web domains. This book will enable you to build better PostgreSQL applications and administer databases more efficiently. We begin by explaining the advanced database design concepts in PostgreSQL 9.6, along with indexing and query optimization. You will also see how to work with event triggers and perform concurrent transactions and table partitioning, along with exploring SQL and server tuning. We will walk you through implementing advanced administrative tasks such as server maintenance and monitoring, replication, recovery and high availability, and much more. You will understand the common and not-so-common troubleshooting problems and how you can overcome them. By the end of this book, you will have an expert-level command of the advanced database functionalities and will be able to implement advanced administrative tasks with PostgreSQL.

Who is this book for?

If you are a PostgreSQL data architect or an administrator who wants to understand how to implement advanced functionalities and master complex administrative tasks with PostgreSQL, then this book is perfect for you. Prior experience of administrating a PostgreSQL database and a working knowledge of SQL is required to make the best use of this book.

What you will learn

Get to grips with the advanced features of PostgreSQL 9.6 and handle advanced SQL

Make use of the indexing features in PostgreSQL and fine-tune the performance of your queries

Work with the stored procedures and manage backup and recovery

Master the replication and failover techniques

Troubleshoot your PostgreSQL instance for solutions to the common and not-so-common problems

Learn how to migrate your database from MySQL and Oracle to PostgreSQL without any hassle

What do you get with Print?

Instant access to your digital eBook copy whilst your Print order is Shipped

Paperback book shipped to your preferred address

Download this book in EPUB and PDF formats

Access this title in our online reader with advanced features

DRM FREE - Read whenever, wherever and however you want

Frequently bought together

PostgreSQL Administration Cookbook, 9.5/9.6 Edition

Mex$1353.99

Mex$1251.99

Mex$1251.99

Total Mex$ 3,857.97

Joao FirJur Apr 01, 2019

Gostei da qualidade geral do livro, bem como do fato de ser uma obra bem completa - do meu ponto-de-vista.

Amazon Verified review

PK Jan 07, 2018

My main gripe with this book isn’t that it’s entirely useless – it isn’t – but rather that it doesn’t come close to being what it claims to be. Whatever it is, it certainly isn’t ‘a comprehensive guide for PostgreSQL developers and administrators’. Nor is it likely to help anyone ‘Master' PostgreSQL 9.6 (let alone the up-to-date version of the application). Instead, the book is a bunch of tips, some of which might be of value, others not so much. Rather than covering everything systematically, the author has written about those areas which he knows something about and has left the rest out.For example, the chapter ‘Writing Stored Procedures’ doesn’t discuss PL/V8 or PL/R – except to say that they are ‘beyond the scope of this chapter’. Clearly, they are relevant and should have been covered. Material on PL/V8 would have been particularly helpful, since this is not mentioned in any of the (five) other Packt books on PostgreSQL which I have. The GitHub page gives no help at all with installation on Windows, and documentation beyond that is scarce.The rest of the chapter on stored procedures rambles aimlessly through a few features of PL/pgSQL and touches on PL/Python and PL/Perl. In the few examples that were given, keywords are used without introduction, and the code is explained barely or not at all. Another irritant of this chapter is the constant and detailed references to Oracle code. The author is obviously familiar with Oracle: later in the book, he devotes several pages to migrating databases from Oracle and MySQL – but nothing about migrating from MS SQL. Getting up to speed with MS SQL would presumably have taken a bit more trouble than he was prepared to put in.Other parts of the book are equally sloppy and incomplete. For example, the discussion on pgpass on p.268 mentions that it ‘can be found in the %APPDATA%postgresqlpgpass.config path’. Since this isn’t a path, it’s obviously not there. It’s actually in the file (not the path) %APPDATA%\postgresql\pgpass.conf. Given that this is in the official documentation, there's no excuse for not checking it before bunging it in the book and causing frustration to his readers. Some sources say that the file should be in UNIX format. It's precisely this kind of detail which the author of a 'comprehensive' guide to PostgreSQL should get right. The password problem is a big one for Windows users, because PSQL defaults to the currently-logged-in user, who might not even have a password.The book doesn’t claim to be about PostgreSQL 10. However, PG 10 is mentioned in the text, showing that it was available at the time of writing. For example, the section on WAL vaguely mentions the idea of ‘tuning’ the transaction log in PG 10. Unfortunately it fails to mention that it’s been moved to a different directory in this version. Again, information which would be genuinely useful is simply missing.I haven’t read through the entire book yet, and it’s possible that there are some gems. What I have found, however, is superficial and chaotic. There are also endless typos and grammatical mistakes throughout the book, as is usual for Packt Publishing.Frankly, I am tired of computer books written by authors who don't care and published by publishing companies who care even less. This book cost $55 and was barely worth half that.

Olivier Dec 11, 2017

J'espèrai avoir des explications plus détaillées que ce que l'ont trouve sur internet. Hélas, il ne m'a pas été possible de parcourir ce livre car la version kindle ne présente que peu de pages et je me rends compte que le livre présente chaque fonction avec un rapide exemple très simple, sans plus avant de passer à la fonction suivante. Je regrette de ne pouvoir le rendre.

Transaction 1	Transaction 2
`BEGIN;`	`BEGIN;`
`UPDATE t_test SET id = id + 1 RETURNING *;`
User will...

Transaction 1	Transaction 2
`BEGIN;`
`SELECT sum(balance) FROM t_account;`
User will see `300`	`BEGIN;`
	`INSERT INTO t_account (balance) VALUES (100);`
	`COMMIT;`
`SELECT sum(balance) FROM t_account;`
User will see `400`
`COMMIT;`

Transaction 1	Transaction 2
`BEGIN;`	`BEGIN;`
`UPDATE t_deadlock SET id = id * 10 WHERE id = 1;`	`UPDATE t_deadlock SET id = id * 10 WHERE id = 2;`
`UPDATE t_deadlock SET id = id * 10 WHERE id = 2;`
Waiting on transaction 2	`UPDATE t_deadlock SET id = id * 10 WHERE id = 1;`
Waiting on transaction 2	Waiting on transaction 1
	Deadlock will be resolved after one second (`deadlock_timeout`)
`COMMIT;`	`ROLLBACK;`

Session 1	Session 2
`BEGIN;`
`SELECT pg_advisory_lock(15);`
	`SELECT pg_advisory_lock(15);`
	It has to wait
`COMMIT;`	It still has to wait
`SELECT pg_advisory_unlock(15);`	It still waiting
	Lock...

Mastering PostgreSQL 9.6: A comprehensive guide for PostgreSQL 9.6 developers and administrators

What do you get with Print?

Mastering PostgreSQL 9.6

Understanding Transactions and Locking

Working with PostgreSQL transactions

Understanding basic locking

Making use of FOR SHARE and FOR UPDATE

Understanding transaction isolation levels

Observing deadlocks and similar issues

Utilizing advisory locks

Optimizing storage and managing cleanup

Summary

Page 1 of 9

Key benefits

Description

Who is this book for?

What you will learn

Product Details

What do you get with Print?

Product Details

Frequently bought together

Table of Contents

Recommendations for you

Customer reviews

People who bought this also bought

About the author

FAQs

Mastering PostgreSQL 9.6: A comprehensive guide for PostgreSQL 9.6 developers and administrators

What do you get with Print?

Contact Details

Shipping Address

Billing Address

Key benefits

Description

Who is this book for?

What you will learn

Product Details

What do you get with Print?

Contact Details

Shipping Address

Billing Address

Product Details

Packt Subscriptions

Frequently bought together

Table of Contents

Recommendations for you

Customer reviews

People who bought this also bought

About the author

FAQs