Linked Questions

2
votes
1answer
8k 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 =...
1
vote
0answers
79 views

Can a critical section cause deadlock in SQL Server? [duplicate]

We get the next ID for our tables from a separate table with two columns, TABLE_NAME and NEXT_ID. We have a stored procedure which reads the NEXT_ID and then adds an increment to the NEXT_ID. The work ...
3
votes
0answers
71 views

Update+Select in one SQL Server transaction, and concurrent deadlocks [duplicate]

I have a problem with a transaction isolation or concurrent atomic select+update. SQL Server, C++ application, OTL/ODBC, and I can't use any stored procedures. Here is the thing. The task is to ...
17
votes
8answers
5k views

Reasons for avoiding large ID values

We are working on a web application, not yet accessible to users. My boss noticed that newly created records get an ID of over 10 000, even though we only have under 100 records in the table. She ...
2
votes
1answer
11k views

MERGE with IDENTITY_INSERT ON does not work if Identity is not the primary key

I frequently use MERGE statements and am quite familiar with it. Now I ran into a situation where some tables have IDENTITY columns that are not the primary key. In this situation the script failed ...
2
votes
3answers
2k views

A performance Issue when querying a big table using a PK/clustered index

To simplify the situation, I will only consider one large table... On a nightly basis a store will send all the new and changed data for one day for a large table to the head office. (this portion is ...
5
votes
2answers
281 views

Transaction with lock on a specific table

NOTE : If there is similar question, please refer me to it. I have two tables Request & Receipt. I want table Receipt remain locked for CRUD operations from anywhere until transaction finished. ...
8
votes
1answer
835 views

Unique identifier field with a conditional

I have a database that's not in production, so the main table being CustodyDetails, this table has a ID int IDENTITY(1,1) PRIMARY KEY column and I'm looking for a way of adding another unique ...
1
vote
1answer
2k views

Prevent gaps while using Identity/sequence object in sql server

I have a table and I am trying to insert values in it. I want to prevent gaps in the primary key column. For primary column I have tried using Identity / sequence objects. I know that this is ...
2
votes
0answers
382 views

Optimal locking - append-only store

I'm designing an internal event store library based off SQL Server. One challenge I had to solve is to allow concurrent writes while still being able to generate a reliable Log Sequence Number (LSN) ...