Tag Info

Hot answers tagged

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

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


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


8

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


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

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


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


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

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


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

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

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

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


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

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

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

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


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

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


3

This process will be a bit cumbersome for the DB. Not only do you want to know if the current record is being viewed, but you also want to notify the users of each other. When a user reads a record, the DB system quickly fetches the data and returns it to the user. As far as the DB is concerned, once the data is sent to the client, nobody is 'viewing' said ...


3

Deadlocks in Oracle with logically disjoint transactions usually involve unindexed foreign keys: There are two issues associated with unindexed foreign keys. The first is the fact that a table lock will result if you update the parent records primary key (very very unusual) or if you delete the parent record and the child's foreign key is not indexed. ...


3

From Oracle 11gR1 you can use the WAIT instruction in DDL: lock table t in exclusive mode wait 10; This will wait 10 seconds before giving up. The WAIT instruction has been available for a long time (9i) for DML: SELECT * FROM t FOR UPDATE WAIT 10;


3

I usually works with the command line tool of the RDBMS, just having 2 (or more) instances of the CLI started. You can then replay one by one and as a race (that would look like an action-RPG) the SQL statements your application layer is sending. You should experiment/feel the locking systems in action as your CLI will "hang" a bit, waiting the locks to be ...


3

You sort of answer your own question when you say you have no pooling but... This is not an answer out of the box, with all client/db stuff you may need to do some work to determine exactly what is amiss backup postgresql.conf changing log_min_duration_statement to 0 log_destination = 'csvlog' # Valid values are combinations of ...


3

Assuming you can isolate the problem to the generation of identity values (try removing that column as a test), what I would recommend is this: Remove the IDENTITY property from the column in the final table. Generate identity values in each of the #Temporary tables. When loading the final table, combine a numeric identifier for the particular store with ...



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