rda submitsql: base

Showing posts with label base. Show all posts

Friday, March 23, 2012

Race condition in SQL Server 2005

hi,
How SQL Server 2005 handel concurrent access to database.
for example. if two application user same data base and same user name to
access it. when both application attempts to access the database how Server
handle this race condition.
Thank in advance
with regards,
AnbuOn 29 May, 07:00, anbu <a...@.discussions.microsoft.com> wrote:
> hi,
> How SQL Server 2005 handel concurrent access to database.
> for example. if two application user same data base and same user name to
> access it. when both application attempts to access the database how Server
> handle this race condition.
> Thank in advance
> with regards,
> Anbu
What you describe is normal. A user's session is scoped to a
connection not a user name, so there is no special problem associated
with multiple users under the same user name accessing the same
database.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks David,
iam very new to SQL Server. can you refer any links where i can find details
about how race condition is handled by Server.
with regards,
Anbu
"David Portas" wrote:
> On 29 May, 07:00, anbu <a...@.discussions.microsoft.com> wrote:
> > hi,
> >
> > How SQL Server 2005 handel concurrent access to database.
> > for example. if two application user same data base and same user name to
> > access it. when both application attempts to access the database how Server
> > handle this race condition.
> >
> > Thank in advance
> > with regards,
> > Anbu
>
> What you describe is normal. A user's session is scoped to a
> connection not a user name, so there is no special problem associated
> with multiple users under the same user name accessing the same
> database.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||See Books Online topics related to locking and row versioning
(http://msdn2.microsoft.com/en-us/library/ms187101.aspx).
Keep in mind the scope that David mentioned when reading the documentation.
You will often see the terms "user" and "session" used interchaneably to
refer to a user's session on a database database connection. This is
completely unrelated to the login/user used to connect to SQL Server.
Hope this helps.
Dan Guzman
SQL Server MVP
"anbu" <anbu@.discussions.microsoft.com> wrote in message
news:53410F4E-C145-446F-8F28-DFB6A24F2823@.microsoft.com...
> Thanks David,
> iam very new to SQL Server. can you refer any links where i can find
> details
> about how race condition is handled by Server.
> with regards,
> Anbu
> "David Portas" wrote:
>> On 29 May, 07:00, anbu <a...@.discussions.microsoft.com> wrote:
>> > hi,
>> >
>> > How SQL Server 2005 handel concurrent access to database.
>> > for example. if two application user same data base and same user name
>> > to
>> > access it. when both application attempts to access the database how
>> > Server
>> > handle this race condition.
>> >
>> > Thank in advance
>> > with regards,
>> > Anbu
>>
>> What you describe is normal. A user's session is scoped to a
>> connection not a user name, so there is no special problem associated
>> with multiple users under the same user name accessing the same
>> database.
>> --
>> David Portas, SQL Server MVP
>> Whenever possible please post enough code to reproduce your problem.
>> Including CREATE TABLE and INSERT statements usually helps.
>> State what version of SQL Server you are using and specify the content
>> of any error messages.
>> SQL Server Books Online:
>> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
>> --
>>|||"anbu" <anbu@.discussions.microsoft.com> wrote in message
news:53410F4E-C145-446F-8F28-DFB6A24F2823@.microsoft.com...
> Thanks David,
> iam very new to SQL Server. can you refer any links where i can find
> details
> about how race condition is handled by Server.
>
Really this isn't a true race condition. (Well, I suppose it can be, but
what you seem to be getting at more is simply multiple accesses.)
Keep in mind like any decent RDBMS, SQL Server is designed to handle this.
At a very basic level, "whoever gets there first wins".
But you need to look up blocking and things like Committed vs. uncommitted
read, read serializable. etc.
> with regards,
> Anbu
> "David Portas" wrote:
>> On 29 May, 07:00, anbu <a...@.discussions.microsoft.com> wrote:
>> > hi,
>> >
>> > How SQL Server 2005 handel concurrent access to database.
>> > for example. if two application user same data base and same user name
>> > to
>> > access it. when both application attempts to access the database how
>> > Server
>> > handle this race condition.
>> >
>> > Thank in advance
>> > with regards,
>> > Anbu
>>
>> What you describe is normal. A user's session is scoped to a
>> connection not a user name, so there is no special problem associated
>> with multiple users under the same user name accessing the same
>> database.
>> --
>> David Portas, SQL Server MVP
>> Whenever possible please post enough code to reproduce your problem.
>> Including CREATE TABLE and INSERT statements usually helps.
>> State what version of SQL Server you are using and specify the content
>> of any error messages.
>> SQL Server Books Online:
>> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
>> --
>>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

R

Hi, my data base table is as follows

PersonID City Sex 1 New York M 2 Boston M 3 Seatle F

and i need to make a report as shown below:

Male

Female Total % Male City

New York 5 8 13 38% Boston 20 35 55 36% Seatle 10 6 16 63%

I'm assuming i need to use a matrix, but how can i get a total and a % column

You can use a standard table for this. To get the total just use an expression to add male and female together. To get the percentage, once again use an expression to evaluate (male/total)*100.

|||Hi sluggy

I am new to reporting services and about to start creating some (matrix) reports. I am interested in your comment that the example above can be created using a tabular report.

I wonder if you could help me out on this scenario:
Say I have a db table with sales for a particular year. (i.e 2003 and 2004). Sol the database table is: field1 = Sales, field2 = Date ( assume the years are static )
Using a tabular report is it possible to calculate the sums for each...? This effectivly what a matrix report would do very well as it allows grouping by columns.

Any response would be greatly appreciated.

Regards,
Neil
|||You could still use a tabular report for eachyear. Matrix reports are when you have information for rows, columns, details (and page header which is optional). In your case, you just have Rows (which will be Date) and Sales (which is column name) and SUM(Sales) group by year (which will be your table details)|||

NeilSt wrote:

Using a tabular report is it possible to calculate the sums for each...?

It sure is - there are many posts on this forum explaining how to do a total row on a table. Totalling is done at the group level, so you can have subtotals within grand totals.

NeilSt wrote:

This effectivly what a matrix report would do very well as it allows grouping by columns.

A matrix is more for when you have a dynamic number of columns. In your prior example, you could use a matrix if Male, Female, Male percentage and Total were on the rows and you had a column grouping on state. But it is just as easy to do it as a table, with Male/Female/etc being the columns and the states being the rows.

|||Hi

The answer to my post seems to assume that the Years field would be a row. I am saying... Row = sales and column = 2003 and 2004. Its more a column group that is required no ? if so how are they done in tabular reports ?

Maybe im missing something...

thanks for the help again.

Neil

Example:
2003 | 2004 | Total

Sales 100 | 50 | 150
now to get the total is fine i guess.. but sales is made up of a number of sales within the year. So its effectivly a sum of all sales in 2003... thats what im trying to work out how to do.

|||

Neil,

You can sum up your sales for all available years in the database in the SQL query itself like this:

(if you have stored the year in a separate column in the table)

SELECT SUM(Sales) AS Sales, Year

FROM SalesTable

GROUP BY Year

(if you have not stored the year in a separate column in the table but in a datetime column, us this)

SELECT SUM(Sales) AS Sales, YEAR(SalesDate) AS Year

FROM SalesTable

GROUP BY YEAR(SalesDate)

and in your matrix, add a row group on Sales field and column group on Year field.

Shyam

|||Shyam,

Yes that is possible, I think when I first responded to this thread, I was mor einterested to know if its possible to do column grouping in tabular reports...? It seems that is what all this comes down to..

Thanks for your help.

Neil
|||Maybe someone can help me decide which style of report makes sense for this mockup of a report:
Columns: Jan - Dec
Rows: City

So lets say we can to find the number of sales for a city form Jan to Dec...
So looking something like,

nyc: 1 2 3 3 3 3 15 3 4 x
nj: 1 1 1 1 1 1 6 1 1 x
la: 2 2 2 2 2 2 12 2 5 x

So which is the best report type to use.. ? Matrix supports column grouping ... tabular only supports row grouping from what I can tell...

each record in the db contains a month , sale value and city...

I leave it up to you all to let me know which one is best suited (tabular/matrix)

Neil
|||

Of course Matrix is suitable. But your report layout woulc make more sense if you could have an additional ROW grouping on Year besides City .

Shyam

|||Hey man.

Thanks again, maybe you can explain to me how one can work out the Total for years column...
which calculates the sum of each row.. This is a limitation of the tabular report (no column grouping) ?

Maybe you could point me in the right direction for this situation: Say i have under each row group, subtotals. But for every detail textbox I check the month of that record and only display it in the appropriate column (jan or feb etc..) how can I get the row group sub total to produce a result for each month.. and not a subtotal of the row group (city), I guess this can be done using a SQL query but im wondering if RS can provide some support for it...

I require this table to have static columns and therefore using a matrix seems too limited.. And there are these small issues stopping me from knowing if its possible to implement using a tabular report...

Neil
|||

- Removed -

|||WOAH!

that is one heck of a thread jack. so anyone have an answer for my original question?
|||

I'm sorry, I didn't intend to thread-jack... I thought we were talking about similar things.

My apologies.

|||sorry

Friday, March 23, 2012

Race condition in SQL Server 2005

Race condition in SQL Server 2005

Race condition in SQL Server 2005

R

rda submitsql

Blog Archive

About Me