127

Without concurrent write access Materialize a selection in a CTE 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 server_info s SET status = '...


38

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


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


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


22

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


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


14

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


13

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


13

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


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


12

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


11

I think the best approach for you would be to actually expose your module to high concurrency and see for yourself. Sometimes UPDLOCK alone is enough, and there is no need for HOLDLOCK. Sometimes sp_getapplock works out very well. I would not make any blanket statement here - sometimes adding one more index, trigger, or indexed view changes the outcome. We ...


11

I'm not looking for alternative ways to do the updates. Having things like a tmp table [will] lock all the rows that are being updated until they all finish (which could be hours), which won't work for me. They MUST be in these awful loops. I disagree. The strength of an RDBMS is in performing set operations like "update all these rows plz". Given ...


11

Just addressing the SERIALIZABLE isolation level aspect. Yes this will work but with deadlock risk. Two transactions will both be able to read the row concurrently. They will not block each other as they will either take an object S lock or index RangeS-S locks dependant on table structure and these locks are compatible. But they will block each other when ...


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

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


11

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


10

You may find this surprising, but you should set the innodb_thread_concurrency to 0 (which is infinite concurrency). This will allow the InnoDB Storage Engine to decide how many concurrency tickets to issue. I wrote a post about InnoDB's multicore engagement (MySQL 5.5, also MySQL 5.1.38 InnoDB Plugin) back on May 26, 2011. According to the MySQL ...


10

I do it all the time with my T-SQL modules. Essentially, all you need to do is run your modules from two or more connections in a loop for a couple of minutes. Typically, all potential problems are exposed in a few minutes, assuming you have a SQL Server box with decent CPUs. I wrote a few examples here and here.


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

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


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


10

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


9

In this particular case the addition of a UPDLOCK lock to the SELECT would indeed prevent anomalies. The addition of HOLDLOCK isn't necessary as an update lock is held for the duration of the transaction, but I confess to including it myself as a (possibly bad) habit in the past. Imagine doing something more legitimate than an ID update, some ...


9

I think it's fine in some circumstances, as long as you accept the consequences, and don't have other options. For other options, I'd push people towards using Read Committed Snapshot Isolation (RCSI) for new applications, or SNAPSHOT ISOLATION (SI) for older applications, where you can't easily test the entire code base for race conditions with RCSI. ...


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