49
votes
How to determine if an Oracle table is locked or not?
Following query gives details of all locks.
SELECT B.Owner, B.Object_Name, A.Oracle_Username, A.OS_User_Name
FROM V$Locked_Object A, All_Objects B
WHERE A.Object_ID = B.Object_ID
- 491
31
votes
Accepted
Can I rely on reading SQL Server Identity values in order?
When inserting a row, is there a window of opportunity between the generation of a new Identity value and the locking of the corresponding row key in the clustered index, where an external observer ...
- 69.2k
28
votes
Accepted
Locking in Postgres for UPDATE / INSERT combination
I have heard of concurrency problems like that in MySQL before. Not so in Postgres.
Built-in row-level locks in the default READ COMMITTED transaction isolation level are enough.
I suggest a single ...
- 150k
24
votes
Accepted
Detecting the locked table or row in SQL Server
I think this does what you need.
USE 'yourDB'
GO
SELECT
OBJECT_NAME(p.[object_id]) BlockedObject
FROM sys.dm_exec_connections AS blocking
INNER JOIN sys.dm_exec_requests blocked
...
- 5,635
22
votes
Accepted
Why does UPDLOCK cause SELECTs to hang (lock)?
As documented in Books Online, UPDLOCK takes update locks and holds them to the end of the transaction.
Without an index to locate the row(s) to be locked, all tested rows are locked, and locks on ...
- 69.2k
22
votes
Accepted
MySQL: will a transaction lock the row?
A single statement like that works the same with MyISAM or InnoDB, with a transaction or with autocommit=ON. It blocks enough to do the query, thereby blocking the other connection. When finished, ...
- 68.1k
20
votes
Accepted
Is it safe to cancel a PostgreSQL ALTER TABLE query that is waiting on a lock?
As far as I understand, the fact that our query is waiting for a lock means it has always been waiting for a lock, and it has never changed anything.
Right -- if you see that pg_stat_activity.waiting ...
- 2,206
18
votes
Accepted
Lock CREATE TABLE
You best bet is to use an explicit containing transaction and acquire a custom exclusive lock to protect the whole operation (SELECT and CREATE TABLE) using sp_getapplock. System objects do not honor ...
- 69.2k
17
votes
How to determine if an Oracle table is locked or not?
The following script can be used in order quickly identify all lock objects within your Oracle system.
select
c.owner,
c.object_name,
c.object_type,
b.sid,
b.serial#,
b.status,
b....
- 271
17
votes
Accepted
How to query-and-increase a value (counter) in a thread-safe way? (avoid race conditions)
The update statements works perfectly fine without the select before! Since single statements are safe by definition, even two UPDATE queries performed at the same time only will result in the row ...
- 2,614
17
votes
Accepted
Sch-M WAIT blocks Sch-S in SQL Server 2014 but not SQL Server 2008 R2?
The behaviour did change between SQL Server 2008 R2 and SQL Server 2012. The 2008 R2 implementation was inconsistent with the documented 'relaxed FIFO' semantics:
Locks are granted in a relaxed ...
- 69.2k
17
votes
Accepted
Unclear update conflict
Why do I get update conflict in this situation instead of just blocking
It is a product defect, which is fixed in SQL Server 2019.
A snapshot write conflict occurs when a snapshot transaction ...
- 69.2k
16
votes
Accepted
Deadlock when update different rows with non-clustered index
...why with clustered index, the deadlock is still there (though hit rate seems to be dropped)
The question isn't precisely clear (e.g. how many updates and to which id values are in each transaction)...
- 69.2k
16
votes
Accepted
Optimizing concurrent updates in Postgres
There is no ORDER BY in an SQL UPDATE command. Postgres updates rows in arbitrary order:
UPDATE with ORDER BY
To avoid deadlocks with absolute certainty, you could run your statements in ...
- 150k
16
votes
Accepted
SQL Server Index Update Deadlock
Is there a way to prevent the deadlock while maintaining the same queries?
The deadlock graph shows that this particular deadlock was a conversion deadlock associated with a bookmark lookup (an RID ...
- 69.2k
16
votes
Accepted
How to find the query that is still holding a lock?
SQL Server doesn't keep a history of the commands that have been executed1,2. You can determine what objects have locks, but you cannot necessarily see what statement caused those locks.
For example,...
- 67k
15
votes
What is lock escalation?
SQL Server does Lock Escalation in order to reduce memory overhead, by converting several fine-grained low level locks to coarse-grained high level locks.
Row Locks are always escalated to Table ...
- 328
15
votes
Accepted
Will increased network latency cause table locks in MS SQL Server?
If client takes long time to receive data and in turn send
acknowledgement to SQL Server that it has received the data SQL Server
has to wait, due to this wait SQL Server will not release the ...
- 31.2k
15
votes
Accepted
View locks acquired during query execution (SQL Server)
is it possible to view the locks, along with the type, acquired during the execution of a query?
Yes, for determining locks,
You can use beta_lockinfo by Erland Sommarskog
beta_lockinfo is a stored ...
- 61.1k
15
votes
Accepted
Good reasons to use SELECT... WITH XLOCK?
As far I understand this, I am looking at a KEYLOCK deadlock basically caused by an uncovered index query that uses a nonclustered and a clustered index in order to collect the required values, right?
...
- 69.2k
15
votes
Accepted
Checking @@ROWCOUNT failing
I'm not sure why you're using a variable, but you need to protect multiple statements with a transaction. What's happening is two users are calling the procedure at the same time, both are getting ...
- 176k
15
votes
Accepted
Does UPDATE without WHERE clause lock a table in PostgreSQL?
An UPDATE without a WHERE clause will lock all rows in the table, but will not lock the table itself for DML.
The rows can not be deleted from a different transaction because they are locked.
But ...
- 70.5k
14
votes
Shared locks taken under Read Committed Snapshot Isolation (RCSI)
How can I be seeing shared locks? Is it because of foreign keys?
Yes. SQL Server reverts to the locking implementation of the read committed isolation level when accessing a table for the purpose of ...
- 69.2k
13
votes
Accepted
Strategies for "checking out" records for processing
I am not a huge fan of either the extra "lock" table or the idea of locking the whole table to grab the next record. I get why it is being done, but that also hurts concurrency for operations that are ...
- 65.6k
13
votes
Accepted
MySQL: Safe to kill transaction that is stuck in "Waiting for table metadata lock"
Yes, it's safe. Your alter table query hasn't even started.
However, the waiting for table metadata lock means, that there's another transaction operating on that table for a long time and you should ...
- 3,028
13
votes
Accepted
How can I set a column to NOT NULL without locking the table during a table scan?
Another answer from another postgresql contributor.
PostgreSQL will not even try to use any indexes during execution of "alter table set not null". It is just not implemented.
Proper implementation ...
- 1,711
13
votes
How to know which application or user put the SQL Server Database in single user mode
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 ...
- 176k
12
votes
Lock CREATE TABLE
My recommendation would be to do a best effort try/catch. Handle the duplicate case explicitly, as appropriate, eg. ignore it...
The real question: why is DDL running on-demand, from multiple xacts? ...
- 50.8k
12
votes
Accepted
Implementing application locks within SQL Server (Distributed Locking Pattern)
Do I need to make the "unlock" SERIALIZABLE as well?
Despite the name, the serializable isolation level does not guarantee transactions will be executed sequentially, or in the order received. Rather,...
- 69.2k
12
votes
Accepted
When is it safe to disable InnoDB doublewrite buffering?
The only situation I can think of is reloading a large mysqldump. Why ?
Check out this Pictorial Representation of InnoDB (Percona CTO Vadim Tkachenko)
From the picture, you can see that the InnoDB ...
- 173k
Only top scored, non community-wiki answers of a minimum length are eligible