48
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
30
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 could see a newer Identity value inserted by a concurrent transaction?
Yes.
The allocation of identity values is independent of the containing user transaction. ...
27
I would like to understand whether the following, very simple select statement would take any locks
It is a common misconception that a SELECT query running at the default READ COMMITTED transaction isolation level will always take shared locks to prevent dirty reads.
SQL Server can avoid taking shared row-level locks when there is no danger of reading ...
24
There is no need to drop and recreate the index.
Just use
ALTER TABLE dbo.production_data
ALTER COLUMN serial NVARCHAR(32) NOT NULL;
This is a metadata only change.
Altering a column from NVARCHAR(16) to NVARCHAR(32) does not affect the storage at all.
Going the other way round (from NVARCHAR(32) to NVARCHAR(16)) would give you an error about ...
24
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
ON blocking.session_id = blocked.blocking_session_id
INNER JOIN sys.dm_os_waiting_tasks waitstats
ON waitstats.session_id = blocked.session_id
...
21
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 statement with a data-modifying CTE (something that MySQL also doesn't have) because it's convenient to pass values from one table to the other directly (if you ...
21
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 qualifying rows are held until the transaction completes.
The first transaction holds an update lock on the row where name = 1. The second transaction is blocked ...
21
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, the other connection proceeds. In all cases, the column is soon decremented by 11.
A third user may see the value decremented by 0 or 4 or 7 or 11. The "very ...
19
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 is "true" for an ALTER TABLE, that almost certainly means that it's patiently waiting for the ACCESS EXCLUSIVE lock on its target table, and its real work (...
18
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 isolation level requests and use locks in the same way as user tables, by design.
The race condition in the original code is that multiple threads can conclude ...
17
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 incremented twice.
If you actually want to select the value for your PHP script, do something with it and later want to update this exact counter value, you can ...
17
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 first-in, first-out (FIFO) fashion. Although the order is not strict FIFO, it preserves desirable properties such as avoiding starvation and works to reduce ...
17
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 attempts to modify a row that has been modified by another transaction that committed after the snapshot transaction began.
The reason for the incorrect behaviour in ...
16
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.osuser,
b.machine
from
v$locked_object a ,
v$session b,
dba_objects c
where
b.sid = a.session_id
and
a.object_id = c.object_id;
Reference:-...
16
I recommend you read Understanding how SQL Server executes a query, it has an explanation of how reads and writes work and how locking works.
The 10000ft view goes as follows:
read operators acquire shared locks on the data they read, before reading the data
write operators acquire exclusive locks on the data they modify before modifying the data
data ...
16
...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) but one obvious deadlock scenario arises with multiple single-row updates within a single transaction, where there is an overlap of [id] values, and the ids ...
16
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 serializable transaction isolation. But that's more expensive and you need to prepare to repeat commands on serialization failure.
Your best course of action is probably ...
answered Jun 17 '14 at 12:12
Erwin Brandstetter
140k17 gold badges332 silver badges476 bronze badges
16
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 lookup in this case):
As the question notes, the general deadlock risk arises because the queries may obtain incompatible locks on the same resources in ...
16
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, if you execute this statement:
BEGIN TRANSACTION
INSERT INTO dbo.TestLock DEFAULT VALUES
And look at the SQL Text via the most recent sql handle, you'll see ...
15
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 locks
held by the query unless acknowledgement is received from client.
This is not accurate, it is dependent on the isolation level.
At the default READ ...
15
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?
Essentially, yes. The read operation (select) accesses the nonclustered index first, then the clustered index (lookup). The write operation (insert) accesses ...
15
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 rowcount = 0, and then they're both trying to insert as a result.
set transaction isolation level serializable;
begin transaction;
update dbo.table1
set value = ...
15
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 you can insert new rows without problems (assuming they do not violate any constraints).
Any row that is inserted after the UPDATE will not be seen by the ...
answered Sep 30 '19 at 12:39
a_horse_with_no_name
66.4k12 gold badges128 silver badges166 bronze badges
14
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 Locks, and
Page Locks are also escalated to Table Locks.
It is a myth that Row locks are escalated to Page locks, same mentioned above by @...
14
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 validating foreign key constraints. This is required for correctness, and cannot be disabled.
The behaviour applies only to data-modification statements. ...
14
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 procedure that provides information about processes and the locks they hold as well their active transactions. beta_lockinfo is designed to gather as much ...
13
when I explicitly lock a row or a table, am I employing the exact same constructs that are used by the database's transaction facilities under the covers to make the transaction work properly?
Yes. If that would not be true, then your own 'locking' would only be scoped to other similar 'locking' and not interact with the engine own locking. So you would ...
13
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 updating to release a locked record (surely two processes can't be fighting over that when it is not possible for two processes to have locked the same record ...
13
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';
Results:
LogDate ProcessInfo Text
----------------------- ------...
12
The default READ COMMITTED transaction isolation level guarantees that your transaction will not read uncommitted data. It does not guarantee that any data you read will remain the same if you read it again (repeatable reads) or that new data will not appear (phantoms).
These same considerations apply to multiple data accesses within the same statement.
Your ...
Only top voted, non community-wiki answers of a minimum length are eligible