January 8, 2009 at 7:42 am
I've a Session1 that's asking a U key lock, but Session2 has already a X key lock on it.
Session2 is asking exactly the same U key lock, but Session1 has already a X lock on it.
Unbelievable, I don't understand how this can be possible.
We're running SQL 2005 Ent. Edition (x64) 9.00.3215
See attachment.
tx
Geof
January 8, 2009 at 7:47 am
Can you export the deadlock graph to XML and post that here?
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
January 8, 2009 at 8:39 am
I'll second Jonathan's request as that will help us see what each session is attempting to do. You may want to check out this blog post as well: http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/01/01/reproducing-deadlocks-involving-only-one-table.aspx
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 8, 2009 at 8:57 am
Hi,
here is the XML:
UPDATE task_header
SET task_type = @c_task_type_PICKING_CLEAN_PALLET,
container_code = from_container_code,
to_container_code = from_container_code -- both fields to be filled
WHERE task_header.picklist_number = @i_picklist_number
AND task_header.task_status = 0
AND task_header.task_type = @c_task_type_PICKING_SKU
AND EXISTS
(SELECT 1
FROM container_explosion conexp
INNER JOIN container_location conloc
ON conloc.container_code = conexp.top_level_container_code
AND conloc.container_location_status = @c_container_location_status_ACTUAL_POSITION
INNER JOIN container contai
ON contai.container_code = conexp.container_code
AND contai.container_usage = @c_container_usage_REAL_TRANSPORTABLE
INNER JOIN location locati
ON locati.warehouse_code = conloc.warehouse_code
AND locati.location_code = conloc.location_code
LEFT OUTER JOIN location_type loctyp
ON loctyp.location_type_code = locati
EXEC @v_local_return_code = spi__picklist_release_switch_tasks_to_pallet_tasks
@i_picklist_number = @i_picklist_number;
EXECUTE @v_local_return_code = spi__picklist_release
@i_picklist_number = @i_picklist_number,
@i_requester_user_code = @i_requester_user_code,
@o_print_requested = @v_print_requested OUTPUT;
EXECUTE @v_local_return_code = spji__picklist_release_handler_process_record
@i_picklist_number = @v_picklist_number,
@i_requester_user_code = @i_requester_user_code,
@i_maximum_number_of_retries = 3;
EXEC spj__picklist_release_handler
@i_requester_user_code = 'DSV'
EXEC spj__picklist_release_handler
@i_requester_user_code = 'DSV'
UPDATE task_header
SET task_type = @c_task_type_PICKING_CLEAN_PALLET,
container_code = from_container_code,
to_container_code = from_container_code -- both fields to be filled
WHERE task_header.picklist_number = @i_picklist_number
AND task_header.task_status = 0
AND task_header.task_type = @c_task_type_PICKING_SKU
AND EXISTS
(SELECT 1
FROM container_explosion conexp
INNER JOIN container_location conloc
ON conloc.container_code = conexp.top_level_container_code
AND conloc.container_location_status = @c_container_location_status_ACTUAL_POSITION
INNER JOIN container contai
ON contai.container_code = conexp.container_code
AND contai.container_usage = @c_container_usage_REAL_TRANSPORTABLE
INNER JOIN location locati
ON locati.warehouse_code = conloc.warehouse_code
AND locati.location_code = conloc.location_code
LEFT OUTER JOIN location_type loctyp
ON loctyp.location_type_code = locati
EXEC @v_local_return_code = spi__picklist_release_switch_tasks_to_pallet_tasks
@i_picklist_number = @i_picklist_number;
EXECUTE @v_local_return_code = spi__picklist_release
@i_picklist_number = @i_picklist_number,
@i_requester_user_code = @i_requester_user_code,
@o_print_requested = @v_print_requested OUTPUT;
EXECUTE @v_local_return_code = spi__picklist_reclaim
@i_picklist_number = @v_picklist_number,
@i_requester_user_code = @v_requester_user_code,
@o_confirm_print_requested = @v_reclaim_confirm_print_requested OUTPUT;
EXECUTE @v_local_return_code = spi__output_registration_create
@i_session_id = NULL,
@i_operation_id = @v_worope_operation_id,
@i_output_id = @v_opeout_output_id,
@i_quantity = 1,
@i_reg_type_id = @c_reg_type_id_GOOD,
@i_reason_code_id = NULL,
@i_comments = '',
@i_container_code = @v_virtual_container_code,
@i_creation_warehouse_code = @v_creation_warehouse_code,
@i_creation_location_code = @v_creation_location_code,
@o_output_reg_id = @v_output_reg_id OUTPUT;
EXEC @o_return_value = spi__transfer_opstch_kitting_complete_process @i_transfer_id
sp_executesql
EXECUTE sp_executesql @v_statement, @v_params, @i_transfer_id = @v_transfer_id, @o_return_value = @v_local_return_code OUTPUT;
EXEC spj__transfer_handler;
EXEC spj__transfer_handler;
Kind Regards
Geoffrey
January 8, 2009 at 8:58 am
O, I forgot to mention I use the read_committed snapshot isolation level
January 8, 2009 at 9:15 am
I've uploaded the xdl file, so you can see it graphicallly.
In the meantime I've the same case with another table.
With Regards
Geoffrey
January 8, 2009 at 10:06 am
Well, it is 2 stored procedures accessing the resources. You need to check that they are doing their access in the same order. Can you post the sp code?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply