Re: installing Oracle on Debian AMD64
Sven Mueller wrote:
Postgresql locks at less than table-granularity all the time.
Fine. I never said it wouldn't.
The point is (and I wasn't clear) is that the locking in MySQL can
become a major gotcha if you're not paying attention in MySQL, while
it's a constant in PostgreSQL.
It is also easier to be fast writing if you lock the
whole table and prevent others from accessing it while you update
things. Slows down reading to stopped while you do a write though.
More like stops every other read and write.
for as long as the write takes.
Which means you won't be doing any bulk-data loading on a busy database.
In fact, it's enough of a problem MySQL AB had to had the special
'INSERT DELAYED' syntax to work around it for batch loads.
Actually, I didn't exactly get it backwards. MyISAM _is_ fast to write
(and fast to read) as long as you only write or only read.
It's only fast to write compared to anything else because it doesn't
bother with any sort of transactional state.
Write speed of a single transaction isn't generally interesting, save
for the single case of bulk loads/updates. Even then, it's not that
interesting, as in most databases you'll end up structuring your bulk
loads into some sort of batch transaction to increase speed.
So yes, it's fast for a single write. But no one really cares about
that. We care how fast it is with concurrent writes, and it's jsut
plain terrible.
There are certainly other drawbacks in MyISAM, but I wouldn't call them
"horrible things".
Funny, most DBAs on any other platform would call lack of transactions
(ignoring replications' binary log psuedo-transactions) and referential
integrity, and the ability to mount and use totally corrupted tables all
horrible things.
4.0 is far older than a year and fixed many things which where
problematic in 3.x.
Except the subqueries and the stored procedures. Which are big ticket
items.
5.0 has reached GA a few weeks ago and its release
candidates (and even its betas) effectively were really stable.
Not interested. If it's not gold, it's not worth using here. Sorry ;p
That a
lot of shops still run 3.x is their problem, the upgrade can easily be
done with only a few (like 2) minutes of downtime if you are careful.
The code upgrade, sure. But 5.x isn't totally compatible with 3.x, and
to use the new, nifty features, you have to move to InnoDB which isn't
just a drop and go thing.
So no, it takes way more than 2 minutes of downtime. It takes an almost
complete revaluation of what you're currently storing and doing.
The whole point of a transaction is to prevent an inconsistent database
in the first place.
Which doesn't contradict me.
You're right, it doesn't, but your use of the word 'instead' confused me.
I don't understand what you would want a lock for inside a transaction.
Consider the simple case of a forum, and a column that indicates posts
per thread. That must be updated on every post, and the code might look
something like this (psuedo code):
BEGIN
VAR pc;
INSERT INTO posts VALUES (22, 'my post', 'my name', 'some post text');
SELECT INTO pc post_count FROM threads WHERE tid = 22;
UPDATE threads SET post_count = pc+1 WHERE tid=22;
COMMIT;
Now, if all transactions are serial (i.e., always executed one after
another) this will work correctly. But, no database runs in
serializable mode, because it's worse than slow.
They run in READ COMMITTED or REPEATED READ modes. The former ensures a
single read will be consistent, the latter, all reads will be
consistent within a single transaction.
But they say nothing about other transactions. As such, it's possible
for two transactions to run at the same time, see the same value of pc,
and update it to the same value. That's a bug.
As such, you'd traditionally either use serialized mode, which kills
concurrency, or use a row-level lock. Only you can't do that in MySQL
for complicated transactions, as you lose your atomic context.
Well, you can, if you want to write the cleanup code yourself. But
that's a major PITA, and why we use techniques like RAII in programming
languages.
Now, to be fair to MySQL, it does provide a solution in the
SELECT ... FOR UPDATE syntax[1], but that's just another fine example of
it deviating from the way everyone else does things.
I mean either the transaction is atomic (and therefor doesn't need a
lock) or it isn't (and might need one). Maybe I overlook something, but
as far as I can tell, a transaction shouldn't need to set locks.
Yes, you should read any database guide (even the MySQL one for InnoDB)
on transaction isolation levels, and what they mean. It's a gotcha for
many people.
Just to make sure I understand you correctly:
You say that if you use a user set lock inside a transaction, the
transaction context is invalidated.
Yes, LOCK TABLES really looks like:
COMMIT
REALLY LOCK TABLES
And if I understand you correctly,
this means that the actions taken inside the transaction are executed as
if outside a transaction and therefor not automatically rolled back
anymore. Right?
Yes. More importantly, you can't ever take a transaction inside a lock.
OK, this sounds different to what you said above. What you say here is
that you can't do something along the lines of:
<set lock>
<...>
<begin transaction>
.....
<commit transaction>
<remove lock>
If that is true (I can't tell, I never tried that), this is definately a
problem.
Yes, you cannot, see:
http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html, and I quote:
LOCK TABLES is not transaction-safe and implicitly commits any
active transactions before attempting to lock the tables. Also,
beginning a transaction (for example, with START TRANSACTION)
implicitly performs an UNLOCK TABLES
They provide a work around, but it's not perfect for all cases. A
ROLLBACK is impossible under their workaround (*Ack*)
What I meant is that a transaction is (or rather: should be) either
completely executed or completely rolled back if an error occured.
Yes, as I said above, I misunderstood what you meant by "instead". Mea
cupla.
If I understood this restriction correctly, it has another nasty effect:
You can't do multiple transactions inside a UDF in PostgreSQL. That's
bad IMHO.
No, you cannot do this:
BEGIN
COMMIT
BEGIN
COMMIT
You might be able to do:
BEGIN -- Implicit
BEGIN
-- Stuff
COMMIT
COMMIT -- Implicit
But I honestly couldn't tell you, as I've never written that complex of
an UDF in PostgreSQL.
Adam
[1] http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html
Reply to: