New answers tagged

4

This is how Postgres was designed from ground up (as opposed to SQL Server where this was added very late) See the manual for details: http://www.postgresql.org/docs/current/static/mvcc-intro.html You can't actually configure Postgres so that readers would block writers (automatically or implicitly) The only way to block read access in Postgres is to ...


3

Chances are it isn't locking the "whole table". It is locking a row in the table but your SELECT * FROM LAYOUTSv2 tries to read the whole table so necessarily is blocked by that lock. For the insert case you can merely specify the READPAST hint to skip past the locked row- however that will not give your desired result for the UPDATE case (it will skip the ...


1

I want to increment a counter in the selected row, but only if the row's status column is ACTIVE. If it is not ACTIVE, then I just want to release the lock without an update so other threads/processes can read/write that row. Don't have the SELECT; simply do UPDATE tbl WHERE ... AND status = 'active' The UPDATE will either change the row or ...


1

Don't know about MySQL, but in Postgresql, the lock is only valid for the duration of the current transaction, so: you must have started a transaction the lock will be released if you commit or rollback the transaction If you don't start an explicit transaction, then the lock will be released right away.


1

Drastically reducing the number of rows will reduce the likelihood of getting the deadlock, but it won't go away completely. In simple terms the select is first using the index to determine the rows to select, then fetching the rows, while the insert is inserting a row, then trying to update the (XLOCKED) index. Application developers tend to use XLOCK ...


12

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


0

Check with the business team to see if you can restructure the business process so that you can start and comit a new transaction for each row in the excel file. If anything failed, record the fail rows so that they would know which row was successful and which one failed


3

If you are appropriately licensed (Advanced Workgroup or Advanced Enterprise edition -- or Performance Management Option, HT to @IanBjorhovde) you can use a DB2 Workload Manager threshold to accomplish something like that. First you create a service class for your procedure: CREATE SERVICE CLASS sc_report You then define a workload that will assign the ...


0

I'm curious why you only want one call at a time? If that is the case, perhaps this would be better off not called by users and scheduled to run. Because I don't think you can truly prevent a stored procedure from being called more than once. But you could set things up to make it more difficult. Off the top of my head, I think you could handle it with ...



Top 50 recent answers are included