4 ms·

One unmentioned reason this is powerful: It avoids concurrency bugs and inconsistencies introduced by write skew, in cases where the application layer (eg. Ruby

One unmentioned reason this is powerful: It avoids concurrency bugs and inconsistencies introduced by write skew, in cases where the application layer (eg. Ruby/Python) makes business logic decisions based on data that has become stale. These are hardest to detect and avoid in the case where business logic depends on no rows matching (count(*) == 0), eg. meeting room scheduling systems, because there is nothing for a transaction to lock! Stored procedures can avoid this entirely, even under the weaker transaction isolation levels, by keeping all the logic in the DB and running it atomically. At Zipcar we took this approach to writing the web reservation system in the early 2000s. Later, it allowed us to easily add a telephone reservation system without duplicating business logic. It worked great back then and it still works great, if you have good ways of managing the tradeoffs (version control, unit testing, etc).

balfirevic 7y ago
> Stored procedures can avoid this entirely, even under the weaker transaction isolation levels, by keeping all the logic in the DB and running it atomically I don't understand. Multiple SQL statements executed inside stored procedures are not run any more atomically than multiple SQL statements executed from application layer.
- jimktrains2 7y ago
  > Multiple SQL statements executed inside stored procedures are not run any more atomically than multiple SQL statements executed from application layer. Yes, but not wrapping those multiple application-generated queries in a transaction is a common bug I've encountered at many of the places I've worked. It also requires a lot more round-trips to the database.
Merad 7y ago
Ive worked on several apps that tried this approach and each of them was a mess. I’ve seen two fundamental problems with building your app on top of stored procs: * Tooling around sql is generally inferior to what’s available for <pick your favorite language>. I’ve yet to see a company with effective automated tests around their database... it’s far more common to have _no_ tests around the database. Even if you’re the unicorn that does have all of that figured out, it still tends to be more difficult for your devs to write and test code. * Most devs are poor to mediocre when it comes to sql. You’re either going to have to hire more specifically for sql, or force your devs to do complex work (your business logic) in a toolset that they aren’t that good at. IMO this is a case where a few applications may have significant concurrency issues that warrant the database business logic approach... but for your average app, it’s unnecessary and makes life more difficult for your team.
- ramraj07 7y ago
  This has always struck me as odd, how can _any_ competent developer suck at SQL especially after spending maybe a week or two trying to write logic in it? As "languages" go it can't get any simpler, you're just directly forced to think about data in a model that's extremely close to the data itself. Seems to me that a dev that can't think well in SQL with minimal training is not a good dev at all, and this could actually be a nice filter.
mistahenry 7y ago
Can’t you just handle these sorts of consistency issues with uniqueness, foreign key, and check constraints?
dragonwriter 7y ago
> These are hardest to detect and avoid in the case where business logic depends on no rows matching (count(*) == 0), eg. meeting room scheduling systems, because there is nothing for a transaction to lock! The predicate lock held by a read transaction at the serializable transaction level will, in fact, prevent this. If you need something that secured it between DB transactions because you need a longer-running business transaction but need to avoid long-running DB transactions, you can place a hold with a pending record in a reservations table and appropriate exclusion constraint without a stored proc. > Stored procedures can avoid this entirely, even under the weaker transaction isolation levels, by keeping all the logic in the DB and running it atomically. Isn't that just manually replicating, on an ad hoc basis, the machinery the server already has for implementing serializable isolation level? There may be times when doing this by hand is justified for performance or other reasons, but in general doing special-case replication of features for which the DB server had general solutions is a pretty poor use of development time.

balfirevic 7y ago

> Stored procedures can avoid this entirely, even under the weaker transaction isolation levels, by keeping all the logic in the DB and running it atomically I don't understand. Multiple SQL statements executed inside stored procedures are not run any more atomically than multiple SQL statements executed from application layer.

jimktrains2 7y ago
> Multiple SQL statements executed inside stored procedures are not run any more atomically than multiple SQL statements executed from application layer. Yes, but not wrapping those multiple application-generated queries in a transaction is a common bug I've encountered at many of the places I've worked. It also requires a lot more round-trips to the database.

Merad 7y ago

Ive worked on several apps that tried this approach and each of them was a mess. I’ve seen two fundamental problems with building your app on top of stored procs: * Tooling around sql is generally inferior to what’s available for <pick your favorite language>. I’ve yet to see a company with effective automated tests around their database... it’s far more common to have _no_ tests around the database. Even if you’re the unicorn that does have all of that figured out, it still tends to be more difficult for your devs to write and test code. * Most devs are poor to mediocre when it comes to sql. You’re either going to have to hire more specifically for sql, or force your devs to do complex work (your business logic) in a toolset that they aren’t that good at. IMO this is a case where a few applications may have significant concurrency issues that warrant the database business logic approach... but for your average app, it’s unnecessary and makes life more difficult for your team.

ramraj07 7y ago
This has always struck me as odd, how can _any_ competent developer suck at SQL especially after spending maybe a week or two trying to write logic in it? As "languages" go it can't get any simpler, you're just directly forced to think about data in a model that's extremely close to the data itself. Seems to me that a dev that can't think well in SQL with minimal training is not a good dev at all, and this could actually be a nice filter.

mistahenry 7y ago

Can’t you just handle these sorts of consistency issues with uniqueness, foreign key, and check constraints?

dragonwriter 7y ago

> These are hardest to detect and avoid in the case where business logic depends on no rows matching (count(*) == 0), eg. meeting room scheduling systems, because there is nothing for a transaction to lock! The predicate lock held by a read transaction at the serializable transaction level will, in fact, prevent this. If you need something that secured it between DB transactions because you need a longer-running business transaction but need to avoid long-running DB transactions, you can place a hold with a pending record in a reservations table and appropriate exclusion constraint without a stored proc. > Stored procedures can avoid this entirely, even under the weaker transaction isolation levels, by keeping all the logic in the DB and running it atomically. Isn't that just manually replicating, on an ad hoc basis, the machinery the server already has for implementing serializable isolation level? There may be times when doing this by hand is justified for performance or other reasons, but in general doing special-case replication of features for which the DB server had general solutions is a pretty poor use of development time.