February 13, 2012 at 4:05 am
IF OBJECT_ID('Consultants') IS NOT NULL DROP TABLE Consultants
GO
CREATE TABLE Consultants (
ConsultantID INT IDENTITY,
ConsultantName VARCHAR(20),
Technology VARCHAR(15),
HireDate DATETIME,
HourlyRate MONEY)
---------------------------------------------------------------------
-- Fill some data
---------------------------------------------------------------------
INSERT INTO Consultants (ConsultantName, Technology, HireDate, HourlyRate)
SELECT 'Jacob', 'SQL Server', '2000-10-03', 120
INSERT INTO Consultants (ConsultantName, Technology, HireDate, HourlyRate)
SELECT 'Smith', 'ASP.NET', '2005-01-01', 140
INSERT INTO Consultants (ConsultantName, Technology, HireDate, HourlyRate)
SELECT 'Michael', 'C#.NET', '2002-10-30', 100
BEGIN TRAN
UPDATE Consultants SET
HourlyRate = 150
WHERE ConsultantName = 'jacob'
Block occured here.
open new window and execute the below query
select * from Consultants
my question : how to insert and modified values in multi user mode without block or timeout.
February 13, 2012 at 5:09 am
This was removed by the editor as SPAM
February 13, 2012 at 5:12 am
Blocking is a part of the process of the system. You need to define your transactions such that they are as small and as short as possible. Tune the queries, tune the indexes, because good performance helps keep the transactions short, plus it helps avoid accessing more of the structure than you need (scans versus seeks for example). Finally, you can look at using one of the snapshot isolation levels. This allows reads to take place while updates are occurring with very little blocking. I'd suggest reading up on read committed snapshot. It's the isolation level I've used the most.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 13, 2012 at 6:12 am
murthysundara (2/13/2012)
IF OBJECT_ID('Consultants') IS NOT NULL DROP TABLE ConsultantsGO
CREATE TABLE Consultants (
ConsultantID INT IDENTITY,
ConsultantName VARCHAR(20),
Technology VARCHAR(15),
HireDate DATETIME,
HourlyRate MONEY)
---------------------------------------------------------------------
-- Fill some data
---------------------------------------------------------------------
INSERT INTO Consultants (ConsultantName, Technology, HireDate, HourlyRate)
SELECT 'Jacob', 'SQL Server', '2000-10-03', 120
INSERT INTO Consultants (ConsultantName, Technology, HireDate, HourlyRate)
SELECT 'Smith', 'ASP.NET', '2005-01-01', 140
INSERT INTO Consultants (ConsultantName, Technology, HireDate, HourlyRate)
SELECT 'Michael', 'C#.NET', '2002-10-30', 100
BEGIN TRAN
UPDATE Consultants SET
HourlyRate = 150
WHERE ConsultantName = 'jacob'
Block occured here.
open new window and execute the below query
select * from Consultants
my question : how to insert and modified values in multi user mode without block or timeout.
Alright I give up, where's the 'COMMIT' at?
February 13, 2012 at 6:29 am
patrickmcginnis59 (2/13/2012)
murthysundara (2/13/2012)
IF OBJECT_ID('Consultants') IS NOT NULL DROP TABLE ConsultantsGO
CREATE TABLE Consultants (
ConsultantID INT IDENTITY,
ConsultantName VARCHAR(20),
Technology VARCHAR(15),
HireDate DATETIME,
HourlyRate MONEY)
---------------------------------------------------------------------
-- Fill some data
---------------------------------------------------------------------
INSERT INTO Consultants (ConsultantName, Technology, HireDate, HourlyRate)
SELECT 'Jacob', 'SQL Server', '2000-10-03', 120
INSERT INTO Consultants (ConsultantName, Technology, HireDate, HourlyRate)
SELECT 'Smith', 'ASP.NET', '2005-01-01', 140
INSERT INTO Consultants (ConsultantName, Technology, HireDate, HourlyRate)
SELECT 'Michael', 'C#.NET', '2002-10-30', 100
BEGIN TRAN
UPDATE Consultants SET
HourlyRate = 150
WHERE ConsultantName = 'jacob'
Block occured here.
open new window and execute the below query
select * from Consultants
my question : how to insert and modified values in multi user mode without block or timeout.
Alright I give up, where's the 'COMMIT' at?
What Patrick said 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply