149

Without concurrent write access Materialize a selection in a CTE (Common Table Expressions) and join to it in the FROM clause of the UPDATE. WITH cte AS ( SELECT server_ip -- pk column or any (set of) unique column(s) FROM server_info WHERE status = 'standby' LIMIT 1 -- arbitrary pick (cheapest) ) UPDATE ...


42

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, ...


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. ...


22

Yes, SQL Server can, under some circumstances read one column's value from the "old" version of the row, and another column's value from the "new" version of the row. Setup: CREATE TABLE Person ( Id INT PRIMARY KEY, Name VARCHAR(100), Surname VARCHAR(100) ); CREATE INDEX ix_Name ON Person(Name); CREATE INDEX ix_Surname ON ...


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

I use READ_UNCOMMITTED (or NOLOCK) when querying production databases from SSMS but not routinely from application code. This practice (along with a MAXDOP 1 query hint) helps ensure casual queries for data analysis and troubleshooting don't impact the production workload, with the understanding the results might not be correct. Sadly, I see ...


19

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

If I run them serially, one right after the other, I'm expecting it will require 7 minutes to complete on average. Is this reasonable? If they use unrelated data sets, then yes. If they share a data set, and the cache is cold for the first query and the query is mostly I/O bound, then the second one might complete in moments. You need to consider caching ...


15

I think I probably meant to add that comment on the prior answer, about two separate statements. It was over a year ago, so I'm not totally sure anymore. The wCTE based query doesn't really solve the problem it's supposed to, but upon reviewing it again over a year later I don't see the possibility of lost updates in the wCTE version. (Note that all of ...


14

It's referring to Azure SQL Database which uses RCSI by default. Isolation Level SQL Database default database wide setting is to enable read committed snapshot isolation (RCSI) by having both the READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION database options set to ON, learn more about isolation levels here. You cannot change the database default ...


13

The only query in what you're showing above appears to be this one, repeated a few times: IF EXISTS ( select * from [dbo].[FinanceDetail] trd WITH (UPDLOCK, SERIALIZABLE) where trd.HeaderId = @HeaderId ) DELETE from dbo.FinanceDetail where HeaderId = @...


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 ...


12

Local temporary objects are separated by Session. If you have two queries running concurrently, then they are clearly two completely separate sessions and you have nothing to worry about. The Login doesn't matter. And if you are using Connection Pooling that also won't matter. Local temporary objects (Tables most often, but also Stored Procedures) are safe ...


12

Would the identity primary key column fail, with a primary key violation in any way, example: processors are trying to input the same identity number? This is covered in the SQL Server product documentation: CREATE TABLE (Transact-SQL) IDENTITY (Property) Identity columns can be used for generating key values. The identity property on a column ...


12

My first question is, will these update statements work as intended? Very likely, but not certain. SQL Server guarantees it will honour the semantics of the query, and the level of ACID compliance determined by the effective isolation level. Beyond that, all is implementation detail (including what type(s) of locks are taken, when, and for how long they ...


12

I have a question regarding inner workings of UPDATE statements with regards to SQL Server. The statement describes a desired logical change to the database. What happens physically at runtime depends on the execution plan, the state of the database, and what other concurrently running statements/queries are doing. I mention this because your question ...


11

I completely agree with @Mat's excellent answer. I only write another answer, because it wouldn't fit into a comment. In reply to your comment: The DELETE in S2 is already hooked on a particular row version. Since this is killed by S1 in the meantime, S2 considers itself successful. Though not obvious from a quick glance, the series of events virtually is ...


11

Short Answer The update lock is sufficient, but you can achieve what you want more simply with: UPDATE dbo.tblOrderNumber WITH (SERIALIZABLE) SET @NextOrderNumber = NextOrderNumber, NextOrderNumber = NextOrderNumber + 1 WHERE CustomerID = @CustomerID; The WITH (SERIALIZABLE) hint is not strictly required if there is a unique index on CustomerID. Longer ...


11

Typically, you have a team table (or similar) with a unique team_id column. Your FK constraint indicates as much: ... REFERENCES teams(id) - so I'll work with teams(id). Then, to avoid complications (race conditions or deadlocks) under concurrent write load, it's typically simplest and cheapest to take a write lock on the parent row in team and then, in the ...


10

The rows that qualify for an update are always stabilized during discovery by using (at least) U locks (Update locks, see Lock Compatibility). When is time to actually update them, the U lock is upgraded to X lock. Because of this stabilization the row cannot disappear nor can it be modified between the discovery and the update. Nor can a second UPDATE ...


10

If you really need to run multiple threads at the same time you can enable the ignore_dup_key option on the primary key. This will just give a warning instead of an error when an insert would result in a duplicate key violation. But instead of failing it will discard the row(s) that if inserted would cause the uniqueness violation. CREATE TABLE [dbo].[...


10

With plain CREATE INDEX, the table will be locked for writes but not reads. Use CREATE INDEX CONCURRENTLY to avoid write locks as well. From the PostgreSQL docs on CREATE INDEX: When this option is used, PostgreSQL will build the index without taking any locks that prevent concurrent inserts, updates, or deletes on the table; whereas a standard ...


10

SQL Server by default operates under Read Committed isolation. If you want SQL Server to behave more like other implementations, what you're looking for is either Read Committed Snapshot Isolation, or Snapshot Isolation. You can read more about them here. Full disclosure, I am a Brent Ozar Unlimited employee Using NOLOCK or Read Uncommitted is ...


9

The UPDLOCK, SERIALIZABLE pattern is all about avoiding incorrect results (including false key violation errors) due to race conditions, when performing a particularly common operation known as an UPSERT - update an existing row if it exists; insert a new row otherwise. ...while avoiding racing conditions, deadlocks, etc? You seem to be looking for a ...


8

You could try DROP INDEX [ CONCURRENTLY ] name CONCURRENTLY Drop the index without locking out concurrent selects, inserts, updates, and deletes on the index's table. A normal DROP INDEX acquires exclusive lock on the table, blocking other accesses until the index drop can be completed. With this option, the command instead waits until conflicting ...


8

As Paul White answered absolutely correct there is a possibility for temporarily "skipped" identity rows. Here is just a small piece of code to reproduce this case for your own. Create a database and a testtable: create database IdentityTest go use IdentityTest go create table dbo.IdentityTest (ID int identity, c1 char(10)) create clustered index ...


8

Many of the issues you see are being caused by an inefficient execution plan: Not that the supplied plan and query matches the question, but even so, I'm working with what was provided. Anyway, you should implement the Name column data type changes (from nvarchar(max)) that I mentioned in your previous question. More importantly, you need to add the ...


8

Since we've established that no other transaction holds any of the locks the current one holds, it logically follows that no other transaction would be attempting to update or lock any of the records with those same object ids in the personal inventory table. You should know that this: no other transaction holds any of the locks the current one ...


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