Tagged Questions
4
votes
0answers
85 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
39 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 ...
0
votes
1answer
23 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
371 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](
...
1
vote
0answers
85 views
Getting LCK_M_S On read committed Snapshot Isolation
I'm getting LCK_M_S on read_committed_snapshot isolation. From my understanding, this isn't supposed to happen since my selects are not placing shared locks anymore.
From my logs, I can see that my ...
1
vote
1answer
69 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
138 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 ...
2
votes
2answers
222 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
91 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 ...
3
votes
1answer
866 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
261 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
310 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
...
2
votes
2answers
692 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
161 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
138 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
1k 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
182 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
194 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
742 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
136 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
3k 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
414 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 ...
5
votes
4answers
388 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 ...
13
votes
3answers
1k 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
140 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
295 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 ...
2
votes
2answers
879 views
SET LOCK_TIMEOUT, is it session or statement based?
For what period does the LOCK_TIMEOUT LAST?
I do a "SET LOCK_TIMEOUT 10" and a "SELECT @@LOCK_TIMEOUT" in one command after a login and get 10 returned. Immediately after this I do a "SELECT ...
21
votes
4answers
1k views
Is NOLOCK always bad?
I am a Report Developer who wants to make my queries as efficient as possible. I used to work with a DBA who told me - I believe because I was always dealing with reports on a Production Server - to ...
1
vote
2answers
176 views
What are the disadvantage/consequences of moving updated column to separate table to prevent table lock on the original table?
I have a table p with a column points. There are a couple of million of records in the table and i need to regularly calculate and update the points for each row. This process takes a couple of ...
2
votes
2answers
265 views
Alternatives to sp_indexoption for Row Locks and Page Locks in SQL Server 2008
Are there better alternatives to setting the Row Locks and Page Locks in SQL Server 2008?
The case here is to set the row and page locks to false on the table. The lock is in effect during a series ...
7
votes
2answers
322 views
How does SQL Server determine the order it takes locks in while selecting a table?
I have two stored procedures that are deadlocking when the system is under load. Proc A is selecting from a table while the Proc B is inserting into the same table. The Lock Graph is showing that Proc ...
3
votes
1answer
196 views
Does blocking always mean open transaction?
This may seem a bit dull question but does blocking always mean that there is open transaction and that may cause transaction log grow to infinity because open transaction prevents log truncation ...
6
votes
2answers
810 views
Shared Lock issued on IsolationLevel.ReadUncommitted
I read that if I use IsolationLevel.ReadUncommitted, the query shouldn't issue any lock. However, when I tested this, I saw the following lock:
Resource_Type: HOBT
Request_Mode: S (Shared)
What is ...
5
votes
3answers
1k views
SQL Server - what isolation level for non-blocking select statements?
I have a long running transaction (called, say, T1) that performs some deletes, updates and inserts on a table in SQL Server 2008 R2. At the same time, another process periodically runs select ...
7
votes
2answers
4k views
Difference Between Row Level and Page Level Locking and Consequences
When attempting to run my Maintenance Plan, I receive the following error:
Executing the query "" failed with the following error: "The
index "" (partition 1) on table "" cannot be reorganized
...
3
votes
2answers
448 views
Will creating partitions reduce locking and how do we implement this in sql-server?
A client wants our application to process more data faster so arranged a meeting with their dba to discuss options.
This application generates quite a lot of data that is used for reporting. Before ...
6
votes
4answers
628 views
SQL Server: what should be done when a process is blocking another process?
I'm an accidental DBA and still learning.
When I see from DMV:s or Activity Monitor that some process is blocking another process, what should be done?
Should I simply kill those processes or is ...
8
votes
2answers
1k views
What is blocking and how does it happen?
I tried to find some information about blocking in SQL Server, but I could not find a concise explanation for what it is and how it happens. Could you please enlighten me?
16
votes
3answers
2k views
Adding columns to production tables
What's the best way to add columns to large production tables on SQL Server 2008 R2? According to Microsoft's books online:
The changes specified in ALTER TABLE are implemented immediately. If the ...
3
votes
1answer
548 views
No “Using locked pages for buffer pool” in SQL Server log
As a trial run for a client I am trying to enable the "Lock Pages in Memory" setting on my development server, but no matter what I change, I cannot get the "Using locked pages for buffer pool" to ...
16
votes
3answers
2k views
Justify NOT using (nolock) hint in every query
Have you ever had to justify NOT using a query hint?
I am seeing WITH (NOLOCK) in every single query that hits a very busy server. It is to the point that the developers think it should just be on ...
3
votes
2answers
2k views
What is the best practice for storing image varbinary(max) data in a database table?
I have a table that stores images that range in size between 16-100 KB each. Since the images are so small, I've taken Microsoft's advice and not used the FILESTREAM data type. The table is ...
5
votes
2answers
366 views
SQL Server - RangeX-X and RangeI-N locks
I came to a dead point in a deadlock analyze. According to msdn:
RangeX-X are Exclusive range, exclusive resource lock; used when updating a key in a range.
RangeI-N are Insert range, null resource ...
15
votes
3answers
4k views
What is lock escalation?
I was asked this question at an interview and had no answer. Can anyone here explain?
