Re: How to update multiple rows atomically

Bob Badour wrote:
Marshall wrote:

...

Paul,

I'm also unsatisfied.

I have done a fair bit of concurrent programming. I've become
reasonably facile with locks and so forth, and can write complex
multithreaded Java programs without deadlock or race conditions.
(Mostly.) But I'm not happy with it. Even if *I* can do it, it is
often the case that other people on my team can't. I think even
the built-in support for concurrency in Java is too low-level.
As has been said before numerous times, shared state concurrency
is the wrong default.
...


The only really interesting thing I have read regarding concurrency and databases was in Dennis Shasha's performance tuning book. What was it again, though? One of these days I really do have to unpack. It's been a year and a day since the purchase of this house closed, and I still have not unpacked.

One of these days I have to get over this procrastination thing.

Heh heh, Edward de Bono gave this advice about how a neophyte should approach trying to master a field - buy all the books you can afford but don't read them, just keep them for reference!

When it comes to concurrency, I've pretty much blown my load once I say that a dbms needs to offer nothing more than a way to serialize a group of statements. Maybe this is the equivalent of the TTM comma operator.

But I'll ramble on anyway. Personally, I've found that users are fine with apps that very occasionally time out because of races or simple contention. They don't find this unreasonable as long as invoking a retry isn't onerous, which the app designers can cater for and as long as they don't have to undo partial work, which the dbms should cater for.

Normalization sometimes goes a long way - I can remember two different systems that were replaced, one IMS and one ACP, both so-called OLTP systems with thousands of concurrent users who were accustomed to attempting certain back-office inventory functions on a scheduled basis, like only at lunchtime on the third Thursday of the month! These systems had massive logical records with sometimes hundreds of 'fields'. Users were delighted with the replacement systems that let them do those functions more or less at will with only rare misfires. It was really only a matter of re-thinking the schemas for a few high-frequency 'tables' and using very selective locking for the high-frequency transactions that the back-office ones were competing with. I can recall adding sentinel logic to a few logical transactions but rarely for small systems that had only dozens of users and after all, RT allows multiple equivalent logical 'arrangements'. The sequence of user actions is often pre-ordained, implying that certain locks make other locks redundant. Similarly for the mix of types of transactions at peak periods. Concurrency is as much part of an app's spec as anything else and it's not hard to predict it based on schemas and transaction volumes with nothing much besides Little's Law.

No experience with so-called concurrent languages nor massive internet systems but I imagine the basic techniques are similar, eg., think of an app that has both high-use and intensive functions as two apps, imagine separate schemas and then make a combination that marries them. If things still look tough, go back to the requirements. In a big system (which is where the big contention problems happen), there are always frivolous requirements that the big boss is willing to trash once their performance implication is explained.

Sometimes I think it is just undisciplined ego, "because it's there", that drives people to make a problem that needs to be solved out of every phenomenon whereas I think some problems are meant to be avoided, not solved, like certain back alleys late at night. There's lots that RT is silent about and that's okay by me - it's about a model for handling data, not a prescription for a modelling language. After all, RT is silent about disk head seek times too.

p
.

Relevant Pages

  • Urgent: Concurrency in SQL Server 2005: How to use granularity of Locks in Snapshot isolation level
    ... I have developed a VS 2005 database application which has the concurrency ... Locking granularity (database, table, [age, row, ... Versioning (snapshot isolation, read committed ... I would like to use the granularity of locks (rows or index ...
    (microsoft.public.sqlserver.clients)
  • Concurrency in a multi-user environment
    ... concurrency in a multi-user PHP/MySQL environment. ... does locking a record prevent file locks (when new ... numeric field in the table. ...
    (php.general)
  • Re: Serious concurrency problems on fast systems
    ... I worked on a big Java Enterprise project a while ago that had highly concurrent deployment but made quite a number of concurrency mistakes that hugely slowed it down. ... Aside from the fact that the JVM optimizes lock acquisition in the uncontended case, once a thread blocks on a monitor, all the other threads also trying to acquire that monitor also block. ... On that big project we proved this with various enterprise monitoring products that reported on locks, wait times for locks and other performance issues. ... We did three things on that project to improve concurrency: eliminated shared data, made shared data immutable, and used 'java.util.concurrent' classes. ...
    (comp.lang.java.programmer)
  • Re: Locking of nodes in tree for concurrent access
    ... Some item is categorized by N criterias (a criteria is the value ... number of concurrent accesses of the tree), ... For a good understanding of Java concurrency in practice, ... Java 6 has much more efficient handling of locks than earlier versions. ...
    (comp.lang.java.programmer)
  • Re: Optimistic Concurrency - how? - net 2.0
    ... implicit transactions. ... How does SQL Server implement optimistic concurrency in a way that helps ...
    (microsoft.public.dotnet.framework.adonet)