Re: Simplistic Database locking.

Post #199,381

3/18/05 9:40:45 AM

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

Post #199,662

by static

3/21/05 12:49:10 AM

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.

Post #199,701

by JayMehaffey

3/21/05 9:16:52 AM

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

Post #199,855

by static

3/21/05 6:46:33 PM

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.

Post #199,895

by CRConrad

3/22/05 2:57:24 AM

"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]

Post #199,901

by static

3/22/05 6:17:56 AM

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.

Post #199,916

by JayMehaffey

3/22/05 9:43:49 AM

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

Welcome to IWETHEY!