Q6.4: What's the purpose of using holdlock?

All select/readtext statements acquire shared locks (see Q6.3) to retrieve their information. After the information is retrieved, the shared lock(s) is/are released.

The holdlock option is used within transactions so that after the select/readtext statement the locks are held until the end of the transaction:

If the holdlock is not used within a transaction, the shared locks are released.

Example

Assume we have the following two transactions and that each where-clause qualifies a single row:

tx #1

begin transaction
/* acquire a shared lock and hold it until we commit */
1: select col_1 from table_a holdlock where id=1
2: update table_b set col_3 = 'fiz' where id=12
commit transaction

tx #2

begin transaction
1: update table_a set col_2 = 'a' where id=1
2: update table_c set col_3 = 'teo' where id=45
commit transaction
If tx#1, line 1 executes prior to tx#2, line 1, tx#2 waits to acquire its exclusive lock until tx#1 releases the shared level lock on the object. This will not be done until the commit transaction, thus slowing user throughput.

On the other hand, if tx#1 had not used the holdlock attribute, tx#2 would not have had to wait until tx#1 committed its transaction. This is because shared level locks are released immediately (even within transactions) when the holdlock attribute is not used.

Note that the holdlock attribute does not stop another transaction from acquiring a shared level lock on the object (i.e. another reader). It only stops an exclusive level lock (i.e. a writer) from being acquired.