We use the following sp in our VB applications.
The basic logic in this sp is 1) read a row, 2)marked the row is
"checked out), 3) write the same row to another table.
This sp will be executed by many users at the same time. The current
logic we built having slow response, but won't lead to any
locking/deadlocks. However, if we add transaction to make sure the row
we read (you do not want others to have same row) is locked, then we
starting have deadlock/lock problem. Any suggestions to improve the
performance without risk of deadlocks (assuming we have good indexes on
the table)?
Thank you very much for your expertise.
CREATE PROCEDURE sp_get_dcn_sql
@.UserID AS char(8),
@.ProfileID as int
AS
Set nocount on
declare @.tempWhere as varchar(2000), @.tempOrderBy as varchar(1000),
@.sqlstr as nvarchar(3000)
declare @.tempDCN as varchar(16), @.tempST as char(2), @.tempDept as
char(5)
declare @.flagGoodDCN as char(1)
create table #tempDCN (tempDCN varchar(16) NULL, tempST char(2) NULL,
tempDept char(5) NULL)
select @.tempWhere = ProfileWhere, @.tempOrderBy = ProfileOrderBy from
tblYZProfileText where ProfileID = @.ProfileID
set @.sqlstr = 'select top 1 DCN, CO_Cd, Dept from tblYZInventoryDetail
where ' +
@.tempWhere + ' and (check_out is null or check_out = ''N'' or
check_out <> ''Y'') '
if ltrim(rtrim(@.tempOrderBy)) is not null
set @.sqlstr = @.sqlstr + ' order by ' + @.tempOrderBy
set @.flagGoodDCN = ' '
while @.flagGoodDCN <> 'Y' --loop to find the next untouched DCN
begin
insert into #tempDCN exec sp_executesql @.sqlstr
select @.tempDCN = tempDCN, @.tempST = tempST, @.tempDept = tempDept from
#tempDCN
if @.tempDCN is not null
begin
update tblYZInventoryDetail set check_out = 'Y'
where DCN = @.tempDCN and Co_Cd = @.tempST and Dept = @.tempDept
insert into tblYZWorkedClaims (DCN, StateID, Dept, StartTime,
WorkedUser)
select @.tempDCN, @.tempST,@.tempDept,getdate(),@.UserID
if @.@.error = 0 --catch PK violation
set @.flagGoodDCN = 'Y'
else
truncate table #tempDCN --go loop
end
else
begin
break
end
end
select tempDCN as DCN, tempST as Co_Cd, tempDept as Dept from #tempDCN
drop table #tempDCN
Set nocount off
GO
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!I'm guessing that you're experiencing conversion deadlocks when the read
locks taken by the select later need to be upgraded to exclusive locks for
the update.
One common solution for this problem is to take update locks on the select
which should ease the deadlock problem when you introduce the transaction
statement, eg:
select @.tempWhere = ProfileWhere, @.tempOrderBy = ProfileOrderBy
from tblYZProfileText WITH (UPDLOCK)
where ProfileID = @.ProfileID
You can read up on this locking hint in SQL Server Books Online here:
http://msdn.microsoft.com/library/en-us/acdata/ac_8_con_7a_1hf7.asp
Take care not to over-use locking hints as they can hurt you more than help
you if you use them when you don't need to..
HTH
Regards,
Greg Linwood
SQL Server MVP
"YZ" <ycz@.dex.com> wrote in message
news:eR5yylNnEHA.3988@.tk2msftngp13.phx.gbl...
> We use the following sp in our VB applications.
> The basic logic in this sp is 1) read a row, 2)marked the row is
> "checked out), 3) write the same row to another table.
> This sp will be executed by many users at the same time. The current
> logic we built having slow response, but won't lead to any
> locking/deadlocks. However, if we add transaction to make sure the row
> we read (you do not want others to have same row) is locked, then we
> starting have deadlock/lock problem. Any suggestions to improve the
> performance without risk of deadlocks (assuming we have good indexes on
> the table)?
> Thank you very much for your expertise.
>
> CREATE PROCEDURE sp_get_dcn_sql
> @.UserID AS char(8),
> @.ProfileID as int
> AS
> Set nocount on
> declare @.tempWhere as varchar(2000), @.tempOrderBy as varchar(1000),
> @.sqlstr as nvarchar(3000)
> declare @.tempDCN as varchar(16), @.tempST as char(2), @.tempDept as
> char(5)
> declare @.flagGoodDCN as char(1)
> create table #tempDCN (tempDCN varchar(16) NULL, tempST char(2) NULL,
> tempDept char(5) NULL)
> select @.tempWhere = ProfileWhere, @.tempOrderBy = ProfileOrderBy from
> tblYZProfileText where ProfileID = @.ProfileID
> set @.sqlstr = 'select top 1 DCN, CO_Cd, Dept from tblYZInventoryDetail
> where ' +
> @.tempWhere + ' and (check_out is null or check_out = ''N'' or
> check_out <> ''Y'') '
> if ltrim(rtrim(@.tempOrderBy)) is not null
> set @.sqlstr = @.sqlstr + ' order by ' + @.tempOrderBy
> set @.flagGoodDCN = ' '
> while @.flagGoodDCN <> 'Y' --loop to find the next untouched DCN
> begin
> insert into #tempDCN exec sp_executesql @.sqlstr
> select @.tempDCN = tempDCN, @.tempST = tempST, @.tempDept = tempDept from
> #tempDCN
> if @.tempDCN is not null
> begin
> update tblYZInventoryDetail set check_out = 'Y'
> where DCN = @.tempDCN and Co_Cd = @.tempST and Dept = @.tempDept
> insert into tblYZWorkedClaims (DCN, StateID, Dept, StartTime,
> WorkedUser)
> select @.tempDCN, @.tempST,@.tempDept,getdate(),@.UserID
> if @.@.error = 0 --catch PK violation
> set @.flagGoodDCN = 'Y'
> else
> truncate table #tempDCN --go loop
> end
> else
> begin
> break
> end
> end
> select tempDCN as DCN, tempST as Co_Cd, tempDept as Dept from #tempDCN
> drop table #tempDCN
> Set nocount off
> GO
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
Showing posts with label applications. Show all posts
Showing posts with label applications. Show all posts
Friday, February 17, 2012
Concurrent access,, Locks, and Deadlocks
We use the following sp in our VB applications.
The basic logic in this sp is 1) read a row, 2)marked the row is
"checked out), 3) write the same row to another table.
This sp will be executed by many users at the same time. The current
logic we built having slow response, but won't lead to any
locking/deadlocks. However, if we add transaction to make sure the row
we read (you do not want others to have same row) is locked, then we
starting have deadlock/lock problem. Any suggestions to improve the
performance without risk of deadlocks (assuming we have good indexes on
the table)?
Thank you very much for your expertise.
CREATE PROCEDURE sp_get_dcn_sql
@.UserID AS char(8),
@.ProfileID as int
AS
Set nocount on
declare @.tempWhere as varchar(2000), @.tempOrderBy as varchar(1000),
@.sqlstr as nvarchar(3000)
declare @.tempDCN as varchar(16), @.tempST as char(2), @.tempDept as
char(5)
declare @.flagGoodDCN as char(1)
create table #tempDCN (tempDCN varchar(16) NULL, tempST char(2) NULL,
tempDept char(5) NULL)
select @.tempWhere = ProfileWhere, @.tempOrderBy = ProfileOrderBy from
tblYZProfileText where ProfileID = @.ProfileID
set @.sqlstr = 'select top 1 DCN, CO_Cd, Dept from tblYZInventoryDetail
where ' +
@.tempWhere + ' and (check_out is null or check_out = ''N'' or
check_out <> ''Y'') '
if ltrim(rtrim(@.tempOrderBy)) is not null
set @.sqlstr = @.sqlstr + ' order by ' + @.tempOrderBy
set @.flagGoodDCN = ' '
while @.flagGoodDCN <> 'Y' --loop to find the next untouched DCN
begin
insert into #tempDCN exec sp_executesql @.sqlstr
select @.tempDCN = tempDCN, @.tempST = tempST, @.tempDept = tempDept from
#tempDCN
if @.tempDCN is not null
begin
update tblYZInventoryDetail set check_out = 'Y'
where DCN = @.tempDCN and Co_Cd = @.tempST and Dept = @.tempDept
insert into tblYZWorkedClaims (DCN, StateID, Dept, StartTime,
WorkedUser)
select @.tempDCN, @.tempST,@.tempDept,getdate(),@.UserID
if @.@.error = 0 --catch PK violation
set @.flagGoodDCN = 'Y'
else
truncate table #tempDCN --go loop
end
else
begin
break
end
end
select tempDCN as DCN, tempST as Co_Cd, tempDept as Dept from #tempDCN
drop table #tempDCN
Set nocount off
GO
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
I'm guessing that you're experiencing conversion deadlocks when the read
locks taken by the select later need to be upgraded to exclusive locks for
the update.
One common solution for this problem is to take update locks on the select
which should ease the deadlock problem when you introduce the transaction
statement, eg:
select @.tempWhere = ProfileWhere, @.tempOrderBy = ProfileOrderBy
from tblYZProfileText WITH (UPDLOCK)
where ProfileID = @.ProfileID
You can read up on this locking hint in SQL Server Books Online here:
http://msdn.microsoft.com/library/en...on_7a_1hf7.asp
Take care not to over-use locking hints as they can hurt you more than help
you if you use them when you don't need to..
HTH
Regards,
Greg Linwood
SQL Server MVP
"YZ" <ycz@.dex.com> wrote in message
news:eR5yylNnEHA.3988@.tk2msftngp13.phx.gbl...
> We use the following sp in our VB applications.
> The basic logic in this sp is 1) read a row, 2)marked the row is
> "checked out), 3) write the same row to another table.
> This sp will be executed by many users at the same time. The current
> logic we built having slow response, but won't lead to any
> locking/deadlocks. However, if we add transaction to make sure the row
> we read (you do not want others to have same row) is locked, then we
> starting have deadlock/lock problem. Any suggestions to improve the
> performance without risk of deadlocks (assuming we have good indexes on
> the table)?
> Thank you very much for your expertise.
>
> CREATE PROCEDURE sp_get_dcn_sql
> @.UserID AS char(8),
> @.ProfileID as int
> AS
> Set nocount on
> declare @.tempWhere as varchar(2000), @.tempOrderBy as varchar(1000),
> @.sqlstr as nvarchar(3000)
> declare @.tempDCN as varchar(16), @.tempST as char(2), @.tempDept as
> char(5)
> declare @.flagGoodDCN as char(1)
> create table #tempDCN (tempDCN varchar(16) NULL, tempST char(2) NULL,
> tempDept char(5) NULL)
> select @.tempWhere = ProfileWhere, @.tempOrderBy = ProfileOrderBy from
> tblYZProfileText where ProfileID = @.ProfileID
> set @.sqlstr = 'select top 1 DCN, CO_Cd, Dept from tblYZInventoryDetail
> where ' +
> @.tempWhere + ' and (check_out is null or check_out = ''N'' or
> check_out <> ''Y'') '
> if ltrim(rtrim(@.tempOrderBy)) is not null
> set @.sqlstr = @.sqlstr + ' order by ' + @.tempOrderBy
> set @.flagGoodDCN = ' '
> while @.flagGoodDCN <> 'Y' --loop to find the next untouched DCN
> begin
> insert into #tempDCN exec sp_executesql @.sqlstr
> select @.tempDCN = tempDCN, @.tempST = tempST, @.tempDept = tempDept from
> #tempDCN
> if @.tempDCN is not null
> begin
> update tblYZInventoryDetail set check_out = 'Y'
> where DCN = @.tempDCN and Co_Cd = @.tempST and Dept = @.tempDept
> insert into tblYZWorkedClaims (DCN, StateID, Dept, StartTime,
> WorkedUser)
> select @.tempDCN, @.tempST,@.tempDept,getdate(),@.UserID
> if @.@.error = 0 --catch PK violation
> set @.flagGoodDCN = 'Y'
> else
> truncate table #tempDCN --go loop
> end
> else
> begin
> break
> end
> end
> select tempDCN as DCN, tempST as Co_Cd, tempDept as Dept from #tempDCN
> drop table #tempDCN
> Set nocount off
> GO
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
The basic logic in this sp is 1) read a row, 2)marked the row is
"checked out), 3) write the same row to another table.
This sp will be executed by many users at the same time. The current
logic we built having slow response, but won't lead to any
locking/deadlocks. However, if we add transaction to make sure the row
we read (you do not want others to have same row) is locked, then we
starting have deadlock/lock problem. Any suggestions to improve the
performance without risk of deadlocks (assuming we have good indexes on
the table)?
Thank you very much for your expertise.
CREATE PROCEDURE sp_get_dcn_sql
@.UserID AS char(8),
@.ProfileID as int
AS
Set nocount on
declare @.tempWhere as varchar(2000), @.tempOrderBy as varchar(1000),
@.sqlstr as nvarchar(3000)
declare @.tempDCN as varchar(16), @.tempST as char(2), @.tempDept as
char(5)
declare @.flagGoodDCN as char(1)
create table #tempDCN (tempDCN varchar(16) NULL, tempST char(2) NULL,
tempDept char(5) NULL)
select @.tempWhere = ProfileWhere, @.tempOrderBy = ProfileOrderBy from
tblYZProfileText where ProfileID = @.ProfileID
set @.sqlstr = 'select top 1 DCN, CO_Cd, Dept from tblYZInventoryDetail
where ' +
@.tempWhere + ' and (check_out is null or check_out = ''N'' or
check_out <> ''Y'') '
if ltrim(rtrim(@.tempOrderBy)) is not null
set @.sqlstr = @.sqlstr + ' order by ' + @.tempOrderBy
set @.flagGoodDCN = ' '
while @.flagGoodDCN <> 'Y' --loop to find the next untouched DCN
begin
insert into #tempDCN exec sp_executesql @.sqlstr
select @.tempDCN = tempDCN, @.tempST = tempST, @.tempDept = tempDept from
#tempDCN
if @.tempDCN is not null
begin
update tblYZInventoryDetail set check_out = 'Y'
where DCN = @.tempDCN and Co_Cd = @.tempST and Dept = @.tempDept
insert into tblYZWorkedClaims (DCN, StateID, Dept, StartTime,
WorkedUser)
select @.tempDCN, @.tempST,@.tempDept,getdate(),@.UserID
if @.@.error = 0 --catch PK violation
set @.flagGoodDCN = 'Y'
else
truncate table #tempDCN --go loop
end
else
begin
break
end
end
select tempDCN as DCN, tempST as Co_Cd, tempDept as Dept from #tempDCN
drop table #tempDCN
Set nocount off
GO
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
I'm guessing that you're experiencing conversion deadlocks when the read
locks taken by the select later need to be upgraded to exclusive locks for
the update.
One common solution for this problem is to take update locks on the select
which should ease the deadlock problem when you introduce the transaction
statement, eg:
select @.tempWhere = ProfileWhere, @.tempOrderBy = ProfileOrderBy
from tblYZProfileText WITH (UPDLOCK)
where ProfileID = @.ProfileID
You can read up on this locking hint in SQL Server Books Online here:
http://msdn.microsoft.com/library/en...on_7a_1hf7.asp
Take care not to over-use locking hints as they can hurt you more than help
you if you use them when you don't need to..
HTH
Regards,
Greg Linwood
SQL Server MVP
"YZ" <ycz@.dex.com> wrote in message
news:eR5yylNnEHA.3988@.tk2msftngp13.phx.gbl...
> We use the following sp in our VB applications.
> The basic logic in this sp is 1) read a row, 2)marked the row is
> "checked out), 3) write the same row to another table.
> This sp will be executed by many users at the same time. The current
> logic we built having slow response, but won't lead to any
> locking/deadlocks. However, if we add transaction to make sure the row
> we read (you do not want others to have same row) is locked, then we
> starting have deadlock/lock problem. Any suggestions to improve the
> performance without risk of deadlocks (assuming we have good indexes on
> the table)?
> Thank you very much for your expertise.
>
> CREATE PROCEDURE sp_get_dcn_sql
> @.UserID AS char(8),
> @.ProfileID as int
> AS
> Set nocount on
> declare @.tempWhere as varchar(2000), @.tempOrderBy as varchar(1000),
> @.sqlstr as nvarchar(3000)
> declare @.tempDCN as varchar(16), @.tempST as char(2), @.tempDept as
> char(5)
> declare @.flagGoodDCN as char(1)
> create table #tempDCN (tempDCN varchar(16) NULL, tempST char(2) NULL,
> tempDept char(5) NULL)
> select @.tempWhere = ProfileWhere, @.tempOrderBy = ProfileOrderBy from
> tblYZProfileText where ProfileID = @.ProfileID
> set @.sqlstr = 'select top 1 DCN, CO_Cd, Dept from tblYZInventoryDetail
> where ' +
> @.tempWhere + ' and (check_out is null or check_out = ''N'' or
> check_out <> ''Y'') '
> if ltrim(rtrim(@.tempOrderBy)) is not null
> set @.sqlstr = @.sqlstr + ' order by ' + @.tempOrderBy
> set @.flagGoodDCN = ' '
> while @.flagGoodDCN <> 'Y' --loop to find the next untouched DCN
> begin
> insert into #tempDCN exec sp_executesql @.sqlstr
> select @.tempDCN = tempDCN, @.tempST = tempST, @.tempDept = tempDept from
> #tempDCN
> if @.tempDCN is not null
> begin
> update tblYZInventoryDetail set check_out = 'Y'
> where DCN = @.tempDCN and Co_Cd = @.tempST and Dept = @.tempDept
> insert into tblYZWorkedClaims (DCN, StateID, Dept, StartTime,
> WorkedUser)
> select @.tempDCN, @.tempST,@.tempDept,getdate(),@.UserID
> if @.@.error = 0 --catch PK violation
> set @.flagGoodDCN = 'Y'
> else
> truncate table #tempDCN --go loop
> end
> else
> begin
> break
> end
> end
> select tempDCN as DCN, tempST as Co_Cd, tempDept as Dept from #tempDCN
> drop table #tempDCN
> Set nocount off
> GO
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
Tuesday, February 14, 2012
Concurency: Oracle vs. MSSQLServer 2K
Hello Gurus,
Im currently reading a Software Engineering for Web Applications Course
which is published online at the MIT (Open CourseWare Site) and here it says
at least it suggests that concurrency behaviour is best in Oracle than in
MSSQLServer. Im SQL Programmer and essentially I love its ease of use and
Id like you guys to comment this transcript. Is it possible to get the same
results in SQLServer by fine tuning the DBMS?
"
Concurrency is Oracle's strongest suit relative to its commercial
competitors. In Oracle, readers never wait for writers and writers never
wait for readers. Suppose the publisher at a large site starts a query at
12:00 PM summarizing usage by user. Oracle might have to spend an hour
sifting through 200 GB of tracking data. The disk drives grind and one CPU
is completely used up until 1:30 PM. Further suppose that User #356712 comes
in at 12:30 PM and changes his email address, thus updating a row in the
users table. If the usage tracking query arrives at this row at 12:45 PM,
Oracle will notice that the row was last modified after the query started.
Under the "I" in ACID, Oracle is required to isolate the publisher from the
user's update. Oracle does this by reaching into the rollback segment and
producing data from user row #356712 as it was at 12:00 PM when the query
started. Here's the scenario in a table:
Time Publisher Public Web Application
12:00 PM Starts a 90-minute query summarizing usage for preceding
year --
12:30 PM usage summary continues to chug away User #356712 updates
email address from "joe@.foobar.com" to "joe@.yahoo.com"
12:45 PM usage summary arrives at User #356712; Oracle reaches into
rollback segment and pulls out "joe@.foobar.com" for the report, since that's
what the value was at 12:30 PM --
1:30 PM usage summary report completes --
How would this play out in Microsoft SQL Server? When you're reading, you
take read locks on the information that you're about to read. Nobody can
write until you release them. When you're writing, you take write locks on
the information that you're about to update. Nobody can read or write until
you release the locks. In the preceding example, User #356712 would submit
his request for the address change at 12:30 PM. The thread on the Web server
would be blocked waiting for the read locks to clear. How long would it
wait? A full hour with a spinning/waving "browser still receiving
information" icon in the upper right corner of the browser window. If you're
thoughtful, you can program around this locking architecture in SQL Server,
but most Internet service operators would rather just install Oracle than
train their programmers to think more carefully about concurrency.
"
--
Jorge Luzarraga C
"I can do it quick. I can do it cheap. I can do it well. Pick any two."SQL Server 2005 contains a feature called "snapshot isolation" that can help
you with this issue.
You really should evaluate the concurrency and data freshness needs of your
application to determine which model you want to use. I personally have
seen cases where the before image was not what the application really
needed. I've also seen cases where it was fine. So, please make sure you
pick what works for your application requirements.
Thanks,
Conor
"Jorge Luzarraga Castro" <jluzarraga@.fidens.cl> wrote in message
news:ubX1kzVFGHA.532@.TK2MSFTNGP15.phx.gbl...
> Hello Gurus,
> Im currently reading a Software Engineering for Web Applications Course
> which is published online at the MIT (Open CourseWare Site) and here it
> says at least it suggests that concurrency behaviour is best in Oracle
> than in MSSQLServer. Im SQL Programmer and essentially I love its ease of
> use and Id like you guys to comment this transcript. Is it possible to
> get the same results in SQLServer by fine tuning the DBMS?
> "
> Concurrency is Oracle's strongest suit relative to its commercial
> competitors. In Oracle, readers never wait for writers and writers never
> wait for readers. Suppose the publisher at a large site starts a query at
> 12:00 PM summarizing usage by user. Oracle might have to spend an hour
> sifting through 200 GB of tracking data. The disk drives grind and one CPU
> is completely used up until 1:30 PM. Further suppose that User #356712
> comes in at 12:30 PM and changes his email address, thus updating a row in
> the users table. If the usage tracking query arrives at this row at 12:45
> PM, Oracle will notice that the row was last modified after the query
> started. Under the "I" in ACID, Oracle is required to isolate the
> publisher from the user's update. Oracle does this by reaching into the
> rollback segment and producing data from user row #356712 as it was at
> 12:00 PM when the query started. Here's the scenario in a table:
> Time Publisher Public Web Application
> 12:00 PM Starts a 90-minute query summarizing usage for preceding
> year --
> 12:30 PM usage summary continues to chug away User #356712
> updates email address from "joe@.foobar.com" to "joe@.yahoo.com"
> 12:45 PM usage summary arrives at User #356712; Oracle reaches
> into rollback segment and pulls out "joe@.foobar.com" for the report, since
> that's what the value was at 12:30 PM --
> 1:30 PM usage summary report completes --
> How would this play out in Microsoft SQL Server? When you're reading, you
> take read locks on the information that you're about to read. Nobody can
> write until you release them. When you're writing, you take write locks on
> the information that you're about to update. Nobody can read or write
> until you release the locks. In the preceding example, User #356712 would
> submit his request for the address change at 12:30 PM. The thread on the
> Web server would be blocked waiting for the read locks to clear. How long
> would it wait? A full hour with a spinning/waving "browser still receiving
> information" icon in the upper right corner of the browser window. If
> you're thoughtful, you can program around this locking architecture in SQL
> Server, but most Internet service operators would rather just install
> Oracle than train their programmers to think more carefully about
> concurrency.
> "
> --
> Jorge Luzarraga C
> "I can do it quick. I can do it cheap. I can do it well. Pick any two."
>|||> Im currently reading a Software Engineering for Web Applications Course
> which is published online at the MIT (Open CourseWare Site) and here it
> says at least it suggests that concurrency behaviour is best in Oracle
> than in MSSQLServer. Im SQL Programmer and essentially I love its ease of
> use and Id like you guys to comment this transcript. Is it possible to
> get the same results in SQLServer by fine tuning the DBMS?
Well, Oracle and SQL Server both appear on this list three times.
http://www.tpc.org/tpcc/results/tpc...=5¤cyID=0
Yes, Oracle appears above Microsoft, but two notes:
(a) Oracle's most recent data is from April 2005
(b) Oracle' average cost is almost 19% higher than SQL Server (avg $7.31 vs.
$6.15), so yes, you can get a bit better performance (even if you believe
the data is valid after that much time) but it's going to cost you a lot
more up front to build the system.
DB2 is at the top of the list, so why does this article choose to only
compare Oracle and SQL Server? Wait, let me guess, it was written by
someone on Oracle's board, or Oracle has made huge investments in MIT's
labs, or...
> How would this play out in Microsoft SQL Server? When you're reading, you
> take read locks on the information that you're about to read. Nobody can
> write until you release them.
That's only with certain isolation levels. You can certainly do a lot of
reading without blocking anyone, either with dirty reads (a.k.a. NOLOCK) or
with the new snapshot isolation in 2005.
> but most Internet service operators would rather just install Oracle than
> train their programmers to think more carefully about concurrency.
Is there any evidence to back this up? How many service providers do you
know of, that provide Oracle as the back end and say, go ahead, build your
database and application around it? I've never heard of one.
There are plenty of ways to design a well-performing application, and while
database vendor choice is certainly a significant factor, it's not the only
one, and it's not the one you end up spending the most time on.
A
Im currently reading a Software Engineering for Web Applications Course
which is published online at the MIT (Open CourseWare Site) and here it says
at least it suggests that concurrency behaviour is best in Oracle than in
MSSQLServer. Im SQL Programmer and essentially I love its ease of use and
Id like you guys to comment this transcript. Is it possible to get the same
results in SQLServer by fine tuning the DBMS?
"
Concurrency is Oracle's strongest suit relative to its commercial
competitors. In Oracle, readers never wait for writers and writers never
wait for readers. Suppose the publisher at a large site starts a query at
12:00 PM summarizing usage by user. Oracle might have to spend an hour
sifting through 200 GB of tracking data. The disk drives grind and one CPU
is completely used up until 1:30 PM. Further suppose that User #356712 comes
in at 12:30 PM and changes his email address, thus updating a row in the
users table. If the usage tracking query arrives at this row at 12:45 PM,
Oracle will notice that the row was last modified after the query started.
Under the "I" in ACID, Oracle is required to isolate the publisher from the
user's update. Oracle does this by reaching into the rollback segment and
producing data from user row #356712 as it was at 12:00 PM when the query
started. Here's the scenario in a table:
Time Publisher Public Web Application
12:00 PM Starts a 90-minute query summarizing usage for preceding
year --
12:30 PM usage summary continues to chug away User #356712 updates
email address from "joe@.foobar.com" to "joe@.yahoo.com"
12:45 PM usage summary arrives at User #356712; Oracle reaches into
rollback segment and pulls out "joe@.foobar.com" for the report, since that's
what the value was at 12:30 PM --
1:30 PM usage summary report completes --
How would this play out in Microsoft SQL Server? When you're reading, you
take read locks on the information that you're about to read. Nobody can
write until you release them. When you're writing, you take write locks on
the information that you're about to update. Nobody can read or write until
you release the locks. In the preceding example, User #356712 would submit
his request for the address change at 12:30 PM. The thread on the Web server
would be blocked waiting for the read locks to clear. How long would it
wait? A full hour with a spinning/waving "browser still receiving
information" icon in the upper right corner of the browser window. If you're
thoughtful, you can program around this locking architecture in SQL Server,
but most Internet service operators would rather just install Oracle than
train their programmers to think more carefully about concurrency.
"
--
Jorge Luzarraga C
"I can do it quick. I can do it cheap. I can do it well. Pick any two."SQL Server 2005 contains a feature called "snapshot isolation" that can help
you with this issue.
You really should evaluate the concurrency and data freshness needs of your
application to determine which model you want to use. I personally have
seen cases where the before image was not what the application really
needed. I've also seen cases where it was fine. So, please make sure you
pick what works for your application requirements.
Thanks,
Conor
"Jorge Luzarraga Castro" <jluzarraga@.fidens.cl> wrote in message
news:ubX1kzVFGHA.532@.TK2MSFTNGP15.phx.gbl...
> Hello Gurus,
> Im currently reading a Software Engineering for Web Applications Course
> which is published online at the MIT (Open CourseWare Site) and here it
> says at least it suggests that concurrency behaviour is best in Oracle
> than in MSSQLServer. Im SQL Programmer and essentially I love its ease of
> use and Id like you guys to comment this transcript. Is it possible to
> get the same results in SQLServer by fine tuning the DBMS?
> "
> Concurrency is Oracle's strongest suit relative to its commercial
> competitors. In Oracle, readers never wait for writers and writers never
> wait for readers. Suppose the publisher at a large site starts a query at
> 12:00 PM summarizing usage by user. Oracle might have to spend an hour
> sifting through 200 GB of tracking data. The disk drives grind and one CPU
> is completely used up until 1:30 PM. Further suppose that User #356712
> comes in at 12:30 PM and changes his email address, thus updating a row in
> the users table. If the usage tracking query arrives at this row at 12:45
> PM, Oracle will notice that the row was last modified after the query
> started. Under the "I" in ACID, Oracle is required to isolate the
> publisher from the user's update. Oracle does this by reaching into the
> rollback segment and producing data from user row #356712 as it was at
> 12:00 PM when the query started. Here's the scenario in a table:
> Time Publisher Public Web Application
> 12:00 PM Starts a 90-minute query summarizing usage for preceding
> year --
> 12:30 PM usage summary continues to chug away User #356712
> updates email address from "joe@.foobar.com" to "joe@.yahoo.com"
> 12:45 PM usage summary arrives at User #356712; Oracle reaches
> into rollback segment and pulls out "joe@.foobar.com" for the report, since
> that's what the value was at 12:30 PM --
> 1:30 PM usage summary report completes --
> How would this play out in Microsoft SQL Server? When you're reading, you
> take read locks on the information that you're about to read. Nobody can
> write until you release them. When you're writing, you take write locks on
> the information that you're about to update. Nobody can read or write
> until you release the locks. In the preceding example, User #356712 would
> submit his request for the address change at 12:30 PM. The thread on the
> Web server would be blocked waiting for the read locks to clear. How long
> would it wait? A full hour with a spinning/waving "browser still receiving
> information" icon in the upper right corner of the browser window. If
> you're thoughtful, you can program around this locking architecture in SQL
> Server, but most Internet service operators would rather just install
> Oracle than train their programmers to think more carefully about
> concurrency.
> "
> --
> Jorge Luzarraga C
> "I can do it quick. I can do it cheap. I can do it well. Pick any two."
>|||> Im currently reading a Software Engineering for Web Applications Course
> which is published online at the MIT (Open CourseWare Site) and here it
> says at least it suggests that concurrency behaviour is best in Oracle
> than in MSSQLServer. Im SQL Programmer and essentially I love its ease of
> use and Id like you guys to comment this transcript. Is it possible to
> get the same results in SQLServer by fine tuning the DBMS?
Well, Oracle and SQL Server both appear on this list three times.
http://www.tpc.org/tpcc/results/tpc...=5¤cyID=0
Yes, Oracle appears above Microsoft, but two notes:
(a) Oracle's most recent data is from April 2005
(b) Oracle' average cost is almost 19% higher than SQL Server (avg $7.31 vs.
$6.15), so yes, you can get a bit better performance (even if you believe
the data is valid after that much time) but it's going to cost you a lot
more up front to build the system.
DB2 is at the top of the list, so why does this article choose to only
compare Oracle and SQL Server? Wait, let me guess, it was written by
someone on Oracle's board, or Oracle has made huge investments in MIT's
labs, or...
> How would this play out in Microsoft SQL Server? When you're reading, you
> take read locks on the information that you're about to read. Nobody can
> write until you release them.
That's only with certain isolation levels. You can certainly do a lot of
reading without blocking anyone, either with dirty reads (a.k.a. NOLOCK) or
with the new snapshot isolation in 2005.
> but most Internet service operators would rather just install Oracle than
> train their programmers to think more carefully about concurrency.
Is there any evidence to back this up? How many service providers do you
know of, that provide Oracle as the back end and say, go ahead, build your
database and application around it? I've never heard of one.
There are plenty of ways to design a well-performing application, and while
database vendor choice is certainly a significant factor, it's not the only
one, and it's not the one you end up spending the most time on.
A
Subscribe to:
Posts (Atom)