Скачать в pdf «PostgreSQL»

holds waiting for transaction 2 to release write lock attempt to get write lock held by transaction 1 deadlock detected—transaction 2 is rolled back transaction 1 returns from update and commits

Table 10.4: Deadlock

test=> begin work; begin

test=> SELECT * test-> FROM lock_test test-> WHERE name = ‘James’ test-> FOR UPDATE;

Id |    name


521 | James (1 row)

test=> —

test=> — the SELECTed row Is locked test=> —

test=> UPDATE lock_test test-> SET name = ‘Jim’ test-> WHERE name = ‘James’;




Figure 10.9: Select…for update

10.7 Summary

Single-user database queries are concerned with getting the job done. Multiuser queries must be designed to gracefully handle multiple users accessing the same data.

Multiuser interaction can be very confusing, because the database is constantly changing. In a multiuser environment, improperly constructed queries can randomly fail when users perform simultaneous operations. Queries cannot assume that rows from previous transactions still exist.

By learning about PostgreSQFs multiuser behavior, you are now prepared to create robust queries. PostgreSQL has the features necessary to construct reliable multiuser queries.

Chapter 11


In an ideal world, users would never need to be concerned about performance. The system would tune itself. Unfortunately, we do not live in an ideal world. An untuned database can be thousands of times slower than a tuned one, so it pays to take steps to improve performance. This chapter shows you how to get the optimal performance from your database.

Скачать в pdf «PostgreSQL»