PORTS USED: hii

Showing posts with label hii. Show all posts

Friday, March 30, 2012

predicate locking

Hi
I am having a debate with one of the Postgres developers (Tom Lane)
according to him ms-sql does not implement predicate
locking(SERIALIZABLE).
Where predicate locking is defined as (from the postgres help):
"12.2.2.1. Serializable Isolation versus True Serializability"
It states: "To guarantee true mathematical serializability, it is
necessary for a database system to enforce predicate locking, which
means that a transaction cannot insert or modify a row that would have
matched the WHERE condition of a query in another concurrent
transaction"
Now I put it to him that that is exactly what SERIALIZABLE does in
MS-SQL.
But his response was "Only for WHERE conditions that can be expressed
as a simple range constraint."
Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
that can be expressed as a simple range constraint." ?
Regards,
Daniel Roth
MCSD.NETDepends on how you want to quantify it. What is a Range Lock? It is the
demarcation of the begining and the end of records within an Index that
satisfies a particular Where condition. That is for a simple condition that
can be satisfied by an explicit index; however, given a more complex
condition, the range locks can be placed on the Clustered Index that would
satisfy a range of conditions.
However, it is true that there is no "true" predicate locking behavior in
SQL Server. But do not be dismayed, no commercial vendor does so either.
It would have to BLOCK, not lock, upon examination of each subsequently
submitted query to "test" whether or not there may be a logical overlap
between the initiated transaction and any subsequently requested
transaction.
Mathematical Locking is nice, but it is theoretical. A lock is where the
rubber meets the road, and some form of physical lock must be acquired.
That does not mean that it can not be done, but I have failed to see any
real implementations suggested that make practical sense.
Sincerely,
Anthony Thomas
"Daniel Roth" <Daniel Roth@.discussions.microsoft.com> wrote in message
news:7E778689-BEDD-49C4-9ADA-7261222E7593@.microsoft.com...
Hi
I am having a debate with one of the Postgres developers (Tom Lane)
according to him ms-sql does not implement predicate
locking(SERIALIZABLE).
Where predicate locking is defined as (from the postgres help):
"12.2.2.1. Serializable Isolation versus True Serializability"
It states: "To guarantee true mathematical serializability, it is
necessary for a database system to enforce predicate locking, which
means that a transaction cannot insert or modify a row that would have
matched the WHERE condition of a query in another concurrent
transaction"
Now I put it to him that that is exactly what SERIALIZABLE does in
MS-SQL.
But his response was "Only for WHERE conditions that can be expressed
as a simple range constraint."
Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
that can be expressed as a simple range constraint." ?
Regards,
Daniel Roth
MCSD.NET

predicate locking

Hi
I am having a debate with one of the Postgres developers (Tom Lane)
according to him ms-sql does not implement predicate
locking(SERIALIZABLE).
Where predicate locking is defined as (from the postgres help):
"12.2.2.1. Serializable Isolation versus True Serializability"
It states: "To guarantee true mathematical serializability, it is
necessary for a database system to enforce predicate locking, which
means that a transaction cannot insert or modify a row that would have
matched the WHERE condition of a query in another concurrent
transaction"
Now I put it to him that that is exactly what SERIALIZABLE does in
MS-SQL.
But his response was "Only for WHERE conditions that can be expressed
as a simple range constraint."
Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
that can be expressed as a simple range constraint." ?
Regards,
Daniel Roth
MCSD.NET
Depends on how you want to quantify it. What is a Range Lock? It is the
demarcation of the begining and the end of records within an Index that
satisfies a particular Where condition. That is for a simple condition that
can be satisfied by an explicit index; however, given a more complex
condition, the range locks can be placed on the Clustered Index that would
satisfy a range of conditions.
However, it is true that there is no "true" predicate locking behavior in
SQL Server. But do not be dismayed, no commercial vendor does so either.
It would have to BLOCK, not lock, upon examination of each subsequently
submitted query to "test" whether or not there may be a logical overlap
between the initiated transaction and any subsequently requested
transaction.
Mathematical Locking is nice, but it is theoretical. A lock is where the
rubber meets the road, and some form of physical lock must be acquired.
That does not mean that it can not be done, but I have failed to see any
real implementations suggested that make practical sense.
Sincerely,
Anthony Thomas

"Daniel Roth" <Daniel Roth@.discussions.microsoft.com> wrote in message
news:7E778689-BEDD-49C4-9ADA-7261222E7593@.microsoft.com...
Hi
I am having a debate with one of the Postgres developers (Tom Lane)
according to him ms-sql does not implement predicate
locking(SERIALIZABLE).
Where predicate locking is defined as (from the postgres help):
"12.2.2.1. Serializable Isolation versus True Serializability"
It states: "To guarantee true mathematical serializability, it is
necessary for a database system to enforce predicate locking, which
means that a transaction cannot insert or modify a row that would have
matched the WHERE condition of a query in another concurrent
transaction"
Now I put it to him that that is exactly what SERIALIZABLE does in
MS-SQL.
But his response was "Only for WHERE conditions that can be expressed
as a simple range constraint."
Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
that can be expressed as a simple range constraint." ?
Regards,
Daniel Roth
MCSD.NET

predicate locking

I am having a debate with one of the Postgres developers (Tom Lane)
according to him ms-sql does not implement predicate
locking(SERIALIZABLE).

Where predicate locking is defined as (from the postgres help):

"12.2.2.1. Serializable Isolation versus True Serializability"

It states: "To guarantee true mathematical serializability, it is
necessary for a database system to enforce predicate locking, which
means that a transaction cannot insert or modify a row that would have
matched the WHERE condition of a query in another concurrent
transaction"

Now I put it to him that that is exactly what SERIALIZABLE does is
MS-SQL.

But his response was "Only for WHERE conditions that can be expressed
as a simple range constraint."

Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
that can be expressed as a simple range constraint." ?

Regards,

Daniel Roth
MCSD.NET(dan.c.roth@.gmail.com) writes:
> I am having a debate with one of the Postgres developers (Tom Lane)
> according to him ms-sql does not implement predicate
> locking(SERIALIZABLE).
> Where predicate locking is defined as (from the postgres help):
> "12.2.2.1. Serializable Isolation versus True Serializability"
> It states: "To guarantee true mathematical serializability, it is
> necessary for a database system to enforce predicate locking, which
> means that a transaction cannot insert or modify a row that would have
> matched the WHERE condition of a query in another concurrent
> transaction"
> Now I put it to him that that is exactly what SERIALIZABLE does is
> MS-SQL.
> But his response was "Only for WHERE conditions that can be expressed
> as a simple range constraint."
> Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
> that can be expressed as a simple range constraint." ?

But what cannot be expressed a range constraint? It would be interesting
to see a counter-example where Tom Lane believes that SQL Server does
not live up the definition of serializable.

Here is one repro that I tried:

In the first query window I ran:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
go
BEGIN TRANSACTION

SELECT CustomerID FROM Customers C
WHERE NOT EXISTS (SELECT *
FROM Orders O
WHERE O.CustomerID = C.CustomerID)

This query returns two rows: FISSA and PARIS

In the second I ran:

insert Orders (CustomerID, EmployeeID, OrderDate, RequiredDate,
ShippedDate, ShipVia, Freight, ShipName, ShipAddress,
ShipCity, ShipRegion, ShipPostalCode, ShipCountry)
values ('ALBIN', 1, '990817', '990820',
'990819', NULL, 12, NULL, 'adddd',
'London', 'UK', NULL, 'UK')

This query blocked. Note that ALBIN is not a valid CustomerID, so once
I rolled back the first transaction, the INSERT statement failed.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||(dan.c.roth@.gmail.com) writes:
> I am having a debate with one of the Postgres developers (Tom Lane)
> according to him ms-sql does not implement predicate
> locking(SERIALIZABLE).
> Where predicate locking is defined as (from the postgres help):
> "12.2.2.1. Serializable Isolation versus True Serializability"
> It states: "To guarantee true mathematical serializability, it is
> necessary for a database system to enforce predicate locking, which
> means that a transaction cannot insert or modify a row that would have
> matched the WHERE condition of a query in another concurrent
> transaction"
> Now I put it to him that that is exactly what SERIALIZABLE does is
> MS-SQL.
> But his response was "Only for WHERE conditions that can be expressed
> as a simple range constraint."
> Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
> that can be expressed as a simple range constraint." ?

But what cannot be expressed a range constraint? It would be interesting
to see a counter-example where Tom Lane believes that SQL Server does
not live up the definition of serializable.

Here is one repro that I tried:

In the first query window I ran:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
go
BEGIN TRANSACTION

SELECT CustomerID FROM Customers C
WHERE NOT EXISTS (SELECT *
FROM Orders O
WHERE O.CustomerID = C.CustomerID)

This query returns two rows: FISSA and PARIS

In the second I ran:

This query blocked. Note that ALBIN is not a valid CustomerID, so once
I rolled back the first transaction, the INSERT statement failed.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland.

Tom had me worried for a second.

Regards,

Daniel Roth
MCSD.NET

Erland Sommarskog wrote:
> (dan.c.roth@.gmail.com) writes:
> > I am having a debate with one of the Postgres developers (Tom Lane)
> > according to him ms-sql does not implement predicate
> > locking(SERIALIZABLE).
> > Where predicate locking is defined as (from the postgres help):
> > "12.2.2.1. Serializable Isolation versus True Serializability"
> > It states: "To guarantee true mathematical serializability, it is
> > necessary for a database system to enforce predicate locking, which
> > means that a transaction cannot insert or modify a row that would have
> > matched the WHERE condition of a query in another concurrent
> > transaction"
> > Now I put it to him that that is exactly what SERIALIZABLE does is
> > MS-SQL.
> > But his response was "Only for WHERE conditions that can be expressed
> > as a simple range constraint."
> > Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
> > that can be expressed as a simple range constraint." ?
> But what cannot be expressed a range constraint? It would be interesting
> to see a counter-example where Tom Lane believes that SQL Server does
> not live up the definition of serializable.
> Here is one repro that I tried:
> In the first query window I ran:
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> go
> BEGIN TRANSACTION
> SELECT CustomerID FROM Customers C
> WHERE NOT EXISTS (SELECT *
> FROM Orders O
> WHERE O.CustomerID = C.CustomerID)
> This query returns two rows: FISSA and PARIS
> In the second I ran:
> insert Orders (CustomerID, EmployeeID, OrderDate, RequiredDate,
> ShippedDate, ShipVia, Freight, ShipName, ShipAddress,
> ShipCity, ShipRegion, ShipPostalCode, ShipCountry)
> values ('ALBIN', 1, '990817', '990820',
> '990819', NULL, 12, NULL, 'adddd',
> 'London', 'UK', NULL, 'UK')
> This query blocked. Note that ALBIN is not a valid CustomerID, so once
> I rolled back the first transaction, the INSERT statement failed.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland.

Tom had me worried for a second.

Regards,

Daniel Roth
MCSD.NET

Because that transaction had isolation level serializable. This means
that if you resubmit a query with in the transaction, you should
essentially get the same results. Therefore writes must be blocked
until the reader transaction commits.

In the default isolation level "Read Committed", the same query in
the same transaction can give different results, and thus locks can
be released once data has been read.

There is also "Repeatable Read" which is between Serializable and Read
Committed. With Repeatable Read, reading the same row twice should yield
the same result. However, a for a range query that is resubmitted newly
entered rows may appear, so called "phantom inserts".

Finally there is "read uncommitted" in which no locks are taken for
reading at all, and thus you can get to read uncommitted data.

And, oh, in SQL 2005, there is a new level called snapshot. The result
is essentially the same as in serializable, but instead of locking
rows, old versions of changed rows are added to a snapshot. In this
way readers do not block writers. Also, the reader gets a wholly
consistent view of the database at a certain point in time, which you
in fact cannot achieve with Serializable.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||It seems that the serializable should just not "get" the updated rows
when it does queries, not lock the table for everyone else... It seems
like that is what the snapshot is I guess.|||pb648174 (google@.webpaul.net) writes:
> It seems that the serializable should just not "get" the updated rows
> when it does queries, not lock the table for everyone else... It seems
> like that is what the snapshot is I guess.

Without a snapshot like there is an Oracle and Rdb, it's difficult to
do serializable without blocking writers. A snapshot effectively means
that there is more than once instance of the data, and in SQL Server
there is only one single.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Wednesday, March 28, 2012

Precedence of MAX and WHERE

Hi
I'd like to create a query which returns the MAX of a group of dates so long
as the number is less than a given date. For example :
SELECT MAX(date), username
FROM mydatatable
WHERE date < '01/01/2005'
GROUP BY username
Will this do what I expect and return the username and date which is the
most recent before 01/01/2005 ?
Thanks
AndrewHi,
Your query looks good.
Thanks
Hari
SQL Server MVP
"Andrew Webb" <andrew.webb@.eme-med.co.uk> wrote in message
news:OTlNYqfuFHA.1572@.TK2MSFTNGP10.phx.gbl...
> Hi
> I'd like to create a query which returns the MAX of a group of dates so
> long as the number is less than a given date. For example :
>
> SELECT MAX(date), username
> FROM mydatatable
> WHERE date < '01/01/2005'
> GROUP BY username
>
> Will this do what I expect and return the username and date which is the
> most recent before 01/01/2005 ?
> Thanks
> Andrew
>|||You could compare these queries and see which one yields the results you
want. Word problems are tough to solve, usually better to provide specs as
described in http://www.aspfaq.com/5006 . Also, "date" is a really bad name
for a column. Not only is it a reserved word, it is also very tough to
decipher it... date of WHAT? Finally, do not use m/d/y or d/m/y date
formats when hard-coding date strings. The safest approach here is to use
YYYYMMDD format, then this can't be

by software or humans.
CREATE TABLE dbo.myDataTable
(
username VARCHAR(32),
eventDate SMALLDATETIME
)
GO
SET NOCOUNT ON
INSERT myDataTable SELECT 'bob','20040101'
INSERT myDataTable SELECT 'bob','20050201'
INSERT myDataTable SELECT 'frank','20040101'
INSERT myDataTable SELECT 'frank','20040725'
GO
SELECT username, MAX(eventDate)
FROM dbo.myDataTable
WHERE eventDate < '20050101'
GROUP BY username
SELECT username, MAX(eventDate)
FROM dbo.myDataTable
GROUP BY username
HAVING MAX(eventDate) < '20050101'
GO
DROP TABLE dbo.myDataTable
GO
"Andrew Webb" <andrew.webb@.eme-med.co.uk> wrote in message
news:OTlNYqfuFHA.1572@.TK2MSFTNGP10.phx.gbl...
> Hi
> I'd like to create a query which returns the MAX of a group of dates so
> long as the number is less than a given date. For example :
>
> SELECT MAX(date), username
> FROM mydatatable
> WHERE date < '01/01/2005'
> GROUP BY username
>
> Will this do what I expect and return the username and date which is the
> most recent before 01/01/2005 ?
> Thanks
> Andrew
>|||Andrew,

> Will this do what I expect and return the username and date which is the
> most recent before 01/01/2005 ?
It is correct, but it could be more than one. It will select each username
and the max date for those username with date values less than '20050101'. I
f
a username does not have date values in this range then it will not appear i
n
the result.
AMB
"Andrew Webb" wrote:

> Hi
> I'd like to create a query which returns the MAX of a group of dates so lo
ng
> as the number is less than a given date. For example :
>
> SELECT MAX(date), username
> FROM mydatatable
> WHERE date < '01/01/2005'
> GROUP BY username
>
> Will this do what I expect and return the username and date which is the
> most recent before 01/01/2005 ?
> Thanks
> Andrew
>
>

Friday, March 23, 2012

Pound Sign Problem

I have come across a bit of problem with my Web Form. I have standard textbox which inserts a value into a SQL 2000 database. However if I put a pound sign anywhere inside the text box and do the insert it disappears when I try and reshow the value.

I have checked the database field to see if it has been inputted but its no where to be seen.

So what has happened to my pound sign? any ideas?

I am using VS.Net 2003.

Thanks in advanceThat is strange. SQL 2000 does not reject the pound sign. Neither does the text box control. I did several tests, cannot seem to produce your problem. Do you do any kind of massage to your input in your code before it is insert into the database table?

Please post your code.|||Hiya

Sorry for the delay in getting back to you, please find below my code. This problem really does have me miffed

Cmd As New SqlCommand

Conn.Open()
With objCmd
.Connection = Conn
.CommandType = CommandType.Text
.CommandText = "INSERT INTO PhoneList (FirstName, Surname) " & _
" VALUES (@.FirstName, @.Surname)"

.Parameters.Clear()
.Parameters.Add("@.FirstName", SqlDbType.VarChar).Value = txtFirstName.Text
.Parameters.Add("@.Surname", SqlDbType.VarChar).Value = txtSurname.Text
.ExecuteNonQuery()
End With
Conn.Close()

Field types in theSQL Server 2000 DB are Varchar's. Just thought could this be one of the rasons for the roblem

Thanks in advance|||I have seen this problem before and I think the DataType on the column needs to beText to store ASCII characters.|||Hi PD

I have changed the data type to Text in my SQL DB but I still get the same problem

It just seems really odd, every other character works including !"$%^&*()

but the £ just does not get stored|||I was sleeping when I read your post, I thought you meant the # sign.

Themonetary data types MONEY and SMALLMONEY can accept many national currency symbols like the British pound, the Euro and others.|||How about if you use NVarChar instead?
Although you shouldn't have to go unicode in order to save the pound sign...

Regards
Fredrik|||Hi Fredrik

I have tried changing it to NvarChar and still didnt have any joy. I have even searched around on teh web and cant seem to find anybody else who has had that problem

Just wondering can anybody else replicate this problem there end?

I dont seem to have any hair left..... i think ive pulled it all out|||OK the only way I can seem to get round the problem is by inputting the HTML equvilant of a £ sign which is £

So when this is rendered it appears as a £

But im sure this cant be a solution.... can it?

I even tried to set validateRequest="false", but did not have any joy|||I am sorry. I thought it was # as well.
I did a test. Cannot re-produce the problem. I am using Varchar for the data field.

Did you try to debug and see whether the pound value was passed in correctly?|||Here is my test code in C#. I just have a textbox at the front end.


conn.Open ();
string strInsert = "Insert Into Chun (Test) Values (@.chun)";
SqlCommand cmd = new SqlCommand (strInsert, conn);
SqlParameter p = new SqlParameter("@.chun",SqlDbType.VarChar,50);
p.Value = txtPound.Text;
p.Direction = ParameterDirection.Input;
cmd.Parameters.Add (p);
cmd.ExecuteNonQuery ();
conn.Close ();

Here is the value I entered in the text box:


£5432

The value is stored and retrieve displayed as it is.|||I have a similar issue when I pass the # as part of a query string! It chops everything off after the pound sign!|||I did eventually manage to solve my pound sign problem, it was a problem with the globalization setting in the web.config.

Try reading the below article and see if it helps

http://www.howtodothings.com/showarticle.asp?article=755

Good luck

Friday, March 30, 2012

predicate locking

predicate locking

predicate locking

Wednesday, March 28, 2012

Precedence of MAX and WHERE

Friday, March 23, 2012

Pound Sign Problem

PORTS USED

Blog Archive

About Me