Tag Info

New answers tagged

1

I'll repost my response on twitter here: Based on the deadlock XML you posted, which lists a trancount of 3 in the session that invokes spUpdateUserAccount, a prior statement or batch in that transaction likely put the lock on IX_Person_TaxPreparer_rowCreated. I'm presuming this lock is held because of the foreign key constraint in place. The locks are ...


0

Using the below query you can find out locks on the table. column oracle_username format a15; column os_user_name format a15; column object_name format a37; column object_type format a37; select a.session_id,a.oracle_username, a.os_user_name, b.owner "OBJECT OWNER", b.object_name,b.object_type,a.locked_mode from (select object_id, SESSION_ID, ...


1

The underlying OS is capable of detecting all the above mentioned possible disconnects and break the communication channel (TCP, net pipe etc). This will result in the rollback. Don't try to outsmart this. Of course, one could ask why not use a reliable queueing communication channel (eg. MSMQ) submit the entire order in one call rather than 100 calls ...


0

The following script can be used in order quickly identify all lock objects within your Oracle system. select c.owner, c.object_name, c.object_type, b.sid, b.serial#, b.status, b.osuser, b.machine from v$locked_object a , v$session b, dba_objects c where b.sid = a.session_id and a.object_id = c.object_id; ...


0

As I dug deeper into the matter at hand trying various approaches I found out that there is no solution to this problem with PostgreSQL versions <= 9.4. PostgreSQL 9.5 (current development version) has a new feature called SKIP LOCKED which does skip rows that have been locked by a query using SELECT * ... FOR UPDATE For more information on the new ...


0

You mentioned you could not use 'locking by deletion' because the row must stay in the table. The code example below does use it, but only after creating a global queue first and populating it. It then uses the 'locking by deletion' for the ##Queue table rather than the table you're working with. USE master GO -- 1. Create a Stored Procedure that ...


0

The most likely explanation for this is that an intermediate piece of network hardware is tearing down or "forgetting" your idle TCP connection, assuming your wait_timeout system variable is not set too low. Stateful firewalls maintain internal data structures of all current "flows" -- source/destination address/port quads -- and if a flow has no traffic ...


0

Yes, iterate over batches. This blog provides some suggestions on how to do it. It is couched in how to chunk up big DELETEs, but the principles work for big UPDATEs, like yours. http://mysql.rjweb.org/doc.php/deletebig It shows you how to walk through using (typically) the PRIMARY KEY and use LIMIT to decide on the next chunk. No step has to scan the ...


2

To elaborate on Josh's correct and excellent answer: Is it safe for us to outright cancel our ALTER TABLE query? Yes. It would be safe even if it was in the middle of rewriting the table. If you wanted to you could just shut down the whole PostgreSQL server, or in fact the machine it runs on, restart it, and everything would be fine. DDL in ...


4

As far as I understand, the fact that our query is waiting for a lock means it has always been waiting for a lock, and it has never changed anything. Right -- if you see that pg_stat_activity.waiting is "true" for an ALTER TABLE, that almost certainly means that it's patiently waiting for the ACCESS EXCLUSIVE lock on its target table, and its real work ...



Top 50 recent answers are included