Keep Learning, Keep Growing

Ref:  http://shiroyasha.io/selecting-for-share-and-update-in-postgresql.html https://docs.oracle.com/cd/E17952_01/mysql-5.1-en/innodb-locking-reads.html A regular select statement does not give you enough protection if you want to query data and make a change in the database related to it. Other transactions can update or delete the data you just queried. PostgreSQL offers additional select statements that lock on read and provide an extra layer of safety. This article explores the  select for share  and  select for update  statements, locks that are created with these statements, and provide examples for using these two select statements. Safely Updating Data Sometimes, applications read data from the database, process the data, and save the result back in the database. This is a classic example where the  select for update  can provide additional safety. Let’s consider the following example: BEGIN ; SELECT * FROM purchases WHERE p...

Ref: https://www.postgresql.org/docs/9.5/transaction-iso.html The SQL standard defines four levels of transaction isolation. The most strict is Serializable, which is defined by the standard in a paragraph which says that any concurrent execution of a set of Serializable transactions is guaranteed to produce the same effect as running them one at a time in some order. The other three levels are defined in terms of phenomena, resulting from interaction between concurrent transactions, which must not occur at each level. The standard notes that due to the definition of Serializable, none of these phenomena are possible at that level. (This is hardly surprising -- if the effect of the transactions must be consistent with having been run one at a time, how could you see any phenomena caused by interactions?) The phenomena which are prohibited at various levels are: dirty read A transaction reads data written by a concurrent uncommitted transaction. nonrepeatable read A transact...