Tag Info

New answers tagged

1

Summarizing other approaches originally offered in comments to the question. Use NOWAIT if the desired behaviour is to fail the whole chunk as soon as an incompatible lock is encountered. From the NOWAIT documentation: Instructs the Database Engine to return a message as soon as a lock is encountered on the table. NOWAIT is equivalent to specifying ...


1

You may do deletes in batches from some script: 1) mark account for deletion (seems you are doing it right now) 2) in script - select one user for some marked account and delete it - as you expect many users per account, this will delete only small portion of all messages and attachments so should be fast enough 3) repeat 2 with remaining users of a given ...


7

If I understand the request correctly, the goal is to delete batches of rows, while at the same time, DML operations are occurring on rows throughout the table. The goal is to delete a batch; however, if any underlying rows contained within the range defined by said batch are locked, then we must skip that batch and move to the next batch. We must then ...


-1

Try filtering on something like this - it can get complicated if you want to get really, really specific. Look in BOL for the description of sys.dm_tran_locks SELECT tl.request_session_id, tl.resource_type, tl.resource_associated_entity_id, db_name(tl.resource_database_id) 'Database', CASE WHEN tl.resource_type = 'object' THEN ...


10

So, we are changing the approach to delete small batch of rows at at time. This is a really good idea to delete in small careful batches or chunks. I would add a small waitfor delay '00:00:05' and depending on the recovery model of the database - if FULL, then do a log backup and if SIMPLE then do a manual CHECKPOINT to avoid bloating of transaction ...


-10

You can use NoLOCK while you are deleting and if the rows are locked, they wont be deleted. Its not ideal but may do the trick for you. DELETE TA FROM dbo.TableA TA WITH (NOLOCK) WHERE Condition = True


1

In addition to someone doing full table locks, there are two other things that I can think of. If you have a table where there are several columns that have a parent child relationship to the same column in a child table and if the child column is not indexed, then indexing it could help. I had a case with an online content management system that had ...


2

You can use Adam Machanic's SP_WHOISACTIVE to get the details tracked. One of the great things about sp_whoisactive is that it’s very quick to generate a table to collect the data you need, then run the procedure in a loop with a wait statement to regularly collect the data you want over an interval. This usage is documented. Please see on how to collect ...


4

SQLState: 55P03 In Postgres 9.4.x I tested this by performing an unresolved SELECT FOR UPDATE in pgAdmin and then doing a SELECT FOR UPDATE NOWAIT in my Java app. I used the JDBC driver JDBC41 Postgresql Driver, Version 9.4-1201. The result was the following PostgreSQL Error Code. Message:ERROR: could not obtain lock on row in relation "my_table_" ...


1

Shared database locks are normal, simply meaning a connection is open in that database context. That lock prevents another session from performing an operation like dropping the database while it's being used. A session AWAITING_COMMAND that is blocking other sessions is an indicator the session still has an open transaction holding a lock on a resource ...


1

some reasons can be app waiting for user inputs,time outs with out proper exceptional handling. you can easily simulate this by Begin tran Update authors …. Waitfor delay ’10:00:00’ --- time out will occur here (simulates long workload) rollback For second question on Slocks,These mean shared locks which ...



Top 50 recent answers are included