![]() The keys_updated information bit determines the lock mode. => UPDATE accounts SET acc_no = 20 WHERE acc_no = 2 Ĭtid | xmax | lock_only | is_multi | keys_upd | keyshr_lock | shr_lock => UPDATE accounts SET amount = amount + 100.00 WHERE acc_no = 1 So, we start a transaction and update the amount in the first account (the key is unchanged) and the number of the second account (the key is changed): To look into pages, we need a pretty familiar pageinspect extension.įor convenience, we will create a view that shows only the information of interest: xmax and some information bits.ĬASE WHEN (t_infomask & 128) > 0 THEN 't' END AS lock_only,ĬASE WHEN (t_infomask & 4096) > 0 THEN 't' END AS is_multi,ĬASE WHEN (t_infomask2 & 8192) > 0 THEN 't' END AS keys_upd,ĬASE WHEN (t_infomask & 16) > 0 THEN 't' END AS keyshr_lock,ĬASE WHEN (t_infomask & 16+64) = 16+64 THEN 't' END AS shr_lockįROM heap_page_items(get_raw_page('accounts',0)) 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). ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |