Try creating a temp table, and then perform a bait-and-switch
ALTER TABLE stuff RENAME oldstuff
CREATE TABLE newstuff LIKE oldstuff;
ALTER TABLE newstuff DROP INDEX idx_UpdatedTime;
INSERT INTO newstuff SELECT * FROM oldstuff;
INSERT INTO newstuff (x,y,z) SELECT x,y,z FROM inventory;
ALTER TABLE newstuff ADD INDEX idx_UpdatedTime(UpdatedTime);
ALTER TABLE newstuff RENAME stuff;
DROP TABLE oldstuff;
This approach may take longer but should establish some continuity for your processing
I renamed stuff to oldstuff to remove any possibility access to the stuff table during this process
Assuming the availability of an index to provide the requested sort order (SomeColumn ASC) the sample query you posted will in all likelihood take a row lock, even where the index in question is not covering for the query.
The query processor is smart enough to know that reading a single row from one end of an index (possibly followed by a lookup to retrieve additional columns) is a low-cost enterprise that suits a row locking strategy.
If there is no such index, or the execution plan is more complex than the sample query suggests (perhaps because the table is partitioned, or there is a WHERE clause), so that a significant amount of data must be read (and probably sorted) then the engine may start with page locks.
Note that ROWLOCK hints are suggestions. The engine can and will ignore the hint in some circumstances. This differs from other 'hints' like FORCESEEK that will throw an error if it cannot be honoured.
One reason not to use ROWLOCK is that it runs the risk of a very large number of locks being taken (and possibly held to the end of the transaction, depending on the exact circumstances). If there are conflicting locks on the table (or partition), these row locks would be prevented from escalating to a partition or table lock to release memory, which could affect the entire instance.
Ultimately, the details depend on localized details that are not provided in the question. That simple query should not require a ROWLOCK hint, given correct indexing. Nevertheless, the evidence suggests that page locks are currently being taken, which READPAST cannot skip.
If this query is being used to implement some sort of queuing mechanism, you would probably benefit from reading Remus Rusanu's excellent article on Using Tables As Queues.
Best Answer
The "normal" result is the INSERT query waiting until the lock is released. But there are some limits and special cases (you wrote about locking TABLES so MyISAM or explicit
LOCK TABLESstatement)READ LOCALtable lock. If you explicitly lock the table without theLOCALmodifier, this feature cannot be used according to the manual.But nowadays you should mostly use the InnoDB engine and transaction. In that case there are usually no explicit table locks used. There still can be metadata locks. But according to the manual InnoDB will not escalate row locks to table locks, instead using Multiple granularity locking according to https://www.percona.com/blog/2010/06/08/table-locks-in-show-innodb-status/ .
With row locks many operations can run in parallel and inserts should only wait if there is a gap lock on the gap into which the insert should put the inserted row.