INSERT in one table but it locks another table
I'm now having a problem with SQL Server table lock. I'm developing in C#.
My queries run under 1 transactions.
I name it for the easiest way to recognize. "setTransaction"
setTransaction is only for "INSERT / UPDATE / DELETE".
- if I want to do SELECT. I'll use SqlDataAdapter.
- If I want to do INSERT / UPDATE or DELETE, it's the time to use setTransaction.
here is the table structure of each ...
[LOG]( [log_id] [int] IDENTITY(1,1) NOT NULL, [subject] [text] NOT NULL, [query] [text] NOT NULL, [log_datetime] [datetime] NOT NULL, [user_id] [int] NOT NULL, [emp_id] [int] NULL, [old_value] [text] NULL ) [RESERVATION_DETAIL]( [**reservation_detail_id**] [int] IDENTITY(1,1) NOT NULL, [reservation_id] [int] NOT NULL, [spa_program_id] [int] NULL, [price] [int] NULL, [oil] [int] NULL ) [RESERVATION_THERAPIST]( [reservation_therapist_id] [int] IDENTITY(1,1) NOT NULL, [**reservation_detail_id**] [int] NOT NULL, [therapist_id] [int] NOT NULL, [hours] [int] NULL, [mins] [int] NULL )
[LOG] is working independently. [RESERVATION_DETAIL] are connected to [RESERVATION_THERAPIST] via reservation_detail_id
The problem is ....
- BEGIN TRANSACTION. I want to delete a record from "RESERVATION_DETAIL" with reservation_detail_id = 25
I select a record from "RESERVATION_DETAIL" with reservation_detail_id = 25
SELECT * FROM RESERVATION_DETAIL WHERE RESERVATION_DETAIL_ID = 25
I insert into table "LOG" with data from 2.
INSERT INTO LOG ( subject, query, log_datetime, user_id, emp_id, old_value ) VALUES ( 'DELETE TEMP RESERVE FROM RES_DETAIL[RES_DETAIL_ID:25]', 'DELETE FROM RESERVATION_DETAIL WHERE RESERVATION_DETAIL_ID = 25', CURRENT_TIMESTAMP, 1, NULL, 'reservation_detail_id:25|reservation_id:25|spa_program_id:-1|price:|oil:' )
now I delete from "RESERVATION_DETAIL" where reservation_detail_id = 25 Then I want to delete a record from "RESERVATION_THERAPIST" with reservation_detail_id = 25
DELETE FROM RESERVATION_DETAIL WHERE RESERVATION_DETAIL_ID = 25
I select a record from "RESERVATION_THERAPIST" with reservation_detail_id = 25 <----- I GOT THE LOCK HERE !!
SELECT * FROM RESERVATION_THERAPIST WHERE RESERVATION_DETAIL_ID = 25
I insert into table "LOG" with data from 5.
- Finally I will delete from "RESERVATION_THERAPIST" where reservation_detaiil_id = 25
Above steps were run consequently. the step 5 (which is about table "RESERVATION_THERAPIST") is now wait on step 3 (about table "LOG") to finished but It never finished.
I don't understand why I insert into table LOG but it put the lock onto the table B !? or this is not a lock !?
there were the queries before the above step that insert into LOG without any problem.
Now I can solve my problem.
The queries and steps are already OK.
But I forgot that the table "RESERVATION_DETAIL" has a trigger that will run right after the DELETE query is committed.
So the trigger will go to delete a record in "RESERVATION_THERAPIST" automatically and this step is under the transaction.
So "RESERVATION_THERAPIST" was locked up after "DELETE FROM RESERVATION_DETAIL" but before I can "SELECT * FROM RESERVATION_THERAPIST"
Several things could cause the table to be locked until the deletion is through:
- You are directly deleting from the table you are trying to select from
- You have cascade delete turned on for a parent table to the one that is locked.
- You have a trigger on another table that you are taking an action from that also deletes from the table.
- You are trying to delete the tables out of order (child table are always deleted first, then parent ones. When deleting from a parent table, it will check to see if child records exist. This of course takes longer than a straight delete and will cause a rollback if the child data exists which takes longer yet.
- You have deadlock between two process running at the same time.
You got the lock on B because you deleted from it.