locks and indexes

Klaus Aschenbrenner has a post where he creates two tables, and two sessions.  Session one updates table 1 and next selects from table 2.  Session two updates table 2 and selects from table 1.

Without an index on the tables, session one has to do a table scan on table 1; similarly session two and table 2.  The table scan creates an exclusive lock on one row in the tablde the table, and that prevents the select statement in session two select occurring because without an index, it has to read the entire table and ask for a shared lock  – session two can’t get the shared lock because of session one’s exclusive lock.

If there was an index on table 1, session two “should’ be able to find the right value to read – unless it is the  row being updated.  Similarly, session one has to do a table scan to find the correct record to select in Table 1, and can’t since it wants to read a row which is locked by session two.

In another post, he has two sessions running against the same table.  The first session, or first query, gets an update lock while looking for the correct row.  That lock is converted to an exclusive lock, the second query blocked, the data updated, the lock removed and the other query runs.  If the initial lock, for either/both queries was a shared lock, there would not be a block, however a shared lock can’t be converted to exclusive if there is another shared lock on that table\row.  If the first query immediately created an exclusive lock , you would block beguine select queries for the entire update transaction.  The update lock allows select queries, can be converted to exclusive at the right time, but makes the second update query wait until the exclusive lock is released since you can’t have two update locks at the same time.

Advertisements