88
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.
41
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, ...
37
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
28
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. ...
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
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 ...
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 ...
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 ...
18
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);
...
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
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 ...
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
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 ...
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
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 ...
15
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 ...
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
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
116k16 gold badges260 silver badges397 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 ...
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 ...
answered Sep 30 '19 at 12:39
a_horse_with_no_name
53.9k10 gold badges106 silver badges136 bronze badges
14
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:-...
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
You can't really list all rows that are being locked by a session. However, once a session is being blocked by another, you can find which session/row is blocking it.
Oracle doesn't maintain a list of individual row locks. Rather, locks are registered directly inside the rows themselves -- think of it as an extra column.
You can find which session has ...
13
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. ...
13
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 (...
13
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 =...
Only top voted, non community-wiki answers of a minimum length are eligible
