3 ms·

Your gripe about locks seems quite exaggerated. At least compared to my own experience. Locks are a problem, yes, but not more so than most systems. Most moder

by jeltz 15y ago

Your gripe about locks seems quite exaggerated. At least compared to my own experience. Locks are a problem, yes, but not more so than most systems. Most modern SQL databases support MVCC (multi-version concurrency control) which means they do not have to take any read locks, so your arguments really only applies to UPDATE where I do not think any database supports explicit order for the locking. Deadlocks in databases are generally detected which causes one transaction to abort. So they are not as harmful as in some other environments. Databases always in MVCC: PostgreSQL, MySQL+InnoDB, Oracle (if I remember correctly) Databases with optional MVCC: MS SQL, SQLite EDIT: Fixed some typos.

cameronh90 15y ago
It's true that MVCC does mitigate some of the problems, but in a write-heavy OLTP database (such as the one I'm working on now) it's still a significant issue. I also am not aware of any database that supports explicit ordering for the locks, but it's only ever really an issue when using a relational database since you have to lock multiple tables for a single entity. I agree that deadlocks aren't as harmful, but they are a pain and do hurt performance, so it's another thing you have to worry about.
- jeltz 15y ago
  I agree with you about that writing write-heavy applications in SQL is much more painful than writing read-heavy. I have been hit by various types of deadlocks including the annoying foreign key deadlocks. In PostgreSQL 9.2 they are working on removing false (as in they are not necessary to preserve consistency) foreign key deadlocks so they should be less common if that patch lands.
- leif 15y ago
  MySQL with TokuDB handles write-heavy OLTP workloads well, even with multiple complicated indexes. Since it uses Fractal Tree indexing, writes are so fast that locks aren't held long enough for this to become a problem.

by jeltz 15y ago

cameronh90 15y ago

It's true that MVCC does mitigate some of the problems, but in a write-heavy OLTP database (such as the one I'm working on now) it's still a significant issue. I also am not aware of any database that supports explicit ordering for the locks, but it's only ever really an issue when using a relational database since you have to lock multiple tables for a single entity. I agree that deadlocks aren't as harmful, but they are a pain and do hurt performance, so it's another thing you have to worry about.

jeltz 15y ago
I agree with you about that writing write-heavy applications in SQL is much more painful than writing read-heavy. I have been hit by various types of deadlocks including the annoying foreign key deadlocks. In PostgreSQL 9.2 they are working on removing false (as in they are not necessary to preserve consistency) foreign key deadlocks so they should be less common if that patch lands.
leif 15y ago
MySQL with TokuDB handles write-heavy OLTP workloads well, even with multiple complicated indexes. Since it uses Fractal Tree indexing, writes are so fast that locks aren't held long enough for this to become a problem.