INSERT INTO QueueTest (ID, Locked, Priority) VALUES (2, NULL, 3) INSERT INTO QueueTest (ID, Locked, Priority) VALUES (1, NULL, 4) Note, two interesting articles here and here.ĪLTER TABLE QueueTest ADD CONSTRAINT PK_QueueTest PRIMARY KEY (ID) ĬREATE INDEX IX_QueuePriority ON QueueTest(Priority) In SQL Server, TOP magically appears to limit the number of records after successfully attaining locks. This is contrary to how SQL Server appears to handle the UPDLOCK, ROWLOCK and READPAST lock hints. This means that if I want to pull one row from a table, and two threads concurrently execute the same SQL, one will receive the row and the other an empty result set (even if there are more eligible rows). How can I guarantee that I retrieve a certain number ( N) rows, assuming there are at least N rows eligible?įrom what I have seen, Oracle applies the WHERE predicate before determining what rows to skip. There are a few questions on how to implement a queue-like table (lock specific rows, selecting a certain number of them, and skipping currently locked rows) in Oracle and SQL Server.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |