79
Use this one command:
RENAME TABLE foo TO foo_old, foo_new To foo;
It is an atomic operation: both tables are locked together (and for a very short time), so any access occurs either before or after the RENAME.
39
Mat and Erwin are both right, and I'm only adding another answer to further expand on what they said in a way which won't fit in a comment. Since their answers don't seem to satisfy everyone, and there was a suggestion that PostgreSQL developers should be consulted, and I am one, I will elaborate.
The important point here is that under the SQL standard, ...
33
It isn't always bad.
Of course it allows you to read uncommitted values (that may be rolled back and hence never logically existed) as well as allowing phenomena such as reading values multiple times or not at all.
The only isolation levels that guarantee that you won't encounter any such anomalies are serializable/snapshot. Under repeatable read values ...
32
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
If your report blocks updates that your DBA is right: you should absolutely not use NOLOCK. The very fact that there are conflicts is a clear indication that if you would use dirty reads you would get incorrect reports.
In my opinion, there are always better alternatives than NOLOCK:
Are your production tables read only in effect and never get modified? ...
26
What you want is SELECT ... FOR UPDATE from within the context of a transaction. SELECT FOR UPDATE puts an exclusive lock on the rows selected, just as if you were executing UPDATE. It also implicitly runs in READ COMMITTED isolation level regardless of what the isolation level is explicitly set to. Just be aware that SELECT ... FOR UPDATE is very bad for ...
26
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. ...
answered Apr 13 '16 at 1:37
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 ...
23
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 ...
answered Sep 17 '13 at 4:33
23
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 believe this is by design, according to the description of the read-committed isolation level for PostgreSQL 9.2:
UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands behave the same as SELECT in terms of searching for target rows: they will only find target rows that were committed as of the command start time1. However, such a target row ...
20
Extending Mark's answer...
When a client timeout event occurs (.net CommandTimeout for example), the client sends an "ABORT" to SQL Server. SQL Server then simply abandons the query processing. No transaction is rolled back, no locks are released.
Now, the connection is returned to the connection pool, so it isn't closed on SQL Server. If this ever happens ...
19
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 ...
answered Sep 9 '15 at 13:35
18
The lock_time in the slow query log is actually the amount of time the query spent waiting to acquire the lock it needs to run. For example, UPDATES queries need a write lock.
The locking also depends on the storage engine you are using in the table. When writing, InnoDB will use row-level locking and only lock the rows that are being changed. MyISAM will ...
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 ...
answered Mar 10 '14 at 18:01
17
To answer that I have to take a little detour, so bear with me.
If two sessions take a lock on the same resource SQL Server checks the lock compatibility map and if the second request is not "compatible" with the first, the second session has to wait. There are three lock types "S"hared, "U"pdate and e"X"clusive. S locks are taken to read from a resource ...
16
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 ...
answered Aug 13 '14 at 5:54
15
Your developer is mistaken. You need either SELECT ... FOR UPDATE or row versioning, not both.
Try it and see. Open three MySQL sessions (A), (B) and (C) to the same database.
In (C) issue:
CREATE TABLE test(
id integer PRIMARY KEY,
data varchar(255) not null,
version integer not null
);
INSERT INTO test(id,data,version) VALUES (1,'fred',0);
...
15
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 ...
15
...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 ...
answered Apr 11 '14 at 11:58
15
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
107k11 gold badges229 silver badges358 bronze badges
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 ...
answered Jan 21 '16 at 16:36
15
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 ...
15
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
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 ...
14
Executing the query "" failed with the following error: "The index ""
(partition 1) on table "" cannot be reorganized because page level
locking is disabled."
The maintenance plan must be attempting an ALTER INDEX REORGANIZE, which is an online operation. To remove fragmentation (pages not in order), pages must be locked and moved, which is not possible ...
14
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 ...
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
What is HOBT lock?
A lock protecting a B-tree (index) or the heap data pages in a table that does not have a clustered index.
Why would I still get a S lock?
This happens on heaps. Example
SET NOCOUNT ON;
DECLARE @Query nvarchar(max) =
N'DECLARE @C INT;
SELECT @C = COUNT(*) FROM master.dbo.MSreplication_options';
/*Run once so compilation ...
Only top voted, non community-wiki answers of a minimum length are eligible
