So, we have our constraints written into our table triggers, when it requires that you look across several rows in the same table, or another table, in order to verify a business rule. But an interesting point was raised the other day: In a concurrent environment, don't you risk these "constraints" being violated?
This is the example we were using (which credit cards aren't our business, but they're an easy way to get the point across). How would we get around this problem?
A credit card company places a credit limit on person 1 of $10,000. Person 1 attempts to make a puchase of $9,999, the system looks across all debits & credits for that person, notes that they have a balance of $0, and preauthorizes the purchase (ie record inserted but not committed). Person 1 also initiates a separate transaction of $5,000. System doesn't see the first transaction, since it's not committed, so it pre-authorizes the next purchase (record inserted but not committed). Then, both transactions (in separate sessions) are committed, giving the person an overall debit of $14,999, which exceeds their credit limit. This is possible, since the TRIGGERs fire off upon INSERT, not upon COMMIT. So they've already performed their check.
Table-level constraints, I'm thinking a Primary Key in particular - since it applies across rows, actually block other sessions from using an already selected primary key, while a primary key value is inserted into a table - but not yet committed. Seems like it's something we should be doing in the Credit Card situation above...
Am I just using a bad design? Kind of confused as to which direction to go next ... Are we supposed to write these "checks" into the application, so that they're called when the "Save" button is pressed, immediately prior to the commit?
This is the downside of optimistic locking. Typically you would lock something (what exactly, depends on the data model) to indicate to the other application that you are making changes that affect its logic. Care must be taken to make transactions as quick as possible so as not to incur unnecessary lock waits. The waiting transaction would retry until it is able to acquire the lock for itself.