Post #190,283
1/15/05 4:07:02 AM
|
Questions
This one updated information about a person in several tables.
Why was it spread over several tables? Perhaps your issue was poor normalization or schema design. Many shops tend to create too many tables.
In this case there were locks being held on person data that lasted the entire request.
Were they done through transaction mechanisms, or explicit application "lock" commands?
________________
oop.ismad.com
|
Post #190,291
1/15/05 9:12:24 AM
|
Don't try to solve the problem
It has been solved. By people with a lot more knowledge and experience than you. Plus who had access to the full details of the situation, which you don't.
As for being spread over several tables, that is a legacy of an inherited database. It would make sense to address that, but that isn't a bottleneck right now.
However that had nothing to do with the problem that I just described.
As for the source of the locks, I thought that I made it clear. The locks were internal to the database, done at the application level through the database's transaction mechanisms.
Getting more specific, the database was Oracle, and everything is in a transaction therefore the locks (in accord with Oracle's usual configuration) are only held from an update to a commit. (As opposed to from a select to a commit, which you'd find in most other databases - which is why they don't tend to make transactions the default.) That is why moving the update to just before the commit removed the long-lasting lock. Knowing that we could safely do so in this case required understanding concurrency and what was being updated in our application.
Which underscores another point. It is not enough to wave a hand and say, "Use transactions." You have to understand how they work. It turns out that how transactions are handled varies from database to database in non-trivial ways. You simply cannot safely code for concurrency the same way with Oracle and Sybase because the transaction semantics are different. If you say otherwise then you're either ignorant or an idiot. And unless you understand the exact semantics of your database, you shouldn't be responsible for developing any high-volume concurrent application against it.
So your answer reflected both ignorance of what kinds of issues concurrent applications hit under load, and also ignorance of the fact that transactions are not created equal.
Regards,
Ben
I have come to believe that idealism without discipline is a quick road to disaster, while discipline without idealism is pointless. -- Aaron Ward (my brother)
|
Post #190,347
1/16/05 2:16:01 AM
|
Dude, you are rude. Grow some people skills
So your answer reflected both ignorance of what kinds of issues concurrent applications hit under load, and also ignorance of the fact that transactions are not created equal.
I didn't answer anything. I only asked questions.
Of course you have to bring in or have transaction experts on hand for heavy-duty issues. However, they tend to only have to focus a handful of bottleneck areas rather than every transaction.
________________
oop.ismad.com
|
Post #190,458
1/17/05 1:57:50 AM
|
This was one of your most hilarious posts ever, Bryce.
|
Post #190,533
1/17/05 10:59:48 PM
|
I am learning NOT to flame back. Me grow up........I think
________________
oop.ismad.com
|
Post #190,552
1/18/05 2:00:54 AM
|
A: Doubt it. B: Still, YOU calling SOMEONE ELSE rude=>funnee
|
Post #190,858
1/19/05 6:29:29 PM
|
Yes, you did answer something
You said, Just use databases and transactions. Fixed! This was in response to a post suggesting that programmers need to learn to deal with concurrency.
The problem is that this is not enough in real applications! You can wave the magic wand of databases and transactions, but you haven't replaced the need to understand how transactions work.
And, contrary to what you claim, it is stupid to just say, "OK, so now we need concurrency experts." It is easier to understand concurrency issues than it is to learn a new application. Therefore if concurrency matters, it is more efficient to make sure that your programmers understand concurrency than it is to try to hire an expert after the fact to solve the problem. This goes doubly because bad application design can easily make scalability impossible, and programmers who do not understand concurrency issues have no idea when they are taking a dangerous path.
Put another way, it can be faster and cheaper to hire competent people in the first place than it is to hire incompetents then hire competent people to fix the crap that the incompetents produce.
Regards,
Ben
I have come to believe that idealism without discipline is a quick road to disaster, while discipline without idealism is pointless. -- Aaron Ward (my brother)
|
Post #190,459
1/17/05 3:18:05 AM
|
lock and load
...everything is in a transaction therefore the locks (in accord with Oracle's usual configuration) are only held from an update to a commit. (As opposed to from a select to a commit, which you'd find in most other databases... The reason for this is that there are two common types of locking strategies (isolation levels): Repeatable Read and Cursor Stability. Repeatable Read means just that. Once you've read a record, you can go back an reread it and be guarenteed that you will get the identical data again. This tends to accumulate locks which can lead to lock escalation (row to page to table to tablespace/dbspace), but if you need to be sure that when the commit is issued that all the data the app depends on is consistent, this is what you need (financial apps particularly). Cursor Stability only maintains share locks on "the current row" (and maybe the one on either side in the access order) and exclusive locks when when a row has been inserted or updated. I don't know about Oracle, but a 'select...for update' in DB2 obtained a share lock and was only escalated to exclusive once the update command was issued. (Share locks allow multiple readers and no writers, exclusive locks allow no other readers or writers.) There are two other locking strategies: Read Committed and Read Uncommited. These are dangerous in the hands of those with a "little learning". Read Committed is like Cursor Stability without the share locks, meaning you aren't even guarenteed that the row you just read can be re-read again. In a high volume read-only application, this is great. Read Uncommited, sometimes called Dirty Read, means you can read any row regardless of what others are doing, meaning the row you get could have old values. This is great for data warehousing where you are only interested in aggregate values, not in particular values. In transactional systems, where the inexperienced want to use it for "performance", it is disaster. Have fun,
Carl Forde
|
Post #190,539
1/17/05 11:24:42 PM
|
re: lock and load
Interesting you mention this. I just completed my ColdFusion certification (yeah, I know), and my study-guide had these four transaction isolation levels as "suggestions to the DB" in order of speed (low to high) and "safety" (high-to-low):
* serializable (default)
* repeatable_read
* read_committed
* read_uncommitted
________________
oop.ismad.com
|
Post #190,407
1/16/05 4:31:58 PM
|
Question for you
Do you think it's ever appropriate to not have a schema "properly normalized"? Hint: I think it is. I can give you an example if you like.
===
Purveyor of Doc Hope's [link|http://DocHope.com|fresh-baked dog biscuits and pet treats].
[link|http://DocHope.com|http://DocHope.com]
|
Post #190,421
1/16/05 8:01:04 PM
|
All the damn time
Data warehousing demands it, unless you have an infinite budget for CPUs.
And even then , the overhead of the multi-cpu matrix will peak and kill you.
|
Post #190,423
1/16/05 8:05:00 PM
|
No hints from the peanut gallery
I was asking someone else what he thinks.
===
Purveyor of Doc Hope's [link|http://DocHope.com|fresh-baked dog biscuits and pet treats].
[link|http://DocHope.com|http://DocHope.com]
|
Post #190,426
1/16/05 8:11:15 PM
|
Give him the benefit
> Why was it spread over several tables?
> Perhaps your issue was poor normalization
> or schema design. Many shops tend to
> create too many tables.
He was leading up to a critique of full normalization
which in turn kill performance. Of course, "poor"
normalization is a "poor" way of stating it, but
it wasn't to the level of George.
|
Post #190,431
1/16/05 8:29:32 PM
|
Fairy nuff
I read his question as, "Why would you have informatoin about one person in multiple tables?" I assumed he's advocating a "theoretically correct" schema that wouldn't work in the real world. I concede the possibility he's describing a badly over-normalized schema, one of which I have seen in production and it blew chunks.
So tablizer, what were you getting at?
===
Purveyor of Doc Hope's [link|http://DocHope.com|fresh-baked dog biscuits and pet treats].
[link|http://DocHope.com|http://DocHope.com]
|
Post #190,457
1/17/05 1:57:15 AM
|
Barry's right; overnormalization is 1 of Bryce's hobbyhorses
|
Post #190,540
1/17/05 11:25:14 PM
|
Huh? What did I over-normalize?
________________
oop.ismad.com
|
Post #190,551
1/18/05 1:57:24 AM
|
It means the opposite of what you seem to think it does. HTH
|
Post #190,689
1/18/05 9:33:12 PM
|
Okay then, what did I UNDER-normalize?
Is this about that time that the info-world forums took a dump, I helped recreate the threads using a FoxPro script, and then we got into a big argument about storing lists of ID's in a cell instead of creating a many-to-many table? It was a one-shot script, not production ebay. So, lighten up.
________________
oop.ismad.com
|
Post #190,762
1/19/05 10:29:32 AM
|
SIGH... "A hobby-horse" means, something you like to...
...*go on and on about*, not something you like to *do*.
That's why "over-normalization" is "a hobby-horse" of yours -- because you *rant* about it all the time.
I was *not* saying that you like to *do* it.
Is comprehension *finally* starting to -- however slowly -- seep in?
Or shall we go over this for a few more rounds?
[link|mailto:MyUserId@MyISP.CountryCode|Christian R. Conrad]
(I live in Finland, and my e-mail in-box is at the Saunalahti company.)
Your lies are of Microsoftian Scale and boring to boot. Your 'depression' may be the closest you ever come to recognizing truth: you have no 'inferiority complex', you are inferior - and something inside you recognizes this. - [link|http://z.iwethey.org/forums/render/content/show?contentid=71575|Ashton Brown]
|
Post #190,859
1/19/05 6:35:22 PM
|
Note: in this case it really is a bad idea
Our team inherited the database from another team. We created a view that joins the tables together into the view that we need, and querying that view has never come close to being a bottleneck, so we've never fixed it.
But we wouldn't have designed it that way in the first place, and if there were signs that this was becoming a bottleneck, then we'd fix it. (The application wouldn't need much fixing.)
Cheers,
Ben
I have come to believe that idealism without discipline is a quick road to disaster, while discipline without idealism is pointless. -- Aaron Ward (my brother)
|
Post #190,536
1/17/05 11:17:29 PM
|
Re: Question for you
Software design involves gambles. Non-normalizing may help for certain kinds of usage, but at the risk of not being able to handle future requirements that are not favored by the unnormalized stuff. Whether it is worth gambling away future flexibility to get performance for a specific usage pattern today is a business decision similar to financial decision cost analysis. I would suggest looking for ways to improve performance under normalization before giving in to denormalization for speed.
And as somebody pointed out, historical (read-only) data is sometimes easier to sift or use if denormalized. This is because most of such research is done from a customer sales perspective rather than concerned with internal processes about already-completed stuff.
________________
oop.ismad.com
|
Post #190,547
1/18/05 1:25:03 AM
|
Also called trade-offs.
... I would suggest looking for ways to improve performance under normalization before giving in to denormalization for speed. I would agree. IMO, far too many people underestimate maintainability problems with a difficult schema, normalized or not. And as somebody pointed out, historical (read-only) data is sometimes easier to sift or use if denormalized. This is because most of such research is done from a customer sales perspective rather than concerned with internal processes about already-completed stuff. This is actually true right now for the application I'm maintaining. I've found normalizing wins points when it eliminates clearly duplicated and errant data. However, it is possible to hide the normalization in the interface rather than do it right down at the database. Whilst this makes for hairy code, it is doable and is sometimes the solution. But it sacrifices maintainability. Then, too, I've found that completely normalising the data can sometimes make it harder to use the database. In the simplest case, code to fetch data might change from reading one or two tables to having to dance on 6 or 7. Often not a huge problem, true, until an update is required, too. Wade.
Is it enough to love
Is it enough to breathe
Somebody rip my heart out
And leave me here to bleed
| |
Is it enough to die
Somebody save my life
I'd rather be Anything but Ordinary
Please
| -- "Anything but Ordinary" by Avril Lavigne. |
|
Post #190,696
1/18/05 9:53:21 PM
|
re: Also called trade-offs
Then, too, I've found that completely normalising the data can sometimes make it harder to use the database. In the simplest case, code to fetch data might change from reading one or two tables to having to dance on 6 or 7. Often not a huge problem, true, until an update is required, too.
I generally only see this if somebody creates a lot of 1-to-1 or 1-to-0:1 relationships. I generally frown on that. When in doubt, make one bigger table instead of multiple smaller (or skinnier) tables.
________________
oop.ismad.com
|
