Disable locking or part-table Lock?

KeeperRU

I have some trouble with locking in my new table.

What we have: New document (I've developed it) with header and lines that created autimatically (on button).
Lines have many columns and document will be created around 5-10 minutes.
Also user can update document (delete old lines, and create new ones instead). And manually edit some numbers or text info.

We have 10 users, that creating this Documents separately. Documents don't have any connections.
When one user create his document (10 minutes) -> others can't update their documents, because of lock table.

Can I lock table "per document", or something?

---

I've already re-writed my code to insert\modify data in the end of code, but it's solved half of the problem.

vaprog

Hi,

check into SQL Server trace flag 1224 (Lock Escalation)

also consider preparing the document in a temporary table first, then copy it to the real table in one go (mind your isolation requirements, though)

KeeperRU

vaprog wrote: »

also consider preparing the document in a temporary table first, then copy it to the real table in one go (mind your isolation requirements, though)

I've made this, but IF two persons run long-term function (10 minutes) at once (different documents), than copy to the real table will be between 9 and 10 minute. And it's very dissapointing to wait 9 minutes and get "table locked by another user" error at the end (instead ob beginning).

I will try LOCKTIMEOUT(FALSE) for this situation, it's work good for long-term function.

KeeperRU

And I read about "Always rowlock". It's very interesting feature for me...
I have not so many users... and not so many transaction to GL Entry. (mostly is our custom tables and forms).

But users work in same documents \ card forms - and lock each other.

---

If I turn on "Always rowlock" - can it ruin my invoice posting to GLE or something?

Kishorm

Turning on "Always Rowlock" will not cause postings to go wrong. The benefit is that it tries not to escalate row locks in page locks, table lock etc... the only downside is that it will use more memory to store lock information.

KeeperRU

"Always Rowlock" don't work at all. I don't understand how it works.

rmv_RU

It seems during the document creation you lock lot of records. There are lot of reasons of this behavior:
1. Loop on table after modify record.
2. Сhecking for existence and Serializable isolation level.
Check for lock sequence and key usage, especially if you modify table