what are range-locks?

Question

well,the topic introduce Isolation in wikipedia have such words,links is here

......... Serializable This is the highest isolation level. With a lock-based concurrency control DBMS implementation, serializability requires read and write locks (acquired on selected data) to be released at the end of the transaction. Also range-locks must be acquired when a SELECT query uses a ranged WHERE clause, especially to avoid the phantom reads phenomenon (see below). When using non-lock based concurrency control, no locks are acquired; however, if the system detects a write collision among several concurrent transactions, only one of them is allowed to commit. See snapshot isolation for more details on this topic.

but whole topic didn't explain "range-locks",google alse have no accurate description.

what's "range-locks",and which different with "read lock" and "write lock"?

thanks!

Answer 1

The easiest way to use locking (not all databases use locking at all) to prevent concurrent operations colliding, is to lock an entire database. This would make everything one-by-one though, which would be terribly slow.

Next down, is if you lock a whole table. At least other tables aren't slowed down.

Narrowest is to lock a row. You can safely update that row and not block other operations.

A range lock is in between the last two. Cases where it's used include:

1. A range of values are affected by the query.
2. A non-unique index is affected by the query (because there can be more than one row matching e.g. 23, so 23 identifies a range rather than a row).
3. Inserting a new auto-incremented key (lock the between range max(curvalue) + indexincrement and ∞).

Answer 2

If you use a range in the WHERE clause the database will lock each possible tuple in this range and also the next tuple (before and after). If there is no next tuple available in a direction it will lock completely in this direction.

So the SELECT is reliable within the transaction.