@SeanGallardy I was under the impression that when @LockOwner is Transaction (the default) then it behaves the same way as any other lock. So you need to use XACT_ABORT ON to guarantee rollback, otherwise you are in the same position as any other lock taken in a transaction.
I agree that when under @LockOwner='Session' then you need to very carefully consider your backout code, in particular how connection pooling plays with it. But not sure why you'd ever use that option anyway.
Also that you do need to check the result of the EXEC for the error conditions (timeout, deadlock, cancellation), and it would be nicer if those errors just threw as normal.
First, I would avoid making a round trip to the database for every value. For example, if your application knows it needs 20 new IDs, do not make 20 round trips. Make only one stored procedure call, and increment the counter by 20. Also it might be better to split your table into multiple ones.
...
It is my first choice because I have researched and stress tested it only once, and I can reuse it in any situation - the serializing has already happened, so everything that happens after sp_getapplock does not affect the outcome. With standard locking mechanisms, I can never be so sure - adding an index or just getting another execution plan can cause deadlocks where before there were none. Ask me how I know. — A-KMar 16, 2013 at 1:35
I sometimes think people advocating "normal locking mechanisms" have never dealt with a real production database
For things like ensuring a procedure is run as a singleton, it is possible to (ab)use standard locking to achieve the same result, but I think an app lock conveys the intention better.
To show the contents of the spool's internal worktable?
I mean, as I said, there are other ways, but they're quite convoluted compared with setting a trace flag
@Charlieface It does not, but (a) that's not really relevant in the question's context; and (b) which return code would you care about and how would you handle it in that specific scenario?
It's a shame sys.dm_db_page_info isn't more useful, or maybe we need a new DMV sys.dm_db_page_body_info. Then we could just apply it inside the same query.
> -1 The lock request timed out. -2 The lock request was canceled. -3 The lock request was chosen as a deadlock victim.
The examples don't use a time out do they? Lock request cancelled is never seen. I suppose a deadlock is possible in theory, but it's not really that uncommon to omit error handling code when demoing a separate concept.
I guess. Having said that, usually the whole point of using it is to avoid a deadlock, so you have to do something really stupid (like take locks in different orders in different places) to get a deadlock.
What does it mean to "cancel" a lock anyway? As opposed to a timeout?
MichaelSwart points out that sp_releaseapplock shouldn't be used under RCSI anyway, as the COMMIT does that. michaeljswart.com/2021/01/…
@Charlieface Seems like a moot point. You protect the thing that needs locking until it doesn't need locking any more. I suppose some people might be unaware that ending the transaction releases the app lock, but they should read the documentation.
People like that are the reason peanut butter has a warning about possibly containing nuts
Yes buried in the docs is the line "When a lock is opened with the Transaction lock owner, that lock is released when the transaction is committed or rolled back." But the also quite prominently show incorrect examples with sp_releaseapplock so I think the point is justified.
> Locks placed on a resource are associated with either the current transaction or the current session. Locks associated with the current transaction are released when the transaction commits or rolls back. Locks associated with the session are released when the session is logged out. When the server shuts down for any reason, all locks are released.
> An interesting restatement of these requirements is that the spool can cache a maximum of one result row for each value of the correlated apply value. In other words, the correlated apply parameters are a key for the spool’s cache.
@PaulWhite should correlated apply value be plural since correlated parameters below it is?
I toyed with "parameter(s)" but adjusting the are/is as well seemed too much.
It's a bit muddled. Perhaps I will have another go. In this specific instance, there is one correlated parameter. In the general case, there might be more.
> An interesting restatement of these requirements is that the spool can cache a maximum of one result row for each value of the correlated apply parameter(s). In other words, the correlated apply parameters form a key for the spool’s cache.
DECLARE @cur CURSOR;
DECLARE @rows int;
DECLARE @db_name sysname;
SET @cur = CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT [d].[name]
FROM [sys].[databases] [d]
ORDER BY [d].[name];
OPEN @cur;
SET @rows = @@CURSOR_ROWS;
WHILE @rows > 0
BEGIN
FETCH NEXT FROM @cur INTO @db_name;
PRINT @db_name;
SET @rows -= 1;
END;
CLOSE @cur;
while using dm_exec_cursors the properties output looks like TSQL | Fast_Forward | Read Only | Local (0) which reminds me of your answers doing things like @scrollopt integer = 16 | 8192 | 16384 | 32768 | 1048576
@ErikReasonableRatesDarling You could try predicting the output of:
USE master;
DECLARE
@cur CURSOR;
DECLARE
@rows integer,
@table_name sysname,
@name_like sysname = N'%monitor%';
SET @cur = CURSOR
LOCAL FORWARD_ONLY DYNAMIC READ_ONLY
FOR
SELECT
T.[name]
FROM sys.tables AS T
WHERE
T.[name] LIKE @name_like;
SET @name_like = N'%options%';
OPEN @cur;
WHILE 1 = 1
BEGIN
FETCH NEXT FROM @cur INTO @table_name;
IF @@FETCH_STATUS = -1 BREAK;
PRINT @table_name;
END;
CLOSE @cur;
> Meteor Lake design has three types of cores: Performance Cores (P-Cores), Efficient Cores (E-Cores), both located in the Compute Tile, and Low Power Efficient Cores located in the SOC tile.
I mean a heap is not an index, and fill factor specifies how much to fill up a leaf-level page of an index before creating another leaf-level page. Right?
i have a very large temp table that gets updated many times, in ways that can't be easily collapsed to the initial insert. for example, some updates depend on the results of what other updates accomplish. the query plans are all very fast up to the point of the update operator, which often takes 30+ seconds to perform its task. i would guess that a lot of this time is page splitting as non-null values are added.
i could add default values to the initial insert, but i'd have to change a bunch of downstream query logic.
it's easy enough to add a clustered index with a lower fill factor, but i like the heap a bit.
Avoiding updates might be a good answer, I have sometimes found with levels of derived calculations in CTEs, it ends up faster to store them in new structures like yet another table - potentially indexed differently and then move onto the next set of calculations in the same way you think about it in your head, just instantiated instead of CTEs.
@ErikReasonableRatesDarling I just found a new way to break MERGEdbfiddle.uk/kdv1XQYq
2
On my local instance the error is:
Msg 5180, Level 22, State 1, Line 46
Could not open File Control Block (FCB) for invalid file ID 111 in database 'Sandpit'.
Verify the file location.
Execute DBCC CHECKDB.
Msg 596, Level 21, State 1, Line 45
Cannot continue the execution because the session is in the kill state.
Msg 0, Level 20, State 0, Line 45
A severe error occurred on the current command. The results, if any, should be discarded.
Just saw somebody comment on a q about bulk insert on Stack Overflow, he says you should drop your indexes and recreate them afterwards, for "better performance". Well, yeah, but at a cost of rebuilding the indexes, so doesn't make sense unless you're inserting most or all of the whole table.
@PaulWhite Speaking of which, the SSL cert expired and I'm 80% done making it a stand alone application, not that you of all people would need it, but just giving an update.
