Table Locking for Dummies (MySQL)

Writing a multi-threaded program is hard enough to do without having to worry about data concurrency in your database. Lucky for us, MySQL has the methods necessary to help us keep our data current built in. There are two main kinds of locks supported by MySQL, table-level locks and row-level locks. Table-level locks are supported by default, while row-level locks are only supported by some engines, most notably InnoDB.

Table-level locks are achieved through the LOCK TABLES command. The two types of locks are read locks and write locks. Read locks allow data to be read by other threads, but blocks any thread (including itself) from updating that table. This kind of lock is useful if you need to make several calls to a table and the data cannot change between reads. Write locks all the current thread to read and write to the table, while blocking all other threads from reading and writing that table. One use for this type of lock is a queue, where you want to be able to read the first item in the queue, then delete it before another thread reads that same row.

Row-level locking is much more intricate, and can potentially speed up your database usage if used correctly. If you are using the InnoDB engine (or another supporting engine), you can lock just one row of a table, blocking access to it but still allowing other threads access to the rest of the table. As long as your threads are looking at different rows, there will be no slowdown at all. Like in table-level locking, there are two types of locks that can be used. Both of these locks requires a transaction to be started first. SELECT ... LOCK IN SHARE MODE allows other threads to read but not update the row(s) selected by the statement. SELECT ... FOR UPDATE blocks other threads from reading or writing to the row(s) selected. Both locks are kept until the transaction is finished.

Categorized as Database

By Bryan Young

Bryan Young is a staff writer for WebProNews.

Leave a comment