Hot answers tagged

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
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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, ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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....
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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)...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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,...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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? ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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? ...
user avatar
  • 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,...
user avatar
  • 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 ...
user avatar

Only top scored, non community-wiki answers of a minimum length are eligible