Chapter 15. Locks

Here's a locking analogy to consider.

Think of an office with men's and women's washrooms. Doubtless a man could actually lock the men's washroom when he goes in, but that would be inefficient for three reasons: (1) men who only want to comb their hair are excluded whether or not their purpose would conflict with another occupant 's; (2) other men can't tell from a distance whether the door is locked; (3) nobody can open the door for maintenance or emergencies. To avoid these problems, it's smarter to use a system of signs. For example, a man scrawls "occupied" on the wall by the bathroom door or, better yet, he flies a flag above his cubicle so that everyone knows where he's going. Of course, there are different flags for urinals (which can be shared) and for cubicles (of which there's only one). Women have flags too, but because they use a different washroom they can ignore the men's flags. The system works if the men check the flags before going to the washroom, lower their own flags as soon as they get out, time their trips for low traffic periods, and avoid declaring emergencies. The measure of the system's efficiency isn't how quickly any given man can gothat's merely response time. What counts is how much everyone can accomplish in a periodthat's throughput.

DBMSs work the same way. The usual term for this process is locking, though, as our analogy shows, it's really a system of signing and flagging. Let's get some other obvious terminology out of the way.

• A data change is an INSERT, UPDATE, or DELETE statement.

• A transaction-end is a COMMIT or ROLLBACK statement.

• A transaction is a series of SQL statements. It begins with the first SQL statement since the last transaction-end and finishes with a transaction-end statement.

• A read-only transaction is a transaction that doesn't do a data change.

• When two transactions have overlapping start or end times, they are concurrent transactions . To prevent concurrent transactions from interfering with each other, the DBMS arranges a lock .

Your DBMS is responsible for arranging locks, but you control them indirectly. You can enforce your control using statements like SET TRANSACTION and the non-standard SQL extension LOCK TABLE, as well as with a more subtle mechanism: arranging the statements of your transactions correctly. The problem all programmers face here is that if you know only a little about locks, it's very easy to write very bad code indeed. So you must know a lot. In this chapter, we'll give you the information you need to enforce your control of locking efficiently and effectively. We'll look at what a lock is, describe lock modes, talk about why granularity matters, and explain what the various transaction isolation levels do.