Tag Info

Hot answers tagged

11

Note that if your table is using the InnoDB Plugin storage engine (which I highly recommend), if it's a secondary index (almost surely the case), you can still read from the table (i.e. it's non-blocking to SELECT statements).


11

Yes you can. It will lock the table you're adding an index to while it's being created. If the table is large, it may take awhile as it has to read each row while building the index.


10

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


10

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


9

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


9

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


9

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


8

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


7

Best book on the subject, and the most complete resource, is Kalen Delaney's SQL Server 2008 Internals. You really can't do any better. Another good book on the subject is Chris Bolton's SQL Server 2008 Internals and Troubleshooting. I don't think it's as complete as Kalen's book, but it does cover things pretty well, especially around locking and ...


7

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


7

Regarding the SERIALIZABLE transactions, be sure to pay attention to the "for optimal performance" tips near the bottom of this section: http://www.postgresql.org/docs/9.1/interactive/transaction-iso.html#XACT-SERIALIZABLE These can reduce your rollback rates quite significantly. That said, and not to discourage use of the SERIALIZABLE transaction ...


7

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


7

Without concurrency Use a subquery in the FROM clause of the UPDATE: UPDATE server_info s SET status = 'active' FROM ( SELECT server_ip -- your pk column or any (set of) unique column(s) FROM server_info WHERE status = 'standby' LIMIT 1 -- arbitrary pick (cheapest) ) sub WHERE s.server_ip = sub.server_ip ...


6

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


6

Transaction Processing: Concepts and Techniques. Readings in Database Systems Principles of Transaction Processing While product specific books do a great job at explaining how to use the products (and Kalen Delaney's series is awesome!), they don't really stand a chance against the 'black book' and the 'red book' of the database systems. Specially the ...


6

No there's no way of configuring SQL Server to do what you want to do. Under snapshot isolation the call to sp_tables gets blocked waiting for a shared key lock on one of the system base tables (sysschobjs) when doing a SELECT from sys.all_objects The Using Row Versioning-based Isolation Levels topic in BOL does say: SQL Server does not keep multiple ...


6

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


6

If eventual consistency is acceptable and all your queries are aggregates then perhaps a low-latency OLAP system might work for you. Your requirement sounds a bit like an algorithmic trading platform. This type of architecture is often used in trading floor systems that have a requirement to carry out aggregate statistical analysis computations on up to ...


5

If you do not want duplicates on the 'othervalue' column, you can do so by creating a unique constraint on that column. The query would be: ALTER TABLE table1 ADD CONSTRAINT unique_c_othervalue UNIQUE(othervalue) This would throw back an error if a query tried to insert a duplicate value into the 'othervalue' column.


5

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.


5

You are looking for WAL Mode. WAL provides more concurrency as readers do not block writers and a writer does not block readers. Reading and writing can proceed concurrently.


5

Locking and concurrency is a topic I learnt most about through testing and observation. Start by reading Isolation Levels in the Database Engine and Concurrency Effects, so you have an understanding of the relationship between the two. Now you can experiment: Construct the SQL for a test transaction. Something simple and self-contained with a ...


5

Assuming you are licensed to use the AWR, you could query either the gv$active_session_history view for more recent data or dba_hist_active_sess_history for older data. gv$active_session_history will have a snapshot of what every active session was doing at the top of each second and will hold that data for a few hours. dba_hist_active_sess_history will ...


5

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


4

I may be wrong, but I don't think this is possible unless you restrict access to the data to be via stored procedures - then the procedure can trigger audit mechanism that you can expose to the users. You would also need polling of the audit if both users need to be aware.


4

This depends on table engine? Yup. MyISAM will lock the table, InnoDB will lock rows If you're looking for ACIDity, you'll want to be using InnoDB. How InnoDB handles locking is described in this doc page The following user comments on the above document page illustrate a flaw in InnoDB performance on large tables: Major gotcha: Rows are locked ...


4

One other resource is Kendra Little's clever poster on isolation levels. There are also links to her presentation, resources and some discussion.


4

The IDENTITY generator is not well documented. There are some behaviors however that can be observed that seem relevant: The identity generation does not get affected by transactions. That means once a value has been used it will not be reused, even if the transaction causing its use is rolled back. Not every use causes an update of the sequence position ...


4

Firstly the batch order should be validated first and then committed. The transaction that commits the inserts into the database should as you say be as short as possible. In the perfect world this would mean that you do all the validation up front before beginning the transaction and committing. From what you have said I doubt such a restructure is on ...


4

When you issue an ALTER TRIGGER statement, that process will attempt to acquire a SCH-M (schema modification) lock on the table object as well as the trigger object. This is an extremely low-concurrency lock and will cause the expected blocking that you're running into. See this reference for a chart on lock compatibility. For referencing sake, here is ...



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