I worked with Microsoft SQL Server 2000 at my previous employer, and was accustomed to explicitly defining the locking mode I wanted, by providing lock hints on queries. MySQL gives less control over locking, but there are ways to control the type of locks a query will acquire. This article explores those techniques.
I’ve been trying to learn more about MySQL locks and deadlocks, and have written several articles on the topic recently, so I’m keen to hear your feedback.
The lock hints I used on SQL Server
The two most common lock hints I used on queries at my former employer were nolock and updlock holdlock. The first hint tells the server not to acquire any locks at all on a query:
select col1, col2 from tbl1(nolock)
That’s a big efficiency win, because locks are extra overhead. The side effect is you could be reading another transaction’s uncommitted data.
The second method was useful for avoiding lock escalation deadlocks. Suppose I read from a table into a temporary table, did some manipulation, and then updated the base table again. Without any lock hints, the initial read would acquire shared read locks, which would be escalated later for writing. If something else acquired shared locks in the meantime, the escalation would cause at least a block, and possibly a deadlock. To avoid this, our DBA’s coding standard was to acquire and hold those write locks as early as possible in the transaction: at the first read. For instance,
create table #temp ... insert into #temp... select col1, col2 from tbl1(updlock holdlock)
Transaction isolation levels
One way to get the same effect in MySQL is to use SQL standard transaction isolation levels (see SET TRANSACTION syntax). For example, to avoid any locks at all, and allow a transaction to read dirty, possibly inconsistent data, use READ UNCOMMITTED. (You can do the same thing in SQL Server 2000, and then omit the (nolock) hint. In SQL Server 2005, I believe more of the standard isolation levels are supported, which provides better multi-versioning. I’m no expert on this, and I don’t keep up with new developments in the SQL Server world anymore, so you’ll have to read elsewhere to find out more.)
MySQL’s default isolation level is REPEATABLE READ. You should read the manual to understand what this really means. It is fairly complex and has interesting implications. A good place to start is on the page InnoDB and TRANSACTION ISOLATION LEVEL.
If you use the SERIALIZABLE isolation level in MySQL, a SELECT statement locks rows in shared mode, similar to the default behavior I discussed above in Microsoft SQL Server.
Lock hints in queries
MySQL does allow you to specify directly what locks should be acquired in a SELECT, with the lock hints FOR UPDATE and LOCK IN SHARE MODE. Basically, FOR UPDATE gets write locks, and LOCK IN SHARE MODE gets shared read locks on the rows read. You can read more about this on the manual page SELECT … FOR UPDATE and SELECT … LOCK IN SHARE MODE Locking Reads.
It’s not the same level of granularity as in SQL Server, because for example you can’t apply a different locking hint to each table involved in a join, but it’s certainly useful.
Server version and configuration
Finally, server version and configuration affects how and when locks are acquired. Not only is the default transaction isolation level configurable, but options that affect replication and binary logging come into play. There’s some discussion about this in the comments on my previous article on deadlocks.
Is there more?
Are there more ways to control locking than I know about? Please post in the comments.
Technorati Tags:No Tags
Baron,
I would not however call MySQL modifiers “hints” as they actually result in different behavior.
Select for Innodb does not set any locks by default and multi versioning is used.
For LOCK IN SHARE MODE or FOR UPDATE modifiers not only locks will be set but versioning will effectively be bypass versioning.
Yes if you plan updating a lot it makes sense to use SELECT FOR UPDATE.
Yes, as far as I recall SQL Server 2000 does NOT do multi-versioning at all; everything is simply controlled through locks. However, I also think I learned at the SQL Server 2005 training I attended (which I have now forgotten) that 2005 does do multi-versioning if you select the right transaction isolation level. So there may be a more valid comparison between 2005 and InnoDB, but it’s a good point that multi-versioning and locking are not the same thing.
We encountered performance problems when we attempted to use READ UNCOMMITTED on the master database for our web site.
Our master database has a load of lots of short queries (about 1000 reads and 250 writes per second). Our few long queries on this database take at most 5 seconds. We are running MySQL 4.1.
For one web page we wanted to not acquire read locks because the main SELECT for the page was known to occasionally run for several seconds, the table got a lot of writes, and there were no application level consistency requirements between rows. We used the following SQL to set this up:
What we later discovered was that this SQL statement sometimes took several seconds to execute. We removed this SQL statement and observed that overall load on our master database dropped substantially.
We don’t know why this happened, but we’re now avoiding READ UNCOMMITTED. Interested in any insight as to why this would be slow, whether this is most likely user error on our part, and any other real world benchmarking of the locking statements.
Thanks!
See Peter’s comment and article — as he points out, an ordinary
SELECTdoesn’t lock any rows. Also, see the section of the MySQL manual dealing with consistent non-locking read. I suspect something else was causing the main query for the page to take so long.