![]() And let's create a table of accounts, same as in the last article. Indeed, if xmax in a tuple matches an active (not yet completed) transaction and we want to update this very row, we need to wait until the transaction completes, and no additional indicator is needed. And this very number xmax is used to indicate a lock. This ID shows that the transaction deleted the tuple. The UPDATE command itself selects the minimum appropriate locking mode rows are usually locked in the FOR NO KEY UPDATE mode.Īs you remember, when a row is updated or deleted, the ID of the current transaction is written to the xmax field of the current up-to-date version. FOR NO KEY UPDATE mode assumes a change only to the fields that are not involved in unique indexes (in other words, this change does not affect foreign keys).FOR UPDATE mode assumes a total change (or delete) of a row.Two of them are exclusive locks, which only one transaction can hold at a time. There are 4 modes that allow locking a row. So, the number of locks used is proportional to the number of simultaneously running processes rather than to the number of rows being updated. ![]() And to this end, we can request a lock on the ID of the locking transaction (to remind you, the transaction itself holds this lock in an exclusive mode). If we need to wait for a row to be released, we actually need to wait until completion of the locking transaction: all locks are released at the transaction commit or roll back. To this end, "normal" locks have yet to be used. Let alone monitoring, but something needs to be done for the queue. And monitoring is impossible either (to count the locks, all the table needs to be read). ![]() Actually, the ID of the xmax transaction, along with additional information bits, serves as the indicator we will look at how this is organized in detail a little later.Ī pro is that we can lock as many rows as we want without consuming any resources.īut there is also a con: since the information on the lock is not available in RAM, other processes cannot be queued. It means that it is not a lock in a usual sense, but just some indicator. PostgreSQL stores information that a row is locked only and exclusively in the row version inside the data page (and not in RAM). The situation with row-level locks is different. Some database management systems apply escalation of locks: if the number of row-level locks gets too high, they are replaced with one, more general lock (for example: a page-level or an entire table-level).Īs we will see later, PostgreSQL also applies this technique, but only for predicate locks. There are different approaches to solving this problem. But we cannot afford to have its own lock for each row either. There is no doubt that we want a change of one row not block other rows of the same table. On the other hand, the higher the granularity, the more of the memory is occupied by locks.The higher granularity of locks, the lower the contention among concurrent processes.A lock must be available somewhere in the shared memory of the server.Let's recall a few weighty conclusions of the previous article. We will also talk of wait queues and of those who jumps the queue. In this article, we will see how row-level locks are organized in PostgreSQL and how they are used together with object-level locks. Last time, we discussed object-level locks and in particular relation-level locks.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |