Transactions deadlock

Question

Transactions deadlock

nKognito

SSC Enthusiast

Points: 173
More actions
December 18, 2008 at 10:40 pm

#129335

Hi there! I have a troubles with post's subject. Here is scenario: there is a table with 1 million records. Fields are Id, ParentId, State, JobId (foreign key), UserId (foreign key), CellId (foreign key), Data. State may have one of two states : "ready" and "inwork". Each user gets a record for work (some client side operation with Data value) and sends it back to server which have to update a record. When user gets a record (to prevent from others to get it too) the system updates a State field with "inwork" value and a UserId filed with current user's Id to know who received a work. The code is:
BEGIN TRAN
DECLARE @Id bigint
DECLARE @State nvarchar(256)
SET @Id = NULL
UPDATE TOP(1) DataTable SET @Id = Id,
@State = State,
PrevState = @State,
State = 'inwork',
WorkTime = GETDATE(),
WorkerUserId = @userid
FROM DataTable
WHERE State LIKE 'ready' AND JobId = @JobId
AND CellId = CASE WHEN @CellId IS NULL THEN CellId ELSE @CellId END AND WorkerUserId != @userid
IF @Id IS NOT NULL
SELECT Id, PId, Data, @JobId AS JobId, @State AS State
FROM DataTable
WHERE Id = @Id
COMMIT TRAN
The problem is if there are two or more users and they want to get a work - I receive a transaction deadlock because (by sql profiler) they both trying to update the same record! I think its because of "hard" where clause and while first user starts to execute it the second one also can read an "old" values. AM I right?
How can I build this mechanism (of getting works) to prevent such cases? Thank you.
P.S. The same happens on saving work - update command takes too long time and while updating row (before completing) other user can get a work (deadlock too) because as I described every get work need to update a State.

Viewing 15 posts - 1 through 14 (of 14 total)

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

ps. One Orange Chip Points: 29422 More actions · Answer 1

deadlock issues will occur if two or more users are trying to update the same record.

What you can do here is introduce concept of allocation. You can allocate the records to various users so that they work on their allocated portion only and do not touch other records.

You can add a column AllocationStatus and use the Field WorkerUserID to allocate.

AllocationStatus can be one of the following

1. Unallocated

2. Allocated

3. Completed -- These records have already been allocated and worked upon...

You may think of using the field State for this purpose instead of adding new column.

-----------

The difference will be in your case, end users are trying to pull a record and update it, in this case u'll allocate records to users and users' will work on those..

Pradeep Singh

nKognito SSC Enthusiast Points: 173 More actions · Answer 2

Thanks for your reply! Your advise may help me at save work mechamism, but still I have a problem at get work. I have checked with profiler on two users trying to get work: first user starting get work with update query. Before first user's completing his query the second one also starting the same query and of course the second one will try to update the same row too (because first one did not completed his update yet!) And when first one trying to complete his query I have a deadlock because the State field is under two transactions and every one of them wants to update it! How to prevent this?

Thank you.

ps. One Orange Chip Points: 29422 More actions · Answer 3

assuming you have 100 records and 20 users, You allocate 5 records each to the 20 users.

Make an change in the application so that each user sees only those records allocated to him. so this conflict will not happen at all...

You can give administrative privilages to reallocate the records to other users...

Pradeep Singh

nKognito SSC Enthusiast Points: 173 More actions · Answer 4

I understand but it is not good for me. I have a million works to do and 100 users (for example) - one of them more "usefull" other less and I can not to split all works between all users because my major task is to get all works done in minimum period of time..

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Can you obtain and post a deadlock graph? Switch traceflag 1204 on (DBCC TRACEON (1204,-1)). That will print the deadlock graph into the error log, showing the resources and queries involved in the deadlock.

If you can post that, the contents of any proc that the deadlock graph shows and the table's schema and indexes, we can probably help you more.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ps. One Orange Chip Points: 29422 More actions · Answer 6

You can write a scheduled job for this task of allocation.

1. find number of available records requiring action and are not in a state of 'inwork'

2. find number of availabe users

3. distribute records uniformly...

You can schedule this job to run multiple times a day... this will eliminate problem of efficiency.... as 'more efficient' users will never fall short of records...

-------------------------------------

Pradeep Singh

nKognito SSC Enthusiast Points: 173 More actions · Answer 7

Thanks for your help, but I know where deadlock occures. THe problem is I do not understand why! Why two paralel update queries can execute parallel!? It means (by profiler) fbefore first one completes the seconf one can update the same row!!! Even with rowlock. How its possible!?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

nKognito (12/19/2008)
Thanks for your help, but I know where deadlock occures.

Are you sure? You know what the victim is, but without the deadlock graph there's no way to be sure what the other process is and what resources are involved.

It means (by profiler) before first one completes the second one can update the same row!!! Even with rowlock. How its possible!?

It's not possible. The second is probably waiting for the first. What resource it has that the first process wants is the question.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

nKognito SSC Enthusiast Points: 173 More actions · Answer 9

GilaMonster (12/19/2008)
nKognito (12/19/2008)
Thanks for your help, but I know where deadlock occures.
Are you sure? You know what the victim is, but without the deadlock graph there's no way to be sure what the other process is and what resources are involved.
It means (by profiler) before first one completes the second one can update the same row!!! Even with rowlock. How its possible!?
It's not possible. The second is probably waiting for the first. What resource it has that the first process wants is the question.

Wait the minute.. Lets say there are two update statements that are executing at the same time:

UPDATE table

SET State = 'inwork'

WHERE State = 'ready'

There is a row in table that matches to this condition. Both update queries trying to update the same record BUT the first one will do it first! What will do the second one? Will try to find an other record that matches to the condition or will return an error?

ps. One Orange Chip Points: 29422 More actions · Answer 10

It'll wait for the first update to finish as first update has acquired a rowlock; 2nd one cannot update the rows held by the lock created by 1st update.

Pradeep Singh

nKognito SSC Enthusiast Points: 173 More actions · Answer 11

ps (12/19/2008)
It'll wait for the first update to finish as first update has acquired a rowlock; 2nd one cannot update the rows held by the lock created by 1st update.

Understood. But what 2nd one will do at the end of 1nd? Will update the next record that matches to the condition or will not do anything?

ps. One Orange Chip Points: 29422 More actions · Answer 12

I tried something like this....

------------------------------

create table ##emp

(

name varchar(25)

)

insert into ##emp

select '1'

union all

select 'Har'

union all

select '2'

union all

select '7'

union all

select 'abc'

union all

select '#'

union all

select '@'

alter table ##emp add col2 varchar(20)

-- from Client 1

begin tran

update ##emp set col2='updated' where name='1'

insert into ##emp select '1','pradeep'

-- from client 2

begin tran

update ##emp set col2='updated2' where name='1'

--** It waits as there is a rowlock held by client 1

-- from client 1

Commit

--At Client 2

--**I See 2 Rows updated....

COmmit

-----------------------------------------------

I Hope this example answers ur queries...

Pradeep Singh

nKognito SSC Enthusiast Points: 173 More actions · Answer 13

nKognito

SSC Enthusiast

Points: 173

December 19, 2008 at 1:40 am

#915039

Ok, thanks. I'll try to solve my problem.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

nKognito (12/19/2008)
Understood. But what 2nd one will do at the end of 1nd? Will update the next record that matches to the condition or will not do anything?

It will update any rows that it finds that match the condition. If there are none (and there probably will be none, seeing as the first update updated them all, leaving no rows matching the condition) then it will do nothing.

An update will update all rows matching the condition, not one. If your query runs

UPDATE table

SET State = 'inwork'

WHERE State = 'ready'

and there are 10 rows with a state of ready, all 10 will be updated, in one operation, to state = inwork

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass