pessimistic vs optimistic concurrency control

Could someone explain to me what the difference between pessimistic (for examle 2PL) and optimistic concurrency control is?I have never heard of "2PL" before, but anyway:

Pessimistic means you actually lock the data when you select it to make sure nobody else can update it before you do. For example in Oracle you would SELECT ... FROM ... WHERE ... FOR UPDATE;

Optimistic means that you do not lock the data when you select it, but when you subsequently update it you check that it has not been updated by someone else in the meanwhile, otherwise your update fails. There are various ways to do this - record version numbers, last_update timestamps, or just check all the data values like this:

UPDATE ...
SET val1 = :new_val1, val2 = :new_val2, ...
WHERE key = :key
and val1 = :old_val1, val2 = :old_val2, ...;

(:old_val1, :new_val1 are variables holding the selected and modified values for column val1).

Pessimistic locking requires that a database session is maintained between the select and the update. In web-based applications, no database connection is maintained and so optimistic locking must be used.|||I suspect that mrmonkeyboy meant 2PC, a common abbreviation for two phase commit. I can see a slight resemblance between pessimistic locking and two phase commit... Both of them are intensely statefull.

-PatP|||Ok! Thanks a lot for the info!

-Mr Monkeyboy|||FYI: Two Phase Locking Protocol ( 2PL )

"In a given transaction, all locks precede all unlocks, i.e. once a transaction has released a lock it cannot acquire any more locks.

2PL guarantees serializability for all transactions that go to conclusion"|||Thanks, I hadn't come across that term before.

Permissions

Hi, could someone help regarding the Create, Alter, Drop and Control permissions.

For example, say I want to create a table on a particular database. How do I determine if the user has the correct 'Create Table' permissions?

In MSDN, it continually refers to (for example)

"

To set Table object properties, users must have ALTER permission on the table.

To create a table, users must have CREATE TABLE permission on the parent database and ALTER permission in the schema.

To drop a table, users must have CONTROL permission on the table, or be a member of the db_ddladmin or db_owner fixed database roles.

To grant, deny, or revoke permission on the table to other users, users must have CONTROL permission on the table.

"

I have tried using the EnumObjectPermissions and EnumDatabasePermissions methods of the Database object in SMO, but having no luck. Anyideas?

Thanks

DAn

EnumDatabasePermissions should indeed give you the 'statement' permissions that users have on that database. You can also look at the various roles, and enumerate members to see whether a user is part of a role, such as db_ddladmin. Could you elaborate on what is not working for you?|||

Right I am trying to create an application, where by a authorised users (SQL Auth) can create a table within a particular database. However, I need to check to ensure that the 'user' as the permissions to perform the task and I am not sure how to achieve this.

I have tried to use the EnumDatabasePermissions, but every time I try no results are returned (no matter which SQL account I used, have even tested with 'sa'). I may be using the EnumDatabasePermissions incorrectly, but everythink i try seems to give the same responce.

I have tried the different call methods of EnumDatabasePermissions in the local debug window of VS2005 (i.e. EnumDatabasePermissions () or EnumDatabasePermissions ("sa") ) but still getting result {Length='0'}

I am using SQL Server 2000 and 2005

The code I have so far is

Public Function Create() As Boolean Implements Interfaces.IDefinition.Create

Dim booSuccess As Boolean = False

' Ensure table name and structure have been defined

If (_dtTableStructure.Rows.Count <> 0) And (_strTableName.Length <> 0) Then

'Connect to the local, default instance of SQL Server.

Dim SQLServer As Server

Dim Database As Smo.Database

Dim Table As Smo.Table

Dim Column As Smo.Column

Dim Index As Smo.Index

Try

' Set Server connection information

SQLServer = New Server(_ServerConnection)

' Connect to server

_ServerConnection.Connect()

' Set Database

Database = SQLServer.Databases(_strDatabaseName)

' Check if database Is Accessible

If Database.IsAccessible = True Then

' Check to ensure table does not already exist

If Database.Tables.Contains(_strTableName) = False Then

' Define table name within specified database

Table = New Smo.Table(Database, _strTableName)

' Check if index already exists

If Table.Indexes.Contains("PK_" & _strTableName) = False Then

' Drop index

Table.Indexes("PK_" & _strTableName).Drop()

End If

Index = New Index(Table, "PK_" & _strTableName)

Table.Indexes.Add(Index)

' Loop though defined table structure

For Each Row As DataRow In _dtTableStructure.Rows

' Column Name

Column = New Column(Table, Row.Item("ColumnName").ToString)

' DataType

Select Case CType(Row.Item("DataTypeID"), _Global.eDataTypes)

Case _Global.eDataTypes.bigint

Column.DataType = DataType.BigInt

Case _Global.eDataTypes.int

Column.DataType = DataType.Int

Case _Global.eDataTypes.smallint

Column.DataType = DataType.SmallInt

Case _Global.eDataTypes.tinyint

Column.DataType = DataType.TinyInt

Case _Global.eDataTypes.bit

Column.DataType = DataType.Bit

Case _Global.eDataTypes.decimal

Column.DataType = DataType.Decimal(0, 18)

Case _Global.eDataTypes.numeric

Column.DataType = DataType.Numeric(0, 18)

Case _Global.eDataTypes.money

Column.DataType = DataType.Money

Case _Global.eDataTypes.smallmoney

Column.DataType = DataType.SmallMoney

Case _Global.eDataTypes.float

Column.DataType = DataType.Float

Case _Global.eDataTypes.real

Column.DataType = DataType.Real

Case _Global.eDataTypes.datetime

Column.DataType = DataType.DateTime

Case _Global.eDataTypes.smalldatetime

Column.DataType = DataType.SmallDateTime

Case _Global.eDataTypes.char

Column.DataType = DataType.Char(Row.Item("Length").ToString)

Case _Global.eDataTypes.varchar

Column.DataType = DataType.VarChar(Row.Item("Length").ToString)

Case _Global.eDataTypes.text

Column.DataType = DataType.Text

Case _Global.eDataTypes.nchar

Column.DataType = DataType.NChar(Row.Item("Length").ToString)

Case _Global.eDataTypes.nvarchar

Column.DataType = DataType.NVarChar(Row.Item("Length").ToString)

Case _Global.eDataTypes.ntext

Column.DataType = DataType.NText

Case _Global.eDataTypes.binary

Column.DataType = DataType.Binary(Row.Item("Length").ToString)

Case _Global.eDataTypes.varbinary

Column.DataType = DataType.VarBinary(Row.Item("Length").ToString)

Case _Global.eDataTypes.image

Column.DataType = DataType.Image

Case _Global.eDataTypes.sql_variant

Column.DataType = DataType.Variant

Case _Global.eDataTypes.timestamp

Column.DataType = DataType.Timestamp

Case _Global.eDataTypes.uniqueidentifier

Column.DataType = DataType.UniqueIdentifier

End Select

' IsNullable

Column.Nullable = CType(Row.Item("AllowNulls"), Boolean)

Table.Columns.Add(Column)

' Primary Key

If CType(Row.Item("PrimaryKey"), Boolean) = True Then

' Add the primary key index

Index.IndexedColumns.Add(New IndexedColumn(Index, Column.Name))

Index.IsClustered = True

Index.IsUnique = True

Index.IndexKeyType = IndexKeyType.DriPrimaryKey

End If

Next

Try

' Attempt to create table

Table.Create()

' Success

booSuccess = True

Catch ex As Exception

' Set ExceptionMessage member variable

_objException = ex

End Try

Else

Dim ex As New Exception("test")

Throw ex

End If

End If

Catch ex As Exception

' Set ExceptionMessage member variable

_objException = ex

Finally

_ServerConnection.Disconnect()

End Try

End If

Return booSuccess

End Function

Permission VS Owner

Why every securable has an owner?
A securable's owner is some one has full control over the securable.
so the term "owner" can be replaced by some one who has full control
over securable.Hi Benny
That may usually be the initial case, but then you can also deny permissions
to the owner so they don't have full control. Others can also be granted
CONTROL permission that gives them owner-like permissions, but they are not
the owner.
John
"Benny" wrote:
> Why every securable has an owner?
> A securable's owner is some one has full control over the securable.
> so the term "owner" can be replaced by some one who has full control
> over securable.
>|||Thanks John.
If permission can handle security thing, why SQL Server provide
object's owner?
Sql Server can works well with permission and without owner.
On Dec 23, 7:33 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi Benny
> That may usually be the initial case, but then you can also deny permissions
> to the owner so they don't have full control. Others can also be granted
> CONTROL permission that gives them owner-like permissions, but they are not
> the owner.
> John
>
> "Benny" wrote:
> > Why every securable has an owner?
> > A securable's owner is some one has full control over the securable.
> > so the term "owner" can be replaced by some one who has full control
> > over securable.- Hide quoted text -- Show quoted text -|||Hi Benny
I can't say for certain, and someone else may be able to give you a better
reply... but ownership tends to be more to do with management of objects than
security.
John
"Benny" wrote:
> Thanks John.
> If permission can handle security thing, why SQL Server provide
> object's owner?
> Sql Server can works well with permission and without owner.
> On Dec 23, 7:33 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> > Hi Benny
> >
> > That may usually be the initial case, but then you can also deny permissions
> > to the owner so they don't have full control. Others can also be granted
> > CONTROL permission that gives them owner-like permissions, but they are not
> > the owner.
> >
> > John
> >
> >
> >
> > "Benny" wrote:
> > > Why every securable has an owner?
> > > A securable's owner is some one has full control over the securable.
> > > so the term "owner" can be replaced by some one who has full control
> > > over securable.- Hide quoted text -- Show quoted text -
>|||This is true. On SQL-Server 2000, you can have multiple objects with the
same name but with different owners; for example: dbo.table1, user1.table1,
user2.table1, ... When the owner (user) of an object is the same as the
owner of the database then dbo. is used. Also, only the database owner or
the object owner can define or change the permissions of an object; for
exemple, only dbo or user1 can change the permissions of the object
user1.table1 while user2 cannot.
For SQL-Server 2000, the owner of an object is also called the schema, to be
on par with other sql-servers (Oracle for exemple). However, this is not a
true sql schema because it can't be hierarchical.
On SQL-Server 2005, with have true schemas because not only they can be
hierarchical but also the name of the schema is not necessarily the same as
the name of the owner.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:390699C8-1F0E-4564-BDDC-CC29AB7A5E10@.microsoft.com...
> Hi Benny
> I can't say for certain, and someone else may be able to give you a better
> reply... but ownership tends to be more to do with management of objects
> than
> security.
> John
> "Benny" wrote:
>> Thanks John.
>> If permission can handle security thing, why SQL Server provide
>> object's owner?
>> Sql Server can works well with permission and without owner.
>> On Dec 23, 7:33 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
>> > Hi Benny
>> >
>> > That may usually be the initial case, but then you can also deny
>> > permissions
>> > to the owner so they don't have full control. Others can also be
>> > granted
>> > CONTROL permission that gives them owner-like permissions, but they are
>> > not
>> > the owner.
>> >
>> > John
>> >
>> >
>> >
>> > "Benny" wrote:
>> > > Why every securable has an owner?
>> > > A securable's owner is some one has full control over the securable.
>> > > so the term "owner" can be replaced by some one who has full control
>> > > over securable.- Hide quoted text -- Show quoted text -
>>|||I still can not understand why ownership exist in SQL Server
Sylvain Lafontaine (fill the blanks, no spam please) wrote:
> This is true. On SQL-Server 2000, you can have multiple objects with the
> same name but with different owners; for example: dbo.table1, user1.table1,
> user2.table1, ... When the owner (user) of an object is the same as the
> owner of the database then dbo. is used. Also, only the database owner or
> the object owner can define or change the permissions of an object; for
> exemple, only dbo or user1 can change the permissions of the object
> user1.table1 while user2 cannot.
> For SQL-Server 2000, the owner of an object is also called the schema, to be
> on par with other sql-servers (Oracle for exemple). However, this is not a
> true sql schema because it can't be hierarchical.
> On SQL-Server 2005, with have true schemas because not only they can be
> hierarchical but also the name of the schema is not necessarily the same as
> the name of the owner.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:390699C8-1F0E-4564-BDDC-CC29AB7A5E10@.microsoft.com...
> > Hi Benny
> >
> > I can't say for certain, and someone else may be able to give you a better
> > reply... but ownership tends to be more to do with management of objects
> > than
> > security.
> >
> > John
> >
> > "Benny" wrote:
> >
> >> Thanks John.
> >> If permission can handle security thing, why SQL Server provide
> >> object's owner?
> >> Sql Server can works well with permission and without owner.
> >>
> >> On Dec 23, 7:33 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> >> > Hi Benny
> >> >
> >> > That may usually be the initial case, but then you can also deny
> >> > permissions
> >> > to the owner so they don't have full control. Others can also be
> >> > granted
> >> > CONTROL permission that gives them owner-like permissions, but they are
> >> > not
> >> > the owner.
> >> >
> >> > John
> >> >
> >> >
> >> >
> >> > "Benny" wrote:
> >> > > Why every securable has an owner?
> >> > > A securable's owner is some one has full control over the securable.
> >> > > so the term "owner" can be replaced by some one who has full control
> >> > > over securable.- Hide quoted text -- Show quoted text -
> >>
> >>|||This is for giving the possibility for two different users to access two
different sets of data from within the same application: if multiple objects
with the same name exists and you don't specify the schema (or owner), SQL
Server will first search for an object belonging to the user and if it don't
find one, for an object belonging to the owner of the database (dbo.).
For example, take the case where three views exists with the same name:
UserA.View1, UserB.View1 and dbo.View1. If an application ask for View1 but
without making an explicit reference to the the ownership, SQL-Server will
automatically assign the use of UserA.View1 for user UserA; UserB.View1 for
UserB and dbo.View1 for all other users.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Benny" <wuyuebing@.gmail.com> wrote in message
news:1167011614.441378.271850@.42g2000cwt.googlegroups.com...
>I still can not understand why ownership exist in SQL Server
> Sylvain Lafontaine (fill the blanks, no spam please) wrote:
>> This is true. On SQL-Server 2000, you can have multiple objects with the
>> same name but with different owners; for example: dbo.table1,
>> user1.table1,
>> user2.table1, ... When the owner (user) of an object is the same as the
>> owner of the database then dbo. is used. Also, only the database owner
>> or
>> the object owner can define or change the permissions of an object; for
>> exemple, only dbo or user1 can change the permissions of the object
>> user1.table1 while user2 cannot.
>> For SQL-Server 2000, the owner of an object is also called the schema, to
>> be
>> on par with other sql-servers (Oracle for exemple). However, this is not
>> a
>> true sql schema because it can't be hierarchical.
>> On SQL-Server 2005, with have true schemas because not only they can be
>> hierarchical but also the name of the schema is not necessarily the same
>> as
>> the name of the owner.
>> --
>> Sylvain Lafontaine, ing.
>> MVP - Technologies Virtual-PC
>> E-mail: sylvain aei ca (fill the blanks, no spam please)
>>
>> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> news:390699C8-1F0E-4564-BDDC-CC29AB7A5E10@.microsoft.com...
>> > Hi Benny
>> >
>> > I can't say for certain, and someone else may be able to give you a
>> > better
>> > reply... but ownership tends to be more to do with management of
>> > objects
>> > than
>> > security.
>> >
>> > John
>> >
>> > "Benny" wrote:
>> >
>> >> Thanks John.
>> >> If permission can handle security thing, why SQL Server provide
>> >> object's owner?
>> >> Sql Server can works well with permission and without owner.
>> >>
>> >> On Dec 23, 7:33 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
>> >> > Hi Benny
>> >> >
>> >> > That may usually be the initial case, but then you can also deny
>> >> > permissions
>> >> > to the owner so they don't have full control. Others can also be
>> >> > granted
>> >> > CONTROL permission that gives them owner-like permissions, but they
>> >> > are
>> >> > not
>> >> > the owner.
>> >> >
>> >> > John
>> >> >
>> >> >
>> >> >
>> >> > "Benny" wrote:
>> >> > > Why every securable has an owner?
>> >> > > A securable's owner is some one has full control over the
>> >> > > securable.
>> >> > > so the term "owner" can be replaced by some one who has full
>> >> > > control
>> >> > > over securable.- Hide quoted text -- Show quoted text -
>> >>
>> >>
>