Deadlock Errors

Question

Deadlock Errors

Jmathai

SSC Rookie

Points: 45
More actions
October 28, 2004 at 11:27 am

#108945

I have a table called sequence which is basically storing the next sequential number for each table
Transactions is like
statement 1
UPDATE sequence
SET current_seq = current_seq + 1
WHERE description = 'ORDERS.ORDER_NO' whereas ORDERS is the table ORDER_NO is the column
If system finds a row to update
statement 2
SELECT min_seq, max_seq, current_seq, rowid
FROM sequence
WHERE description = 'ORDERS.ORDER_NO'
and then the current seq is used to create an entry in ORDERS table
If system do not find any row, system creates a record in SEQUENCE
statement 3 >> insert into sequence
But system sometimes generate deadlock on the statemnet 1.
Any suggestions will be highly appreciated.. Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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

sunjiulu Ten Centuries Points: 1232 More actions · Answer 1

deadlock is not only a lock. it happened only if you have at least 2 transactions, and one transaction hold a lockA and require lockB, but another transaction hold lockB and require lockA, the server will choose a victum and kill it, because deadlock will never be solved by itself.

deadlock can be solved by spliting bigger transaction into small one, or carefully code the sequence of DML in all sql.

search google, you will find some scenario.

ericp37 SSChasing Mays Points: 631 More actions · Answer 2

This problem is almost always incured when you have multiple people or processes going against a KEY_DEF type table.

1) Consider using an Identity for the column that has the key. Ok, it is not as flexible, but you wont have deadlocks.

2) If you have multiple keys in your table, make sure that ROW LEVEL LOCKING is turned on for that table.

3) If you do have multiple rows in the key table, make sure the SQL is not doing a table scan. This often happens in small tables, but the down side is that it can LOCK the entire table.

4) The deadlock may occur on the next row, but the SQL took a page lock. It can help to put one row per page in the table.

5) Make sure that your processes always access the tables in the same order.

Have fun

Eric

Jacob Russo SSC Rookie Points: 32 More actions · Answer 3

I had a major problem with deadlocks many months ago. I did follow the advice mentioned above. I needed a lot more control over the locking. The main things I did to get around them was as mentioned:

1. Make sure that table access to all tables was in the same exact order in every piece of code that updates or adds records.

2. Immediatelly upon opening the tables. I would issue an "UPDATE tablename SET tableRecID = tableRecID WHERE condition" on ALL tables I had read from to "Force" the locks immediatelly. Then perform ALL the "UPDATE" statements at the SAME time but in reverse order.

I have not seen a deadlock since! What I learned is that SQL-Server has a mind of its own when it comes to record locking.

Imaging101 Affordable Document Imaging for Business!
PC Networks Document Imaging Specialists!

Jmathai SSC Rookie Points: 45 More actions · Answer 4

I thought on SQLServer Rowlevel locking is by default. I will try to put 1 row per page and see if it helps.. thanks for all the replies

Jacob Russo SSC Rookie Points: 32 More actions · Answer 5

Jmathai,

I theory, row level locking is automatic... but the exact results are based on way too many things:

a) Lock type - Pessimistic / Optimistic

b) Number of indexes

c) Type of indexes

Under some Select or Update conditions, and depending on any combination of the above, SQL-Server may lock a whole bunch of rows you did not expect to be locked. A single row per page may be an unnecessary waste of resources and will probably still not provide you with the proper results.

Best of Luck,

Imaging101 Affordable Document Imaging for Business!
PC Networks Document Imaging Specialists!

Jmathai SSC Rookie Points: 45 More actions · Answer 6

We are using Read committed (SQL Server default level).

Initially we did not have any clustered indexes at all, we have clustered index on some tables.There is 1 index on SEQUENCE table , as this is a varchar field, I did not add a clustered index.

UPDATE sequence

SET current_seq = current_seq + 1

WHERE description = 'ORDERS.ORDER_NO' whereas ORDERS is the table ORDER_NO is the column

I changed to UPDATE sequence ( ROWLOCK)

SET current_seq = current_seq + 1

WHERE description = 'ORDERS.ORDER_NO' whereas ORDERS is the table ORDER_NO is the column

For past 2 weeks, users are not getting deadlock on SEQUENCE table, I am not sure whether ROWLOCK fixed the issue

Thanks for your input

Jesse Mathai