The spid is recorded in the SQL Server error log, but not the user / application / host. And, as Grant mentioned, nothing is recorded to the built-in system_health session (or the default trace, if you still have that enabled).
EXEC sys.sp_readerrorlog 0, 1, 'single_user';
LogDate ProcessInfo Text
Consider database-qualifying the sp_getapplock proc name. This way, the lock will be acquired in the specified database instead of the current session database context.
EXEC @lock_result = tempdb..sp_getapplock @Resource = 'my_resource',
LockMode = 'Exclusive',
@LockTimeout = 30000;
Modern SQL Server versions will allow you to create a Server Secuirty Audit. They are a bit like the extended events, but leaner.
A basic script to set up SQL Server Security audits could look like this:
Create Server Security Audit
/****** Object: Audit [ServerSecurityAudit2021] Script Date: 03.03.2021 12:39:07 ******/
CREATE SERVER ...
There are any number of ways to do literally what you ask, but I would generally advise against using any of them. In most cases, all you need do is prevent any changes to the table while your process completes. This can be done with a TABLOCKX hint inside the transaction:
SELECT TOP (0) NULL FROM Production.Product AS P WITH (TABLOCKX);
You could also ...
You can't know after the fact. I checked, and system_health doesn't capture this information. However, you can set up Extended Events to capture exactly this in the future. I have examples of auditing database changes here. I don't show setting it to SINGLE_USER, but the example where I change compatibility level would absolutely apply.
You can place it in a stored procedure in one of the databases, then execute it with a cross-database call.
CREATE PROC dbo.LockMy_resource
DECLARE @lock_result int;
EXEC @lock_result = sp_getapplock @Resource = 'my_resource',
@LockMode = 'Exclusive',
@LockTimeout = 30000;...
The details have changed a bit over the years, and continue to be tweaked from time to time. A reasonable summary that answers the broad point in the question can be found in SQL Server, Lock Manager, and “relaxed” FIFO by Microsoft's Bob Ward.
Locks are granted in a relaxed first-in, first-out (FIFO) fashion. Although the order is not strict FIFO, it ...
You can enable log_lock_waits to get information about sessions that have to wait more than deadlock_timeout for a lock. You can reduce deadlock_timeout to see shorter waits.
Other than that, monitor the wait_event_type and wait_event in pg_stat_activity. If you see locks regularly, that can be a problem.
Yes, SELECT is a locking select in this case.
If you use SELECT in any query that changes data, it is implicitly a locking query as if you had used SELECT ... LOCK IN SHARE MODE. This puts a shared lock on the rows it reads.
Any other statement that changes data acts the same way. This includes cases like:
INSERT ... SELECT ...
CREATE TABLE ... AS SELECT ......
As David Browne mentions, this is the default behavior of the default isolation level in SQL Server. You can look into using alternative isolation levels such as READ_COMMITTED_SNAPSHOT.
Also to clarify, UPDATE operations don't lock the entire table necessarily, in the default isolation level. In your example, likely only a row level lock is occuring on the ...
If you're concerned about taking locks, consider using read committed snapshot isolation or snapshot isolation instead. These use row-versioning instead of shared locks when reading. If there are few changes to the database, there is little reason to prefer your solution.
Ultimately, if the database is completely unchanging, it could be set to read-only. In ...
I agree with Erik that you should check your indexes. However, I would also try this statement, which will result in one less trip to the table.
UPDATE AggregationMeasurement WITH (UPDLOCK, SERIALIZABLE)
SET ValueFloat = @ValueFloat
WHERE Date = @Date
AND AggregationConfigurationId = @AggregationConfigurationId;
IF @@ROWCOUNT = 0
The estimated subtree cost is a rough estimate of query cost, based on the query execution time on a particular computer 20 years ago. It's used by the cost based query optimizer to evaluate which query plan to choose.
It’s calibrated to account for locking cost, but probably is too rough an estimate to account for lock escalation.
CPU cost of actual query ...
The LCK_M_SCH_S was most likely related to the index/table from your ALTER INDEX and not to tempdb.
About the spike you see on the tempdb during the ALTER INDEX operation it might be caused by the SORT_IN_TEMPDB Option For Indexes:
When you create or rebuild an index, by setting the SORT_IN_TEMPDB
option to ON you can direct the SQL Server Database Engine ...
Unfortunately, there is no great alternative to just retrying in a loop. But you can perhaps make the retrying more clever. When I need to do this and can be in a transaction block, I take the lock explicitly, and use the NOWAIT option.
but still has performance implications, since every failed attempt to run the ALTER TABLE statement still blocks queries ...
If this happens often there is an option to write a package or a stored procedure for that. The owner of the package would need the alter system privilege and the package should be created with definers rights.
You just need execute privileges on that package and feed the session kill procedure with the right parameters.
There is still a good chance that ...
If the lock count heuristic is per-session then it is less of potential issue.
It is per-session.
Are there any other relevant database properties to consider with respect to lock escalation?
On the database, no, but you can disable lock escalation on the whole instance by enabling trace flag 1211.
what is the “best” way to prevent table lock escalation ...
I can't find any documentation on this
Docs are here. An S lock is compatible with a U lock, but the UPDATE will reuquire an X lock to actually complete, which is incompatible with an S lock.
and also don't know how to test this
You can always run a a query like
from sales.SalesOrderDetail with (tablock), master..spt_values v, master.....
I would go for backup and restore on the same instance, delete and clean up that restore database and then do a backup of that database. I don't know of a supported way to "lock" the database. One thing you could investigate is to configure snapshot replication and see (through a T-SQL trace) how the snapshot agent does exactly this. Possibly ...
To prevent deadlocks, you are going to want the right index on this table.
I suggest one of the following clustered indexes, if you can make it unique, even better.
The reason you need indexing is two-fold: otherwise the INSERT and NOT EXISTS will lock far too much, and ...
these tables have foreign keys on a highly referenced table and lock is required by drop
If FK constraints point to a table to be deleted, add CASCADE to also drop any such FK constraint (not the referencing tables). The manual:
(CASCADE will remove a dependent view entirely, but in the foreign-key case it will only remove the foreign-key constraint, not ...
In most cases, locks don't cause waits.
Imagine I have a simple banking system. In order to transfer money from account A to account B, I start a transaction and acquire a row-level lock on both account records. That lets me run my validations (i.e. A has enough money for the transfer, the fraud department doesn't have a flag on either account, etc.), ...
Here, my assumption is that each query may scan rows in a different order which implies locks on rows (resources) will be taken in different orders.
The scenario, as you've outlined it, is unlikely under normal circumstances. The exact same query being run concurrently will most likely use the same execution plan - which means they will both scan the index ...
I know that DB discovers that client is disconnected and will release locks eventually, but after what time? Which setting is that?
Table locks are associated with and held by the session that created the locks and will be released immediately when that session (i.e. connection) ends. The session could be terminated normally or unexpectedly, but the locks ...
I wonder what you are trying to do, but below are the parameter for MariaDB Deadlock setting :
Timeout in seconds an InnoDB transaction may wait for a lock before being rolled back. The value 100000000 is infinite timeout.
Timeout in seconds to wait for a lock before returning an error.
No, taking multiple locks is not atomic. Yes you can get a deadlock. It is easy to demonstrate this by reversing steps 1 and 2.
With the steps in the order you give, it would be hard to get the timing exactly right to hit it. But it is the same principle so it must also be possible.
You should use a sequence for this, and get the next value with NEXT VALUE FOR or sys.sp_sequence_get_range to reserve a range of values for a multi-row insert.
Sequences only provide a numeric value, but you can easily prefix the character code yourself.
An alternative would be an identity column with a computed column incorporating the prefix, but you have ...
You have a number of major flaws in your trigger:
It does not take into account there being multiple (or zero) rows in the inserted table.
XACT_ABORT is always ON in a trigger anyway, and I can't see a reason to try turn it off.
BEGIN TRAN \ COMMIT is also a waste of time because triggers run in the same transaction.
You need HOLDLOCK, UPDLOCK to keep the ...