Dead lock issue

Question

Post reply

Dead lock issue

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 1

we had an issue where a select would default to an index scan on the PK because it was grabbing something like 3 million rows and at the same time there would be an insert that would also try to use the PK and this caused blocking

we got around the problem by using an index hint to force it to use a nonclustered index and we have plans to change the PK to a nonclustered index and another column to a clustered index

SriSun Say Hey Kid Points: 684 More actions · Answer 2

Hi,

I finally changed my code (procedure) and came to this end.

if not exists(select [name] from workspace.sys.tables where [name] = 'geoleveltbl')

begin

create table dbo.geoleveltbl(geokey varchar(10), refgeokey varchar(10))

end

This code blows up saying table geoleveltbl already created.

Reason: As I said earlier multiple instances(20) hit at one short. Could any body help me in this regard.

Following Techniques I used: Transactions and Isolation Level Serializable.

Thanks,

Sri

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

SriSun (1/13/2008)

Hi,

I finally changed my code (procedure) and came to this end.

if not exists(select [name] from workspace.sys.tables where [name] = 'geoleveltbl')

begin

create table dbo.geoleveltbl(geokey varchar(10), refgeokey varchar(10))

end

This code blows up saying table geoleveltbl already created.

Reason: As I said earlier multiple instances(20) hit at one short. Could any body help me in this regard.

Following Techniques I used: Transactions and Isolation Level Serializable.

Thanks,

Sri

I don't recall anything prior where you said you were creating tables like this. I think to make this work you would need to do a full select under serializable transaction level to lock everything assocaited with the name = 'geoleveltbl' value. Then if @@rowcount = 0 try the create. I do have to question why you are trying to dynamically create tables so frequently that you get errors trying to create the same table. This could be a really bad design flaw in your app.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

SriSun Say Hey Kid Points: 684 More actions · Answer 4

I don't create geolevel frequently, there are 10 different levels with data associated with them, so whenever client application try to download data for 5-6 levels say, then all of them try to create table geoleveltbl and insert data. That is how the requirement was, I know its a bad design keeping DDL stmts inside a procedure.

Could you please write me a sample code, since I tried all the ways like keeping the TRANSACTION ISOLATION LEVEL SERIALIZABLE still my problem did not solved.

Thanks,

Sri.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

I would think something like this would do it:

BEGIN TRAN

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

--note: this will give your sproc another output set. I am not sure though if just putting [name] into a variable will suffice.

select [name] from workspace.sys.tables where [name] = 'geoleveltbl' --you may also try throwing in a UPDLOCK on the table here

IF @@ROWCOUNT = 0 --no table really exists

begin

create table dbo.geoleveltbl(geokey varchar(10), refgeokey varchar(10))

end

COMMIT TRAN

You should throw in error handling too and also don't forget to set isolation level back to your default both on sproc exit as well as in any error handling loops.

I still don't understand why these tables have to be created when they are used. Are you CERTAIN they can't just remain in place and be reused by whatever process needs them when it arises??

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

SriSun Say Hey Kid Points: 684 More actions · Answer 6

SET TRANSACTION isolation level SERIALIZABLE

begin transaction

if not exists(select [name] from workspace.sys.tables (UPDLOCK ) where [name] = 'sri')

begin

create table workspace.dbo.sri(empno int)

end

if @@error > 0

rollback transaction

else

commit transaction

Is this code looks ok, if so still I see the problem.

I cannot just say

select [name] from workspace.sys.tables (UPDLOCK ) where [name] = 'sri'

since it will output the empty resultset.

As I said that I cannot create these table ahead because of few concern.

Thanks for your valuable time.

Could you please check this code and do some changes if required to fix the problem, I ran out of any ideas.

Thanks,

Sri.