Bypassing locks when doing insert or update

Hi,

I want to bypass locks while doing Insert or Update. I am only updating a log db and I don't care about one or two fields getting junk as I won't use it later (atleast as long as I am working with my current company ;) )

I am using MS SQL 2000

I am getting too many deadlocks and messages like these

"Process ID was deadlocked with another process and has been chosen a victim. Please rerun the transaction".

Please tell me how to achieve this.

Regards,
Noorul

you can try using a 'NOLOCK' locking hint....but u have to be sure..it wont affect ur data (ACID)....

correct..and longer part will be to try and find the source of deadlocks..and remove it..

|||Have a look to see what you have in the way of indexes on your table, particularly your clustered index. Hopefully you have an id field which only increments as your clustered index, so that it never tries to move the pages around, and your inserts can just jump straight in and out again.

Updates shouldn't have to be any different - make sure you have a good indexing strategy so that the system doesn't try to lock more of the table than it needs to.

Why are you updating a log anyway? And should I assume you mean 'audit' ?

Rob|||You absolutely CANNOT bypass locking when doing UPDATES or INSERTS or DELETES. Nor would you want to. You are getting deadlocks because your code is accessing the data in differring order, you are holding transactions open too long, and/or you are not using NOLOCK hints on your SELECT statements where appropriate.|||

Please take a look at the links below on how to resolve deadlocks. You can't eliminate them by just using locking hints in your various statements. Deadlocks are typically due to errors in your execution logic.

http://support.microsoft.com/kb/832524/

http://msdn2.microsoft.com/en-us/library/aa937573(SQL.80).aspx

|||Thanks All

I read about the clustered Index from Microsoft kb 169960. I created the clustered index with 70 fill factor. It has reduced the deadlocks down to zero. Actually I have an application that has 10 threads logging the sent SMS messages into same table. My application don't read from it, it just does insert. So far, so good. With 70 fill factor, how will that affect the memory consumption?

Regards,
Noorul

Bypassing locks when doing insert or update

Hi,

I want to bypass locks while doing Insert or Update. I am only updating a log db and I don't care about one or two fields getting junk as I won't use it later (atleast as long as I am working with my current company ;) )

I am using MS SQL 2000

I am getting too many deadlocks and messages like these

"Process ID was deadlocked with another process and has been chosen a victim. Please rerun the transaction".

Please tell me how to achieve this.

Regards,
Noorul

you can try using a 'NOLOCK' locking hint....but u have to be sure..it wont affect ur data (ACID)....

correct..and longer part will be to try and find the source of deadlocks..and remove it..

|||Have a look to see what you have in the way of indexes on your table, particularly your clustered index. Hopefully you have an id field which only increments as your clustered index, so that it never tries to move the pages around, and your inserts can just jump straight in and out again.

Updates shouldn't have to be any different - make sure you have a good indexing strategy so that the system doesn't try to lock more of the table than it needs to.

Why are you updating a log anyway? And should I assume you mean 'audit' ?

Rob|||You absolutely CANNOT bypass locking when doing UPDATES or INSERTS or DELETES. Nor would you want to. You are getting deadlocks because your code is accessing the data in differring order, you are holding transactions open too long, and/or you are not using NOLOCK hints on your SELECT statements where appropriate.|||

Please take a look at the links below on how to resolve deadlocks. You can't eliminate them by just using locking hints in your various statements. Deadlocks are typically due to errors in your execution logic.

http://support.microsoft.com/kb/832524/

http://msdn2.microsoft.com/en-us/library/aa937573(SQL.80).aspx

|||Thanks All

I read about the clustered Index from Microsoft kb 169960. I created the clustered index with 70 fill factor. It has reduced the deadlocks down to zero. Actually I have an application that has 10 threads logging the sent SMS messages into same table. My application don't read from it, it just does insert. So far, so good. With 70 fill factor, how will that affect the memory consumption?

Regards,
Noorul

by vb.net create exe File to update sql server

is there any chance to create an exe file to update the sql server database by uisng windows schdule ?

for example this exe file will run to update my database, every night @. 12:00 AM.

this exe should be in vb.net

pllllzzzz help

What is your design for that ? Do you want to execute DDL or DML or just maintainance on the database ? You might check the option of the SQL Server Agent, which does Scheduling for SQL Server. More information would be helpful to help you.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

You can also VBScript from SQL Agent…

Button1_click code behind

Hi, I have the following code which creates a dropdown box with values from the database.

1) I would like an sql statement to take place to update the database. When the button is clicked I would like the database to update so 'doc_area_default' is changed to '1' for the value that is selected via the dropdown. All others should be changed to '0'. Then direct the user to ManageAreas.aspx with a success message.

2) I would like to be able to do this in the code behind page (cs). Using the "protectedvoid Button1_Click(object sender,EventArgs e)" method. I'm not sure how to do the database connection like I would in the aspx page either.

Thanks for any help you can give!

<

asp:DropDownListID="DropDownList1"runat="server"DataSourceID="SqlDataSource1"DataTextField="doc_area_name"DataValueField="doc_area_id"></asp:DropDownList><asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:CPS_docshareConnectionString %>"SelectCommand="SELECT * FROM [document_area] WHERE [doc_area_type] = 1 ORDER BY [doc_area_default] DESC"></asp:SqlDataSource>

<

asp:ButtonID="Button1"runat="server"Text="Update"OnClick="Button1_Click"/>

Dearmlawton40 .

I guess u can use store procedure to do this.

Try to use these code

//Create procedure

create proc sp_update

@.doc_area_id int

as

update document_area set doc_area_type=1 where [doc_area_id] = @.doc_area_id

update document_area set doc_area_type=0 where [doc_area_id] <> @.doc_area_id

go

And Code behind your page

public void Button1_Click()
{

SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["CPS_docshareConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand("sp_update", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@.doc_area_id",DropDownList1.selectedvalue));
cmd.Connection.Open();
cmd.ExecuteNonQuery();

Response.Redirect("ManageAreas.aspx");
}

Hope that it will help you..

but...

hi,
thanks for replying me.
increasing (decreasing too?) the WIDTH of a row because of
an update is a new information for me, and i would also
say it is quite shocking for me.
i allways thought table structure is a fixed-width, so the
space for all of the fields is allocated in the same time
and PLACE in advance for each inserted record.
or maybe do you mean that the index may be compuond by
varchar fields so changing the values may change the
lenght of the compound index-value, for example an index-
value coumpound by 2 fields may be changed from "X"+"A"
to "X"+"ABCDEF"
(so if, for example, the table contains integer fields
only, is Fill-factor 100 still be the optimal option even
when updates are concerned)
i guess it's a long story to explain why a clustered index
is actually needs to contain ALL fileds ...?
i probablly missing something (or a lot of things)...
thanks again.
edo.

>--Original Message--
>Not quite true.
>You're on the right track as far as inserts are
concerned, but updates are
>another matter.
>Because clustered indexes contain ALL columns, any
updates that increase the
>width of the row might cause a page split.
>Therefore, whether 100% is optimal or not depends on
whether there are any
>updates to the table.
>HTH
>Regards,
>Greg Linwood
>SQL Server MVP
>"edo" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:1c2db01c4528a$671b1890$a601280a@.phx.gbl...
auto-[vbcol=seagreen]
places[vbcol=seagreen]
new
>
>.
>
Hi Edo,
On Mon, 14 Jun 2004 21:40:34 -0700, edo wrote:

>hi,
>thanks for replying me.
>increasing (decreasing too?) the WIDTH of a row because of
>an update is a new information for me, and i would also
>say it is quite shocking for me.
>i allways thought table structure is a fixed-width, so the
>space for all of the fields is allocated in the same time
>and PLACE in advance for each inserted record.
This is only true if the row contains no varying length columns. Each
table that holds at least one varchar, nvarchar or varbinary column has
rows with varying length.
(snip)
>i guess it's a long story to explain why a clustered index
>is actually needs to contain ALL fileds ...?
Not at all. The clustered index determines the order in which rows are
stored in the data file. Suppose you have a clustered index on an integer
column, there are rows with values 1 and 3 for that column and you then
insert a row with value 2. In that case, the database will store the
entire row between the rows vor key value 1 and 3, not only the key value.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hugo's answered most of your questions already here but I'll just answer
that qn you put about whether 100% fillfactor is optimal if all columns are
fixed width such as integer. I'd say that that answer to that is yes - if
the columns are fixed width, then the underlying storage requirements will
never grow for a given row, so there should not be any requirement to split
storage pages. Even if there is some obscure cause of page splits in this
scenario, I'd suggest that would be rare & therefore the 100% fillfactor
would still be optimal.
Regards,
Greg Linwood
SQL Server MVP
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:aeatc0poos36e86c2tcthsforql0rbs5lk@.4ax.com...
> Hi Edo,
> On Mon, 14 Jun 2004 21:40:34 -0700, edo wrote:
>
> This is only true if the row contains no varying length columns. Each
> table that holds at least one varchar, nvarchar or varbinary column has
> rows with varying length.
>
> (snip)
> Not at all. The clustered index determines the order in which rows are
> stored in the data file. Suppose you have a clustered index on an integer
> column, there are rows with values 1 and 3 for that column and you then
> insert a row with value 2. In that case, the database will store the
> entire row between the rows vor key value 1 and 3, not only the key value.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

but...

hi,
thanks for replying me.
increasing (decreasing too?) the WIDTH of a row because of
an update is a new information for me, and i would also
say it is quite shocking for me.
i allways thought table structure is a fixed-width, so the
space for all of the fields is allocated in the same time
and PLACE in advance for each inserted record.
or maybe do you mean that the index may be compuond by
varchar fields so changing the values may change the
lenght of the compound index-value, for example an index-
value coumpound by 2 fields may be changed from "X"+"A"
to "X"+"ABCDEF"
(so if, for example, the table contains integer fields
only, is Fill-factor 100 still be the optimal option even
when updates are concerned)
i guess it's a long story to explain why a clustered index
is actually needs to contain ALL fileds ...?
i probablly missing something (or a lot of things)...
thanks again.
edo.

>--Original Message--
>Not quite true.
>You're on the right track as far as inserts are
concerned, but updates are
>another matter.
>Because clustered indexes contain ALL columns, any
updates that increase the
>width of the row might cause a page split.
>Therefore, whether 100% is optimal or not depends on
whether there are any
>updates to the table.
>HTH
>Regards,
>Greg Linwood
>SQL Server MVP
>"edo" <anonymous@.discussions.microsoft.com> wrote in
message
> news:1c2db01c4528a$671b1890$a601280a@.phx
.gbl...
auto-[vbcol=seagreen]
places[vbcol=seagreen]
new[vbcol=seagreen]
>
>.
>Hi Edo,
On Mon, 14 Jun 2004 21:40:34 -0700, edo wrote:

>hi,
>thanks for replying me.
>increasing (decreasing too?) the WIDTH of a row because of
>an update is a new information for me, and i would also
>say it is quite shocking for me.
>i allways thought table structure is a fixed-width, so the
>space for all of the fields is allocated in the same time
>and PLACE in advance for each inserted record.
This is only true if the row contains no varying length columns. Each
table that holds at least one varchar, nvarchar or varbinary column has
rows with varying length.
(snip)
>i guess it's a long story to explain why a clustered index
>is actually needs to contain ALL fileds ...?
Not at all. The clustered index determines the order in which rows are
stored in the data file. Suppose you have a clustered index on an integer
column, there are rows with values 1 and 3 for that column and you then
insert a row with value 2. In that case, the database will store the
entire row between the rows vor key value 1 and 3, not only the key value.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo's answered most of your questions already here but I'll just answer
that qn you put about whether 100% fillfactor is optimal if all columns are
fixed width such as integer. I'd say that that answer to that is yes - if
the columns are fixed width, then the underlying storage requirements will
never grow for a given row, so there should not be any requirement to split
storage pages. Even if there is some obscure cause of page splits in this
scenario, I'd suggest that would be rare & therefore the 100% fillfactor
would still be optimal.
Regards,
Greg Linwood
SQL Server MVP
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:aeatc0poos36e86c2tcthsforql0rbs5lk@.
4ax.com...
> Hi Edo,
> On Mon, 14 Jun 2004 21:40:34 -0700, edo wrote:
>
> This is only true if the row contains no varying length columns. Each
> table that holds at least one varchar, nvarchar or varbinary column has
> rows with varying length.
>
> (snip)
> Not at all. The clustered index determines the order in which rows are
> stored in the data file. Suppose you have a clustered index on an integer
> column, there are rows with values 1 and 3 for that column and you then
> insert a row with value 2. In that case, the database will store the
> entire row between the rows vor key value 1 and 3, not only the key value.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

business process logic in stored procedures

Hi All,
I've always tried to think of and use sql as being a tool to quickly
retrieve / update relevant data, and let other parts of a system handle
the decisions as to what needs to be done to the data. However I keep
coming across (and sometimes find myself creating) the situation where
there are massive stored procedures which will have several different
statements, pulling data from loads of tables and updating others based
on some business logic.
First question: is this a bad thing?
as I see it this has the advantage that updates can be expressed as a
function to be applied over a whole table, making the process much
faster than if the data was changed in a business layer, then
propogated to the database. However the "code" is hard for someone else
to understand, and hard to re-use/improve. I often find that processes
which would be modelled with quite a large framework of objects are
condensed down into a large stored procedure, such that to anyone else
looking at it will just see a mass of update insert and selects with no
idea why.
Second question: when to do it?
I have found that at times it's unaviodable, either for performance or
simply the ease of access to all the data that I need to use a stored
proc, does anyone have any rules of thumb as to when it's a good/bad
idea?
Third question: what are the alternatives?
I'd hope that there are other ways to get around the problems that
people are solving using sql, does anyone have any links/suggestions of
how to approach things that people would often resort to sql for, using
more maintainable methods?
I could probably rattle on for days on this issue, but I'm hoping maybe
people will be able to suggest some best practice about this.
Cheers
WillWill
1),2)
I remember some times ago it was discussion about this subject and some
people say that they put the business logic in the stored procedure and
some people say they do not but only code/dll....
I have been praticipate in some projects where we put all login in to
stored procedure and it was relaible/readable and worder very good in terms
of perfomance as well
So the answer will be it depends on YOUR project's business logic and sure
if you can test 'somehow' and make the right decision
3) Well if you develop multi tier application the question is where to put
BL in data layer (dll that access to the database) or directly to stored
procedures
Again , I have seen many projects where people (including me) put the logic
into SP and some projects where people put the BL (including me) in the
code, so it is really DEPENDS on many things.
If you are lucky and Erlan ( and many others here at forum) jump in , it is
interesting to see what does he suggest ?
"Will" <william_pegg@.yahoo.co.uk> wrote in message
news:1144324357.527781.15400@.e56g2000cwe.googlegroups.com...
> Hi All,
> I've always tried to think of and use sql as being a tool to quickly
> retrieve / update relevant data, and let other parts of a system handle
> the decisions as to what needs to be done to the data. However I keep
> coming across (and sometimes find myself creating) the situation where
> there are massive stored procedures which will have several different
> statements, pulling data from loads of tables and updating others based
> on some business logic.
> First question: is this a bad thing?
> as I see it this has the advantage that updates can be expressed as a
> function to be applied over a whole table, making the process much
> faster than if the data was changed in a business layer, then
> propogated to the database. However the "code" is hard for someone else
> to understand, and hard to re-use/improve. I often find that processes
> which would be modelled with quite a large framework of objects are
> condensed down into a large stored procedure, such that to anyone else
> looking at it will just see a mass of update insert and selects with no
> idea why.
> Second question: when to do it?
> I have found that at times it's unaviodable, either for performance or
> simply the ease of access to all the data that I need to use a stored
> proc, does anyone have any rules of thumb as to when it's a good/bad
> idea?
> Third question: what are the alternatives?
> I'd hope that there are other ways to get around the problems that
> people are solving using sql, does anyone have any links/suggestions of
> how to approach things that people would often resort to sql for, using
> more maintainable methods?
> I could probably rattle on for days on this issue, but I'm hoping maybe
> people will be able to suggest some best practice about this.
> Cheers
> Will
>