IWETHEY v. 0.3.0 | TODO
1,095 registered users | 0 active users | 0 LpH | Statistics
Login | Create New User
IWETHEY Banner

Welcome to IWETHEY!

New Simplistic Database locking.
I've been developing a bit of a familiarity with MySQL, including finding out some of its limitations. The biggest hurdle I've got at the moment is that the readlocks are sometimes a bit too simplistic for the application I'm supporting. To wit: a long-running SELECT will block updates to the same table. Whilst this is not unexpected, I was wondering whether this is widespread in databases. I know Oracle can support asynchronous access, for instance. I started looking at MaxDB but the documentation is extremely formal and it was hard to tell.

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.

New AFAIK, Oracle is the only one using its model
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)
New Postgres allows it too
New Does it now?
If that's true, they should *definitely* be making more of that.

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.

New Re: Simplistic Database locking.
To wit: a long-running SELECT will block updates to the same table. Whilst this is not unexpected, I was wondering whether this is widespread in databases. I know Oracle can support asynchronous access, for instance. I started looking at MaxDB but the documentation is extremely formal and it was hard to tell.

I'm not sure what you mean by asynchronous access in this context, but a SELECT should not normally block an update an MySQL. At least for any 4.x version of the database, I'm not at all familiar with older versions.

Jay
New The SELECT does.
It's MySQL 4.0. What I meant by asynchronous access is sometimes called decoupled access. Both ideas are implemented by versioning as Todd and Christian describe.

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.

New Re: The SELECT does.
SELECT ... FROM is a consistent read, reading a snapshot of the database and setting no locks unless the transaction isolation level is set to SERIALIZABLE. For SERIALIZABLE level, this sets shared next-key locks on the index records it encounters.

A consistent read means that InnoDB uses its multi-versioning to present to a query a snapshot of the database at a point in time. The query see the changes made by exactly those transactions that committed before that point of time, and no changes made by later or uncommitted transactions. The exception to this rule is that the query sees the changes made by the transaction itself that issues the query.

That is from MySQL's documentation, and sounds very much like the systems mentioned elseware in this discussion. This is from the InnoDB documentation, other engines might be different. But the above says that a normal SELECT should never issue any locks on the table at all.

The only really bad MySQL locking issue that I'm aware of is this.
A locking read, an UPDATE, or a DELETE generally set record locks on every index record that is scanned in the processing of the SQL query. It does not matter if there are WHERE conditions in the query that would exclude the row from the result set of the query. InnoDB does not remember the exact WHERE condition, but only knows which index ranges were scanned.

That is rather ugly, and could easily cause performance problems.

Jay
New It's not an InnoDB table.
And I can't change it. Everything in the database is MyISAM; partly because that's the default. Besides, the server doesn't have InnoDB turned on, and the table requires a FullText index (which isn't supported on InnoDB).

I'm getting the impression that the loud and popular "free" database, MySQL, is still several years behind practically everyone else. Including the other "free" database, PostGres. I don't think The Powers That Be will be at all interested in changing the database. :-/

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.

New "Including 'THE' other..." ?!? :-(
If you're beginning to think it's ridiculous that so many people think of MySql as "THE" free database... then imagine how much "better" it is to imply that there is just ONE *other* free database.

(The most ironic thing about this is that PostGres, too, is just an upstart compared to [link|http://firebird.sourceforge.net/|Firebird]/[link|http://www.borland.com/interbase/|Interbase]; the latter began life at about the same time as, or even before, Oracle.)


   [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]
New Quite.
However, when the un-knowledgeable look for a "free" database, their first two choices are usually MySQL then PostGreSQL. As I've just unwittingly demonstrated. :-)

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.

New That would be a problem.
MyISM is really only sutible for setting up a single user / single computer database. MySQL has been focusing on InnoDB based databases, since that is the database version that is transaction safe.

I'm getting the impression that the loud and popular "free" database, MySQL, is still several years behind practically everyone else. Including the other "free" database, PostGres. I don't think The Powers That Be will be at all interested in changing the database. :-/

MySQL is popular because it is fast and easy, but it is least SQL compliant of the popular databases. MySQL 5 seems to move it up a lot terms of SQL compliance and adds some key features, but I have not had a chance to play with it much. MySQL's popularity is really grounded in the fact that many low cost web hosting companies support nothing but MySQL.

Jay
New The "Multi-Generational Architecture"...
...of [link|http://firebird.sourceforge.net/|Firebird], as well as its closed-source ancestor [link|http://www.borland.com/interbase/|InterBase], makes that problem moot for those two.

WTF is he gibbering about, you ask? Here, read this [link|http://www.ibphoenix.com/main.nfs?a=ibphoenix&page=ibp_conf_opt#Multi_generational|explanation of Multi-Generational Architecture].


   [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]
New More commonly known as MVCC
Multi Version Concurrency Control

[link|http://www.postgresql.org/docs/7.4/static/mvcc.html|http://www.postgresq.../static/mvcc.html]

[link|http://www.google.com/search?client=safari&rls=en-us&q=mvcc+database&ie=UTF-8&oe=UTF-8|http://www.google.co...ie=UTF-8&oe=UTF-8]

I continue to be amazed at mysql's popularity given its limitations and the availability of postgres.



"Whenever you find you are on the side of the majority, it is time to pause and reflect"   --Mark Twain

"The significant problems we face cannot be solved at the same level of thinking we were at when we created them."   --Albert Einstein

"This is still a dangerous world. It's a world of madmen and uncertainty and potential mental losses."   --George W. Bush
New You'd be amazed
Ever seen a large application (backed by MySQL in this case) where every query used exclusively left joins? Guess why? The guy deciding what db server to use only knows one join syntax, and it's LEFT JOIN. Doesn't grasp the performance issues. Doesn't care about the fact that it potentially returns different data. (You can always add "DISTINCT" to the SELECT clause.)

Oh, and doesn't see the need for triggers, stored procedures, or referential integrity constraints, because "real programmers" take care of all that stuff in their code.

Yes, he actually puts down anyone wishing for referential integrity checks as just being lazy. Woo!

(Nah, we don't find corrupt data on a near-daily basis. Why do you ask?)
===

Purveyor of Doc Hope's [link|http://DocHope.com|fresh-baked dog biscuits and pet treats].
[link|http://DocHope.com|http://DocHope.com]
New MySQL Gotchas
[link|http://sql-info.de/mysql/gotchas.html|MySQL Gotchas] scary stuff. Here's another that isn't on that list (yet?): Update ... limit ... [link|http://dev.mysql.com/doc/mysql/en/update.html|MySQL update syntax]. The where clause specifies n rows, the limit says (n-x), so x rows aren't updated and you have no way to know which ones. How is that ever a good idea?

Have fun,
Carl Forde
New My favorite quote:
6. Oh yes, this page does not render well in Internet Explorer, run along and get yourself a proper browser.
--
[link|mailto:greg@gregfolkert.net|greg],
[link|http://www.iwethey.org/ed_curry|REMEMBER ED CURRY!] @ iwethey

[link|http://it.slashdot.org/comments.pl?sid=134485&cid=11233230|"Microsoft Security" is an even better oxymoron than "Military Intelligence"]
No matter how much Microsoft supporters whine about how Linux and other operating systems have just as many bugs as their operating systems do, the bottom line is that the serious, gut-wrenching problems happen on Windows, not on Linux, not on Mac OS. -- [link|http://www.eweek.com/article2/0,1759,1622086,00.asp|source]
New Somebody's channeling Peter in 'snarky' mode.
New That postgres link is really useful.
It is very close to the MaxDB documentation. Hmm. I'm getting the idea that multi-versioning databases are letting themselves get walked over by MySQL.

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.

New mysql
is the answer to that complex problem of data storage that is obvious and simple and wrong.



"Whenever you find you are on the side of the majority, it is time to pause and reflect"   --Mark Twain

"The significant problems we face cannot be solved at the same level of thinking we were at when we created them."   --Albert Einstein

"This is still a dangerous world. It's a world of madmen and uncertainty and potential mental losses."   --George W. Bush
New That is the question *I* keep asking MySELF.
Why MySQL? Doesn't really work well.

PostgresQL scales much better and can manage much more data with fewer resources.
--
[link|mailto:greg@gregfolkert.net|greg],
[link|http://www.iwethey.org/ed_curry|REMEMBER ED CURRY!] @ iwethey

[link|http://it.slashdot.org/comments.pl?sid=134485&cid=11233230|"Microsoft Security" is an even better oxymoron than "Military Intelligence"]
No matter how much Microsoft supporters whine about how Linux and other operating systems have just as many bugs as their operating systems do, the bottom line is that the serious, gut-wrenching problems happen on Windows, not on Linux, not on Mac OS. -- [link|http://www.eweek.com/article2/0,1759,1622086,00.asp|source]
     Simplistic Database locking. - (static) - (19)
         AFAIK, Oracle is the only one using its model -NT - (ben_tilly) - (2)
             Postgres allows it too -NT - (broomberg) - (1)
                 Does it now? - (static)
         Re: Simplistic Database locking. - (JayMehaffey) - (6)
             The SELECT does. - (static) - (5)
                 Re: The SELECT does. - (JayMehaffey) - (4)
                     It's not an InnoDB table. - (static) - (3)
                         "Including 'THE' other..." ?!? :-( - (CRConrad) - (1)
                             Quite. - (static)
                         That would be a problem. - (JayMehaffey)
         The "Multi-Generational Architecture"... - (CRConrad) - (8)
             More commonly known as MVCC - (tuberculosis) - (7)
                 You'd be amazed - (drewk)
                 MySQL Gotchas - (cforde) - (2)
                     My favorite quote: - (folkert) - (1)
                         Somebody's channeling Peter in 'snarky' mode. -NT - (inthane-chan)
                 That postgres link is really useful. - (static) - (2)
                     mysql - (tuberculosis) - (1)
                         That is the question *I* keep asking MySELF. - (folkert)

Naive is one word for it.
153 ms