Yes, MySQL locks the table completely whilst it does the ALTER TABLE statement. Most of this time is spent in physically copying the table, which is why it is recommended you put all your necessary changes together in the one ALTER TABLE statement.
There are several approaches to mitigating this problem on a live database if you can't get a decent maintenance window.
First of all, many environments have timeouts of several minutes waiting for a table to become available for their query and don't know (and it's really difficult to check) just why the table is locked. I've used this quirk of a live website to do changes to tables. On a site I used to look after, I figured we had an allowance of about 7 minutes before someone started noticing. :-) It helps to make sure your boss is on your side.
Another way to do it is to do the select-insert-rename trick. This works well if the table either has fairly low UPDATE frequency, or is purely the target of INSERTs. The basic steps are to copy the table's schema, make the necessary changes, craft up a statement to do a INSERT...SELECT from the old to the new, and rename the tables (do the rename in one statement). You also need prepare ahead of time a statement to copy over any "new" records that got added or updated between the SELECT and the RENAME. I also did this a few times in a past job.
However, there are come caveats:
- You will almost certainly have problems if the source table is MyISAM, unless perhaps the table is almost never written to. This is because of the way MyISAM tables lock. It works better with InnoDB tables because it can still be read whilst your big
INSERT...SELECT is running.
- You need a foolproof way of figuring out records that have been added or changed between the
SELECT and the RENAME. For tables that are only used for INSERT, use the auto_increment column. For tables that get UPDATEs, you will need a reliable last-modified column.
Other ways to tackle this problem involve modifying slaves and failing the application over. This is more closely tied up with how your databases replicate. I also haven't done this one myself, so I can't describe exact steps.
Finally, there are a dozen server settings you can twiddle and several more that are much harder to change that will affect how long it takes to copy a table. The sort buffer is one, but also how much memory MySQL is allowed to use is another. (Remember that you can set a lot of those per connection, too, rather than setting some of them high globally.) When dealing with a lot of data, MySQL has a 'tipping point' effect where things are fairly linear up to a certain size, and then go to hell suddenly. It often comes up with complex queries working with a lot of data and is related to internal temporary table sizes and how much memory it is allowed to use, but it can come up with table alterations because they involve re-indexing the data. That is one reason why giving a database more memory is almost always a good thing.
CAUTION The answer about changing the UNIX password for "postgres" through "$ sudo passwd postgres" is not preferred, and can even be DANGEROUS!
This is why: By default, the UNIX account "postgres" is locked, which means it cannot be logged in using a password. If you use "sudo passwd postgres", the account is immediately unlocked. Worse, if you set the password to something weak, like "postgres", then you are exposed to a great security danger. For example, there are a number of bots out there trying the username/password combo "postgres/postgres" to log into your UNIX system.
What you should do is follow Chris James's answer:
sudo -u postgres psql postgres
# \password postgres
Enter new password:
To explain it a little bit. There are usually two default ways to login to PostgreSQL server:
By running the "psql" command as a UNIX user (so-called IDENT/PEER authentication), e.g.: sudo -u postgres psql. Note that sudo -u does NOT unlock the UNIX user.
by TCP/IP connection using PostgreSQL's own managed username/password (so-called TCP authentication) (i.e., NOT the UNIX password).
So you never want to set the password for UNIX account "postgres". Leave it locked as it is by default.
Of course things can change if you configure it differently from the default setting. For example, one could sync the PostgreSQL password with UNIX password and only allow local logins. That would be beyond the scope of this question.
Best Answer
Yes. The community edition is by far the most widely deployed version of MySQL in production environments. You can thank Oracle's acquisition of Sun for the confusion.
Transactions are indeed supported in the InnoDB storage engine, which is included in MySQL community edition.
The enterprise edition comes with a support contract, as well as a few extra bells and whistles: MySQL Enterprise Monitor, MySQL Enterprise Backup, and MySQL Workbench. It should be noted that these tools are actually very good, and do add some justification for the price instead of it being just a support contract.
If you want open source alternatives to MySQL Enterprise Backup I suggest Percona Xtrabackup. I'm not aware of any good alternatives to their Monitor product though.
Another thing worth considering is using the drop-in replacement for MySQL named Percona Server with XtraDB. It too is free and based off of the MySQL code. They have applied several (widely used) patches to improve MySQL performance on modern hardware (multi-core in particular) as well as improvements for things like replication, monitoring, and reliability.