Performing an update or delete via a table scan does not acquire a table-level exclusive lock as it did in earlier versions. In version 11.9.2, the scan acquires an exclusive intent table lock when the transaction starts. When the scan locates a row that must be updated, it acquires an exclusive page or row lock, depending on the locking scheme. The only exception is an unindexed update or delete to a data-only-locked table at transaction isolation level 3; these updates acquire an exclusive table lock. In previous versions, any update or delete that does not use an index acquires a table-level exclusive lock at the start of the transaction.
In most cases, performing the scan without using a table level lock increases concurrency. However, some applications may experience one of these problems:
Applications that did not deadlock in earlier versions may experience deadlocks in 11.9.2.
Tasks that update a large number of rows may use a large number of locks if they cannot perform lock promotion due to conflicting locks.
Workarounds:
If deadlocks are creating problems, consider converting the table to use datarows locking.
If deadlocks or excessive numbers of locks are creating problems, consider using the lock table command to acquire an exclusive table lock before you begin the transaction.
