7
votes
1answer
159 views

Deadlock when update different rows with non-clustered index

I'm solving a deadlocking issue while I noticed the lock behavior is different when I use clustered and non-clustered index on the id field. The deadlock issue seems to be solved if clusted index or ...
0
votes
1answer
75 views

Reporting Services TempDB lock blocking report generation

Two days ago one of our reports in Reporting Services started to take much longer to generate. Its generation time had increased from 4 seconds to 2 minutes. Investigation found: The Stored ...
14
votes
2answers
488 views

Lock CREATE TABLE

In another application I was struck by bad design: multiple threads execute an EnsureDatabaseSchemaExists() method concurrently, which looks basically like this: IF NOT EXISTS (SELECT * FROM ...
2
votes
0answers
83 views

SQL Server - Same query, same database, different results [closed]

I have a database in SQL Server 2008 with 2 tables. I'm running the following query on Management Studio: select M.FD_BROWSER_SHORT_NAME AS X, Count(1) AS Y from TB_EVENTS E INNER JOIN ...
1
vote
1answer
119 views

DB Locks - How would one handle long transactions and maintain high concurrency?

We're maintaining a POS/WMS Application built in powerbuilder that runs on SQL Server 2008 R2 backend. Recently, we've been having a lot of locking issues and I believe it has to do with how we handle ...
4
votes
2answers
203 views

In SQL Server, how do read locks work?

Suppose I have the following long-running query UPDATE [Table1] SET [Col1] = 'some value' WHERE [Col2] -- some clause which selects thousands of rows and suppose the following query is executed ...
3
votes
1answer
78 views

When changing the size of a nvarchar column, do I need to drop the unique index? And will the table get locked when recreating the index?

In our database a big table exists that more or less looks like this: CREATE TABLE dbo.production_data ( pd_id BIGINT PRIMARY KEY, serial NVARCHAR(16) NOT NULL UNIQUE, ... ); but now ...
2
votes
1answer
52 views

What is happening with locks in my database?

I'm having some problems on my database. At any given time, I'm trying to execute an update with five records on my table, but this table is locked and I get a time out executing the query. I ...
2
votes
1answer
84 views

How to use proper hints for Upsert involving a top 1 subquery

For this specific case (tracking Load Balancer switches) we wish to optimize an Upsert so that it does not exhibit any race conditions, cause any PK violation, or acquire any over sized locks. ...
1
vote
1answer
50 views

How, when and why does implicit changing of transaction isolation level happen?

RCSI (Read Committed Snapshot Isolation) is the setting of a database changing the behavior of (the default) Read Committed isolation level in SQL Server. This is, as I understand, the context of ...
1
vote
1answer
59 views

Can I limit the time a table lock is being held server side?

I have a SQL Azure server with some stored procedures with TABLOCKX hints that I call from C# code on a Windows Azure web role VM. I'm kinda paranoid about those hints - what if there's some network ...
0
votes
1answer
72 views

Can we set LOCK_TIMEOUT instance- or database-wide?

It is possible to change the SQL Server's default (which is -1 or indefinite period of time) LOCK_TIMEOUT value session-wide. Is it possible to set LOCK_TIMEOUT default value server-, instance- ...
0
votes
1answer
371 views

Row locking within stored procedure [duplicate]

I have a SQL Server Stored Procedure for getting the next number in sequence stored in a configuration table: declare @val as int SELECT @val = LongValue from tblSystem WHERE [Key] = @Key; SET @val ...
0
votes
2answers
94 views

Does ROLLBACK TRAN honor LOCK_TIMEOUT value?

I have a following stored procedure on my system. BEGIN TRY BEGIN TRAN SET LOCK_TIMEOUT 10000 UPDATE tbl COMMIT TRAN END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRAN END CATCH ...
1
vote
0answers
191 views

Unable to insert into or update table

A have the following table in a SQL Server 2000 database (service pack 2), with about 600,000 rows: Id [int, primary key] Form [int] Element [int] Data [char(8000)] MoreData [varchar(8000)] ...
1
vote
0answers
75 views

UPLOCK to avoid multiple reads

Given the following table structure... Orders ====== OrderId int PK OrderEvent =========== OrderEventId int PK OrderId int FK EventTypeId int FK I've got multiple concurrent processes SELECT-ing ...
4
votes
1answer
211 views

Locking in “Read Committed Snapshot”

If an update is run on a table with "Read Committed Snapshot" isolation level and a commit is pending eg: update table1 set col1 = col1 + 1 where PKcol < 3 update table1 set col1 = col1 + 1 ...
1
vote
2answers
71 views

Read committed snapshot:Finding lock on table

IF update command is run on a table and commit is pending then another user cannot be able to update the table. Is there any query to get the user or PC which caused lock on the table.
2
votes
2answers
69 views

Lock hints on preconditions

Assuming for a moment that FK references are not available, what lock hints should be placed on the preconditions of the following example to prevent deadlocks and inconsistent data? (This is a ...
0
votes
1answer
611 views

Diagnosing Blocking issues using Blocked Process report

I have an application that is going down as a result of timeouts. During these times we see a massive spike in response times from the database that holds ASP session state. In an effort to to ...
3
votes
1answer
582 views

Understanding SIX lock in Microsoft SQL-Server

Can somebody explain me how a process can acquire SIX lock on a page? I my deadlock-graph xml file I see that a process running under RC isolation level (executing a select statement at the moment of ...
2
votes
2answers
306 views

Finding the last command issued by a blocking process

I've developed an interface from ERP SAP Business One to warehouse management, and am facing problems concerning database locks. My program includes direct write operations into the ERP database (a ...
4
votes
1answer
152 views

Can I tell SQL Server to “do something” when a connection is closed?

Can I tell SQL Server to "do something" (e.g. release an application-level lock) when the connection is closed for whatever reason? Background: I want to lock a record on the application level, not ...
4
votes
1answer
262 views

Performance impact of sp_configure blocked process threshold change

I've been tasked with identifying any blocking happening on a production server. My plan to achieve this is to use EXECUTE sp_configure 'blocked process threshold', 5 In combination with a server ...
2
votes
2answers
2k views

Does a simple select query acquire locks?

I am very new to SQL Server, and would like to understand whether the following, very simple select statement would take any locks. Select * from Student; Please consider the case where the ...
0
votes
1answer
32 views

How to get default locking hint of a table

Is there is any way to get default locking hint using a query. I want to know whether the locking use by my transaction is tablock | rowlock | nolock etc.
1
vote
0answers
33 views

Does SQL Server place shared locks on scanned records when using REPEATABLE READ [duplicate]

Assume a SQLCmd session which is using transaction isolation level REPEATABLE READ. In this session I start a transaction and execute an UPDATE statement with a WHERE clause on a non indexed column. ...
7
votes
2answers
1k views

Updating a table with more than 850 million rows of data

I have been tasked with writing an update query to update a table with more than 850 million rows of data. Here are the table structures: Source Tables : CREATE TABLE [dbo].[SourceTable1]( ...
3
votes
1answer
246 views

Shared locks taken under Read Committed Snapshot Isolation (RCSI)

I am seeing waits for shared locks (LCK_M_S) when using RCSI. My understanding is that this isn't supposed to happen since SELECTs do not require shared locks when using RCSI. How can I be seeing ...
2
votes
1answer
3k views

Find out number of active locks on a table

I was wondering if there is a way to find out the number of active locks held on a table in SQL Server?
3
votes
2answers
300 views

pessimistic locking and client death

We are considering pessimistic locking for our project (SELECT ... FOR UPDATE); as we are used to optimistic locking, we are afraid about the operation of the server being blocked on such a lock (lock ...
3
votes
2answers
1k views

difference between UPDLOCK and FOR UPDATE

given the following code: Set conn = CreateObject("ADODB.Connection") Set RS = CreateObject("ADODB.Recordset") conn.BeginTrans ... //This is the line it is about RS.Open "SELECT a,b FROM c FOR ...
1
vote
1answer
130 views

SQL Server 2005 Blocking Issue

I am experiencing a blocking issue in sql server 2005. An end user executes a query (e.g. select statements) and seems to get into a never-ending blocking until i kill the process e.g. for hours on ...
4
votes
1answer
2k views

ASPState database locking and growth problems

We use an ASPState database to persist .NET Session state on a SQL Server 2005 cluster. We are seeing some strange behavior during peak periods The DeleteExpiredSessions proc is run every minute ...
5
votes
2answers
620 views

Options for setting NOLOCK hint in dataset queries

Some context: At first we wrote reports just "straight up", without any locking hints in the queries. With the larger reports this would sometimes cause locking problems. At first we remedied this by ...
3
votes
1answer
918 views

How to force the use of row locks?

I am trying to get my table to use row locks. I have already disabled lock escalation. The isolation level is READ_COMMITTED_SNAPSHOT alter table <TABLE_NAME> SET (LOCK_ESCALATION=DISABLE) go ...
4
votes
2answers
1k views

Deadlock on insert for a table with an insert trigger that updates column in same triggered table

By specification each table uses a guid as the primary key. Another requirement is that each resource requires a 6 number identifier (100001, 100002, ...) as a public id that is unique within the ...
4
votes
2answers
245 views

Schema blocking in READ_COMMITTED_SNAPSHOT

I am trying to troubleshoot locking behavior and the READ_COMMITTED_SNAPSHOT isolation level while attempting to resolve concurrency issues. Background: Assume an online ordering system (ecommerce). ...
3
votes
1answer
206 views

SQL Server : KEY lock prevents FK on nearby row, but not SELECT [closed]

I have the following table structure (simplified, pseudocode): TABLE dbo.Users (Id int NOT NULL IDENTITY PRIMARY KEY) TABLE dbo.Transfers ( Id int NOT NULL IDENTITY PRIMARY KEY, ...
7
votes
2answers
3k views

FIFO queue table for multiple workers in SQL Server

I was attempting to answer the following stackoverflow question: What SQL Server 2005/2008 locking approach should I use to process individual table rows in multiple server application instances? ...
3
votes
1answer
229 views

All-or-none exclusive lock on 2 SQL tables

My simplistic testing suggested that I can acquire an exclusive lock on 2 tables or postpone acquiring the lock on either until both can be acquired at once by using a query like this: SELECT ...
1
vote
1answer
287 views

JDBC Read uncommitted in SQL Server

If I set the jdbc isolation level to read uncommitted in SQL Server, will SQL Server ever lock any tables, pages, rows etc? Thanks.
5
votes
2answers
1k views

how does SQL Server know to lock view objects?

Not sure whether this is a more or less appropriate place to ask this question, originally posed at Stack Overflow. In SQL Server 2008 I have a view V over tables A and B that looks roughly like ...
1
vote
2answers
165 views

INSERT statements in a transaction and locking a range of rows

I have a table that is concurrently accessed by many users. Typically, INSERTs to the table are made one at a time. Occassionaly, users will add a "batch" of rows at the same time. The batch is ...
6
votes
4answers
4k views

Can I be automatically notified of prolonged blocking in SQL server?

About once a week I have to resolve a blocking chain on a SQL Server 2005 database, caused by a long-lived read lock from an Access 2003 front-end. The lock is taken out whenever a user opens a ...
1
vote
2answers
858 views

How to lock a Row while I'm using it

I want to be able to Lock a row, select it, increment its value, and then release the lock. (without lockin the other rows, so that other connections can work with the rest of the table) I've found ...
6
votes
4answers
430 views

Resources for understanding SQL Server locking and concurrency?

As demonstrated by a recent question of mine locking and concurrency are HARD. Can you suggest any good resources for intermediate-to-advanced SQL professionals to do a thorough study on these that ...
16
votes
3answers
2k views

Managing concurrency when using SELECT-UPDATE pattern

Let's say you have the following code (please ignore that it's awful): BEGIN TRAN; DECLARE @id int SELECT @id = id + 1 FROM TableA; UPDATE TableA SET id = @id; --TableA must have only one row, ...
2
votes
1answer
201 views

Checking lock granularity

I understand in SQL Server there are various levels of granularity for locks...RID, Key, Page, Extent, Table, DB. Forgetting about lock escalation, I just want to check the default level of lock ...
2
votes
2answers
640 views

How do I determine what object two SQL process are contending over?

When long running Query#1 exclusively locks an object (table, index or page), and then Query#2 is executed by another user and becomes blocked on the lock held by Query:1, how would I get the name of ...