Locks [Programming SQL]

Prev: Check Reporting Services Installation Programmitcally
Next: TransactionAbortedException Occurs Randomly

From: Marek on 22 Jul 2008 03:43

On Jul 22, 8:33 am, "Uri Dimant" <u...(a)iscar.co.il> wrote:
> Marek
> What is an output? Do you have any indexes define on the tables?
>
> ELECT resource_type, resource_description,
>
> resource_associated_entity_id, request_mode, request_status
>
> FROM sys.dm_tran_locks
>
> WHERE resource_associated_entity_id > 0
>
> "Marek Grolms" <gro...(a)volny.cz> wrote in message
>
> news:ev2rht76IHA.1468(a)TK2MSFTNGP05.phx.gbl...
>
>
>
> > HI
> > I have webservice which delete , insert a update data from sql database..
> > sqlcon.Open();
> > sqlcom.CommandText = string.Format("delete from do_denni WITH (ROWLOCK)
> > where oscislo = {0} and convert(datetime,'{1}',104) <= datum and
> > DATEADD(dd,1,convert(datetime,'{2}',104)) <= datum ", oscislo,
> > datumod.ToString("dd.MM.yyyy"), datumdo.ToString("dd.MM.yyyy"));
> > sqlcom.ExecuteNonQuery();
>
> > in delete i delete rows for person with specific date range .... After
> > delete this I insert new rows . The webservice is called 50x in the same
> > time.....
>
> > thanx
>
> > "Andrew J. Kelly" <sqlmvpnooos...(a)shadhawk.com> wrote in message
> >news:eMCOG936IHA.3672(a)TK2MSFTNGP04.phx.gbl...
> >> Can you be more specific? What do you mean 50x delete? SQL Server code
> >> just like nay other code is basically procedural in which the first
> >> command needs to finish before the second one can start. How exactly are
> >> you calling these deletes?
>
> >> --
> >> Andrew J. Kelly SQL MVP
> >> Solid Quality Mentors
>
> >> "Marek Grolms" <gro...(a)volny.cz> wrote in message
> >>news:u$LxAx36IHA.2336(a)TK2MSFTNGP03.phx.gbl...
> >>> Hi ,
> >>> I have some quesation about Locks in the sql 2005.In the application I
> >>> call 50x delete where I delete from table rows with specific range
> >>> datetime.Why this command locks second delete and not run paralel ?
>
> >>> thanx- Hide quoted text -
>
> - Show quoted text -

processor works on 100% permanently

KEY
(7901ef738398)
72057594043498496 U GRANT
KEY
(780101dc368a)
72057594043498496 U GRANT
KEY
(7201653c8be5)
72057594043498496 U GRANT
KEY
(750157cc55d2)
72057594043498496 U GRANT
KEY
(7401b963e0c0)
72057594043498496 U GRANT
KEY
(6f018b933ef7)
72057594043498496 U GRANT
KEY
(7401e2d74e5a)
72057594043432960 X GRANT
KEY
(76013e88257f)
72057594043432960 X GRANT
KEY
(8401e635f030)
72057594043432960 X GRANT
PAGE
1:76444
72057594042974208 IU GRANT
KEY
(8101089a4522)
72057594043432960 X GRANT
KEY
(86013a6a9b15)
72057594043432960 X GRANT
KEY
(8701d4c52e07)
72057594043432960 X GRANT
KEY
(8d01b0259368)
72057594043432960 X GRANT
KEY
(8a0182d54d5f)
72057594043432960 X GRANT
KEY
(8b016c7af84d)
72057594043432960 X GRANT
KEY
(90015e8a267a)
72057594043432960 X GRANT
KEY
(7e0149e210bb)
72057594043432960 X GRANT
KEY
(7f01a74da5a9)
72057594043432960 X GRANT
KEY
(7c0195bd7b9e)
72057594043432960 X GRANT
KEY
(9501c05a3efd)
72057594043432960 X GRANT
PAGE
1:76457
72057594042974208 IU GRANT
PAGE
1:76458
72057594042974208 IU GRANT
KEY
(93011c0555d8)
72057594043432960 X GRANT
KEY
(9201f2aae0ca)
72057594043432960 X GRANT
KEY
(5401a1d81883)
72057594043498496 X GRANT
KEY
(5a017d8773a6)
72057594043498496 X GRANT
KEY
(7401323de3cc)
72057594043498496 U GRANT
KEY
(4c01d1a7b516)
72057594043498496 X GRANT
KEY
(7101dc9256de)
72057594043498496 U GRANT
KEY
(52010df8de33)
72057594043498496 X GRANT
KEY
(7601ee6288e9)
72057594043498496 U GRANT
KEY
(770100cd3dfb)
72057594043498496 U GRANT
KEY
(48016918635c)
72057594043498496 X GRANT
KEY
(7d01642d8094)
72057594043498496 U GRANT
KEY
(7a0156dd5ea3)
72057594043498496 U GRANT
OBJECT
1826105546 Sch-S GRANT
OBJECT
1826105546 Sch-S GRANT
OBJECT
1826105546 Sch-S GRANT
OBJECT
1826105546 Sch-S GRANT
OBJECT
1826105546 Sch-S GRANT
KEY
(4601b5470879)
72057594043498496 X GRANT
KEY
(7b01b872ebb1)
72057594043498496 U GRANT
KEY
(0201be3304cb)
72057594043498496 X GRANT
KEY
(0401626c6fee)
72057594043498496 X GRANT
KEY
(4301cf89040d)
72057594043498496 X GRANT
KEY
(f000a95b320a)
72057594043498496 X GRANT
KEY
(f6007504592f)
72057594043498496 X GRANT
KEY
(fc0011e4e440)
72057594043498496 X GRANT
KEY
(fa00cdbb8f65)
72057594043498496 X GRANT
KEY
(ee00057bf4ba)
72057594043498496 X GRANT
KEY
(8f013bed9ac2)
72057594043432960 X GRANT
KEY
(8e01d5422fd0)
72057594043432960 X GRANT
KEY
(8901e7b2f1e7)
72057594043432960 X GRANT
KEY
(8c01091d44f5)
72057594043432960 X GRANT
KEY
(830183524c88)
72057594043432960 X GRANT
KEY
(82016dfdf99a)
72057594043432960 X GRANT
KEY
(8801b1a292bf)
72057594043432960 X GRANT
KEY
(85015f0d27ad)
72057594043432960 X GRANT
KEY
(9601a53d8245)
72057594043432960 X GRANT
KEY
(94017962e960)
72057594043432960 X GRANT
KEY
(910197cd5c72)
72057594043432960 X GRANT
KEY
(7d012c85ac03)
72057594043432960 X GRANT
KEY
(7b01f0dac726)
72057594043432960 X GRANT
KEY
(7b01f0dac726)
72057594043432960 U WAIT
KEY
(7b01f0dac726)
72057594043432960 U WAIT
KEY
(7b01f0dac726)
72057594043432960 U WAIT
KEY
(7b01f0dac726)
72057594043432960 U WAIT
KEY
(7b01f0dac726)
72057594043432960 U WAIT
KEY
(7b01f0dac726)
72057594043432960 U WAIT
KEY
(7b01f0dac726)
72057594043432960 U WAIT
KEY
(5c014ef0ac46)
72057594043498496 X GRANT
KEY
(8001c22a1911)
72057594043432960 X GRANT
KEY
(56012a101129)
72057594043498496 X GRANT
KEY
(700115d52cd6)
72057594043498496 U GRANT
KEY
(700115d52cd6)
72057594043498496 U WAIT
KEY
(5801f64f7a0c)
72057594043498496 X GRANT
KEY
(4a01e2d06af6)
72057594043498496 X GRANT
KEY
(790133bae21b)
72057594043498496 U GRANT
KEY
(44013e8f01d3)
72057594043498496 X GRANT
KEY
(7c01dd155709)
72057594043498496 U GRANT
KEY
(50018630d799)
72057594043498496 X GRANT
KEY
(7301575a5f74)
72057594043498496 U GRANT
KEY
(4e015a6fbcbc)
72057594043498496 X GRANT
KEY
(7201b9f5ea66)
72057594043498496 U GRANT
KEY
(780165aa8143)
72057594043498496 U GRANT
KEY
(75018b053451)
72057594043498496 U GRANT
PAGE
5:10991
72057594043432960 IU GRANT
PAGE
5:10991
72057594043432960 IU GRANT
PAGE
5:10991
72057594043432960 IU GRANT
PAGE
5:10991
72057594043432960 IU GRANT
PAGE
5:10991
72057594043432960 IU GRANT
PAGE
5:10991
72057594043432960 IU GRANT
PAGE
5:10991
72057594043432960 IX GRANT
PAGE
5:10991
72057594043432960 IU GRANT
PAGE
5:2727
72057594042646528 IU GRANT
KEY
(7001eef4824f)
72057594043498496 U GRANT
KEY
(7101005b375d)
72057594043498496 U GRANT
KEY
(760132abe96a)
72057594043498496 U GRANT
KEY
(7301dc045c78)
72057594043498496 U GRANT
KEY
(770164bb8a32)
72057594043498496 U GRANT
KEY
(7a018a143f20)
72057594043498496 U GRANT
KEY
(6e0142d444ff)
72057594043498496 U GRANT
KEY
(7201691f47f0)
72057594043432960 X GRANT
KEY
(7201691f47f0)
72057594043432960 U WAIT
KEY
(7201691f47f0)
72057594043432960 U WAIT
KEY
(7801b5402cd5)
72057594043432960 X GRANT
KEY
(fe009a2cedea)
72057594043498496 X GRANT
KEY
(f800467386cf)
72057594043498496 X GRANT
KEY
(f20022933ba0)
72057594043498496 X GRANT
KEY
(f400fecc5085)
72057594043498496 X GRANT
KEY
(ea0052ec9635)
72057594043498496 X GRANT
KEY
(ea0052ec9635)
72057594043498496 U WAIT
KEY
(ea0052ec9635)
72057594043498496 U WAIT
KEY
(ea0052ec9635)
72057594043498496 U WAIT
KEY
(ea0052ec9635)
72057594043498496 U WAIT
KEY
(ea0052ec9635)
72057594043498496 U WAIT
KEY
(ea0052ec9635)
72057594043498496 U WAIT
KEY
(ea0052ec9635)
72057594043498496 U WAIT
KEY
(ea0052ec9635)
72057594043498496 U WAIT
KEY
(ea0052ec9635)
72057594043498496 U WAIT
KEY
(ea0052ec9635)
72057594043498496 U WAIT
PAGE
1:17319
72057594043498496 IU GRANT
PAGE
1:17319
72057594043498496 IU GRANT
KEY
(ec008eb3fd10)
72057594043498496 X GRANT
PAGE
5:11012
72057594043432960 IU GRANT
PAGE
5:11012
72057594043432960 IX GRANT
PAGE
5:11012
72057594043432960 IU GRANT
KEY
(5005584715a6)
72057594042974208 U GRANT
KEY
(4e056fa9b681)
72057594042974208 U GRANT
PAGE
5:11202
72057594043432960 IU GRANT
PAGE
5:11202
72057594043432960 IX GRANT
KEY
(000135fb0d61)
72057594043498496 X GRANT
KEY
(d9003beca8c0)
72057594043301888 X GRANT
PAGE
1:17566
72057594043498496 IU GRANT
PAGE
1:17565
72057594043498496 IU GRANT
KEY
(be00a6228144)
72057594043432960 X GRANT
KEY
(bf00488d3456)
72057594043432960 X GRANT
PAGE
1:17598
72057594043498496 IU GRANT
KEY
(c10094d25f73)
72057594043432960 X GRANT
KEY
(8605c4bf6123)
72057594042908672 U GRANT
KEY
(73010c78fb48)
72057594043432960 X GRANT
PAGE
5:11315
72057594043432960 IU GRANT
PAGE
5:11315
72057594043432960 IU GRANT
PAGE
5:11315
72057594043432960 IU GRANT
PAGE
5:11315
72057594043432960 IU GRANT
OBJECT
78623323 IX GRANT
OBJECT
78623323 IX GRANT
OBJECT
78623323 IX GRANT
OBJECT
78623323 IX GRANT
OBJECT
78623323 IX GRANT
OBJECT
78623323 IX GRANT
OBJECT
78623323 IX GRANT
OBJECT
78623323 IX GRANT
OBJECT
78623323 IX GRANT
OBJECT
78623323 IX GRANT
OBJECT
78623323 IX GRANT
OBJECT
78623323 IX GRANT
OBJECT
78623323 IX GRANT
OBJECT
78623323 IX GRANT
OBJECT
78623323 IX GRANT
OBJECT
78623323 IX GRANT
OBJECT
78623323 IX GRANT
OBJECT
78623323 IX GRANT
OBJECT
78623323 IX GRANT
OBJECT
78623323 IX GRANT
OBJECT
78623323 IX GRANT
OBJECT
78623323 IX GRANT
OBJECT
78623323 IX GRANT
OBJECT
78623323 IX GRANT
OBJECT
78623323 IX GRANT
OBJECT
78623323 IX GRANT
OBJECT
78623323 IX GRANT
OBJECT
78623323 IX GRANT
OBJECT
78623323 IX GRANT
KEY
(57014f77ad91)
72057594043498496 X GRANT
KEY
(59019328c6b4)
72057594043498496 X GRANT
KEY
(5d012b9710fe)
72057594043498496 X GRANT
KEY
(5101e3576b21)
72057594043498496 X GRANT
KEY
(4f013f080004)
72057594043498496 X GRANT
KEY
(4b0187b7d64e)
72057594043498496 X GRANT
KEY
(45015be8bd6b)
72057594043498496 X GRANT
KEY
(0101509cb1d9)
72057594043498496 X GRANT
KEY
(f30047f48718)
72057594043498496 X GRANT
OBJECT
2146106686 IX GRANT
OBJECT
2146106686 IS GRANT
OBJECT
2146106686 IS GRANT
OBJECT
2146106686 IX GRANT
OBJECT
2146106686 IX GRANT
OBJECT
2146106686 IX GRANT
OBJECT
2146106686 IS GRANT
OBJECT
2146106686 IS GRANT
OBJECT
2146106686 IS GRANT
KEY
(f5009babec3d)
72057594043498496 X GRANT
KEY
(760142969880)
72057594043498496 U GRANT
KEY
(ff00ff4b5152)
72057594043498496 X GRANT
KEY
(f90023143a77)
72057594043498496 X GRANT
KEY
(c9006166b5da)
72057594043432960 U GRANT
KEY
(eb00378b2a8d)
72057594043498496 X GRANT
KEY
(c40031ab9aa1)
72057594043432960 U GRANT
KEY
(c100df042fb3)
72057594043432960 U GRANT
KEY
(ed00ebd441a8)
72057594043498496 X GRANT
KEY
(ca00554b27ce)
72057594043432960 U GRANT
KEY
(cb00bbe492dc)
72057594043432960 U GRANT
KEY
(c50067bbf9f9)
72057594043432960 U GRANT
KEY
(c80089144ceb)
72057594043432960 U GRANT
KEY
(7801ed41780b)
72057594043498496 U GRANT
KEY
(790103eecd19)
72057594043498496 U GRANT
KEY
(7b01dfb1a63c)
72057594043498496 U GRANT
OBJECT
2114106572 IX GRANT
PAGE
5:11889
72057594043432960 IU GRANT
KEY
(5501c4bfa43b)
72057594043498496 X GRANT
KEY
(5b0118e0cf1e)
72057594043498496 X GRANT
KEY
(49010c7fdfe4)
72057594043498496 X GRANT
KEY
(4701d020b4c1)
72057594043498496 X GRANT
KEY
(4d01b4c009ae)
72057594043498496 X GRANT
KEY
(5301689f628b)
72057594043498496 X GRANT
PAGE
1:16114
72057594043498496 IU GRANT
PAGE
1:16114
72057594043498496 IU GRANT
PAGE
1:16114
72057594043498496 IU GRANT
PAGE
1:16114
72057594043498496 IU GRANT
PAGE
1:16114
72057594043498496 IU GRANT
PAGE
1:16114
72057594043498496 IU GRANT
PAGE
1:16114
72057594043498496 IU GRANT
PAGE
1:16114
72057594043498496 IX GRANT
PAGE
1:16114
72057594043498496 IU GRANT
PAGE
1:16114
72057594043498496 IU GRANT
PAGE
1:16114
72057594043498496 IU GRANT
PAGE
1:16102
72057594043498496 IX GRANT
KEY
(bd00c3453dfc)
72057594043432960 X GRANT
KEY
(bd00c3453dfc)
72057594043432960 U WAIT
KEY
(c0002dea88ee)
72057594043432960 X GRANT
KEY
(c200f1b5e3cb)
72057594043432960 X GRANT
KEY
(c3001f1a56d9)
72057594043432960 X GRANT
KEY
(750187b0f2e2)
72057594043432960 X GRANT
KEY
(77015bef99c7)
72057594043432960 X GRANT
KEY
(fd00748358f8)
72057594043498496 X GRANT
KEY
(fb00a8dc33dd)
72057594043498496 X GRANT
KEY
(f100cc3c8eb2)
72057594043498496 X GRANT
KEY
(750127f12438)
72057594043498496 U GRANT
KEY
(7401c95e912a)
72057594043498496 U GRANT
KEY
(f7001063e597)
72057594043498496 X GRANT
KEY
(c900302c9b76)
72057594043432960 U GRANT
KEY
(cc00de832e64)
72057594043432960 U GRANT
KEY
(c60002dc4541)
72057594043432960 U GRANT
KEY
(c700ec73f053)
72057594043432960 U GRANT
KEY
(ca0004010962)
72057594043432960 U GRANT
KEY
(c000663cf82e)
72057594043432960 U GRANT
KEY
(c000663cf82e)
72057594043432960 U WAIT
KEY
(c000663cf82e)
72057594043432960 U WAIT
KEY
(c000663cf82e)
72057594043432960 U WAIT
KEY
(cb00eaaebc70)
72057594043432960 U GRANT
KEY
(ef00601c4802)
72057594043498496 X GRANT
KEY
(c30054cc2619)
72057594043432960 U GRANT
KEY
(c200ba63930b)
72057594043432960 U GRANT
KEY
(77018826c4b3)
72057594043498496 U GRANT
KEY
(7a01668971a1)
72057594043498496 U GRANT
PAGE
5:8052
72057594042908672 IU GRANT
PAGE
5:8042
72057594042908672 S GRANT
PAGE
5:8043
72057594042908672 S GRANT
PAGE
1:79279
72057594042974208 S GRANT
PAGE
1:79280
72057594042974208 IU GRANT
KEY
(0301db54b873)
72057594043498496 X GRANT
KEY
(0501070bd356)
72057594043498496 X GRANT
KEY
(4201aaeeb8b5)
72057594043498496 X GRANT
PAGE
1:79317
72057594042974208 S GRANT
PAGE
1:79317
72057594042974208 S GRANT
PAGE
1:79318
72057594042974208 IU GRANT

From: Marek on 22 Jul 2008 05:09

why
delete from do_kumulace WITH (ROWLOCK) where oscislo = 501212 and
convert(datetime,'01.03.2007',104) <= do and
convert(datetime,'31.03.2007',104) >= od
blocking this command ?
delete from do_intervaly WITH (ROWLOCK) where oscislo = 501070 and
convert(datetime,'17.09.2007',104) <= datum and
convert(datetime,'23.09.2007',104) >= datum

?

From: dave ballantyne on 22 Jul 2008 05:16

Marek wrote:
> why
> delete from do_kumulace WITH (ROWLOCK) where oscislo = 501212 and
> convert(datetime,'01.03.2007',104) <= do and
> convert(datetime,'31.03.2007',104) >= od
> blocking this command ?
> delete from do_intervaly WITH (ROWLOCK) where oscislo = 501070 and
> convert(datetime,'17.09.2007',104) <= datum and
> convert(datetime,'23.09.2007',104) >= datum
>
> ?

Whats the relationship between do_kumulace and do_intervaly is one a
view on another ? Does one have a cascading delete to the other ?

Does

Select * from do_kumulace where oscislo = 501212 and
convert(datetime,'01.03.2007',104) <= do and
convert(datetime,'31.03.2007',104) >= od

and

Select * from do_intervaly where oscislo = 501070 and
convert(datetime,'17.09.2007',104) <= datum and
convert(datetime,'23.09.2007',104) >= datum

execute with a decent query plan ?

Im assuming that do ,od and datnum are datetimes , in that case you dont
need the convert.

Has the app left a transaction open ?

Dave

From: Uri Dimant on 22 Jul 2008 05:35

Marek
There are many objects (72057594043498496,72057594043432960 ) can show us by
using OBJECT_NAME... to what table/index does it relate?

Again, do you have index on do? or oscislo ? Clustered Index might be a good
candidate 'datum' column

"Marek" <grolms(a)volny.cz> wrote in message
news:a8d30c62-fac4-4170-bea7-f7a36aca4c30(a)b1g2000hsg.googlegroups.com...
> why
> delete from do_kumulace WITH (ROWLOCK) where oscislo = 501212 and
> convert(datetime,'01.03.2007',104) <= do and
> convert(datetime,'31.03.2007',104) >= od
> blocking this command ?
> delete from do_intervaly WITH (ROWLOCK) where oscislo = 501070 and
> convert(datetime,'17.09.2007',104) <= datum and
> convert(datetime,'23.09.2007',104) >= datum
>
> ?

From: Marek on 22 Jul 2008 06:21

I am not use transaction in the application ? I use sqlconnection and
sqlcomand . Relation between tables is not ....

First | Prev | Next | Last
Pages: 1 2 3
Prev: Check Reporting Services Installation Programmitcally
Next: TransactionAbortedException Occurs Randomly