![]() ![]() Rather, it checks that no concurrent transaction has modified the data between the time we read them and the time we modify the database. Solving the probem with “optimistic locking”ĭifferent from pessimistic locking, “optimistic locking” does not actually lock the contended objects. Pessimistic locking is a good strategy if conflicts are likely. ![]() Such concurrent transactions are preemptively blocked. This technique is called “pessimistic locking” since it expects that there will be concurrent transactions that “disturb” our processing. RAISE EXCEPTION 'cannot leave a prison without guards' ĭROP TRIGGER IF EXISTS checkout_trig ON jail_app.on_duty ĬREATE TRIGGER checkout_trig AFTER DELETE ON jail_app.on_dutyįOR EACH STATEMENT EXECUTE PROCEDURE jail_app.checkout_trig() ORDER BY on_duty.prison_id, on_duty.guard_id * of the prisons where somebody went off duty, For this we need a statement level trigger with a transition table (new since v10):ĬREATE OR REPLACE FUNCTION jail_app.checkout_trig() RETURNS trigger We want to avoid deadlocks, so we will make sure that we always lock rows in the same order. Our trigger now becomes a little more complicated. This effectively serializes data modifications, so it reduces concurrency and hence performance.ĭon’t consider locking the whole table, even if it seems a simpler solution. We can avoid the race condition by explicitly locking the rows we check. Solving the problem with “pessimistic locking” With trigger functions written in any other language, you have no way to “peek” at uncommitted data. You could potentially do the same if you write a trigger function in C, but few people are ready to do that. This is against the normal MVCC rules but guarantees that constraints are not vulnerable to this race condition. When checking constraints, PostgreSQL also checks rows that would normally not be visible to the current transaction. After all, this is a consequence of PostgreSQL’s multi-version concurrency control (MVCC). Given the above, you may wonder if regular constraints are subject to the same problem. Do normal constraints have the same problem? But don’t forget there are bad people out there, and they may attack your application using exactly such a race condition (in the recent fad of picking impressive names for security flaws, this has been called an ACIDRain attack). You may think that this is a rare occurrence and you can get by ignoring that race condition in your application. That means that the second transaction succeeds, both guards go off duty, and the prisoners can escape. Now the first transaction has not yet committed when the second UPDATE runs, so the trigger function running in the second transaction cannot see the effects of the first update. Now if Chris happens to have the same idea at the same time, the following could happen (the highlighted lines form a second, concurrent transaction): The prison guard application runs a transaction like the following: What is wrong with our trigger constraint? RAISE EXCEPTION 'sorry, you are the only guard on duty' ĬREATE TRIGGER checkout_trig BEFORE DELETE ON jail_app.on_dutyįOR EACH ROW EXECUTE PROCEDURE jail_app.checkout_trig() īut, as we will see in the next section, we made a crucial mistake here. Unfortunately there is no way to write this as a normal database constraint (if you are tempted to write a CHECK constraint that counts the rows in the table, think again).īut it would be easy to write a BEFORE DELETE trigger that ensures the condition:ĬREATE FUNCTION jail_app.checkout_trig() RETURNS trigger We want to establish a constraint that at least one guard has to be on duty in any given prison. Naïve implementation of a constraint as triggerĪs guards go on and off duty, rows are added to and deleted from on_duty. So, Alice is on duty in Karlau, and Bob and Chris are on duty in Stein. INSERT INTO jail_app.on_duty (prison_id, guard_id) VALUES Then we have a junction table that stores which guard is on duty in which prison: INSERT INTO jail_app.guard (guard_id, guard_name) VALUES INSERT INTO jail_app.prison (prison_id, prison_name) VALUES Suppose we have a table of prisons and a table of prison guards: It will also familiarize you with the little-known PostgreSQL feature of “constraint triggers”. This article describes how to do this and what to watch out for. In such a case it is tempting to use triggers instead. Sometimes you want to enforce a condition on a table that cannot be implemented by a constraint. Constraint development sql help trigger © Laurenz Albe 2019
0 Comments
Leave a Reply.AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |