Reading locked records

Brian Donahue

Hi Brian,

Thanks for posting. I've had a look at Data Compare and I've noticed that it does its' SELECT operations WITH NOLOCK. According to Books Online, this means that it can do 'dirty reads', in other words, it can read uncommitted tansactions. The exact wording from BOL:

Specifies that dirty reads are allowed. This means that no shared locks are issued and no exclusive locks are honored. Allowing dirty reads can result in higher concurrency, but at the cost of lower consistency. If READUNCOMMITTED is specified, it is possible to read an uncommitted transaction or to read a set of pages rolled back in the middle of the read; therefore, error messages may result. For more information about isolation levels, see SET TRANSACTION ISOLATION LEVEL.

Hopefully this helps!

mago

HI, brian donahue:

The simular question is :
What's sql data compare's Isolation Level

When sql data compare runs, we cannot select a row from the database.
If sql data compare allows "READ UNCOMMITTED", I guess our selection is operatable. For our case, we must to wait for sql data compare complete, then we could run our select sql. It seems a little unreasonable.

Regards,
Mago

Brian Donahue

Hello mago,

When reading data, it shouldn't cause a lock situation as far as I know. If you run the synchronization from the program then the table should be locked for the duration of the transaction. Once the end of the transaction is reached, you should be free to update the table.

I don't think that this is an issue because of the isolation level, but down to the fact that SQL Server behaves this way when you run a rather large transaction.

mago

Hi, Brian:

Sorry. I need to refine my question. Maybe the reason is not transation lock when sql data compare runs.

My question is :
Why a simple sql query times out when sql data compare runs.

When our sql times out, I try to check server's loading. The sql data compare always runs at that time. So we think sql data compare results in table lock that we couldn't get data at the time by our sql. (Our sql doesn't include any transaction)

Could you please tell us more about sql data compare's operation ?
Does sql data compare lock table when updating data ? And what do we do to avoid that ? Thanks a lot. :-)

Regards,
Mago

Brian Donahue

Hello Mago,

As far as I'm aware, we cannot change the SQL Server behaviour when updating the data. Once we go into a transaction, the table will be locked by SQL Server until the transaction completes. If you do not like this, you can try using the 'do not use transactions' option.

mago

Hi, Brian:

Could you please tell me the detail about sql data compare operations ?
Does it only set one transaction for whole sync operation or for one updated sql ? It's different.

If only one transaction for whole sync operation, tables would be locked a long time after data sync finished. I suggest to have setting to disable it inside sql data compare(like sql compare).

If it just locked table as SQL Server behaviour for insert one row at that time, it cannot explain why table is locked so long.

Many thanks your quick response. :-)

Regards,
Jessy

Brian Donahue

Hello,

Normally the updates all happen within the same transaction, locking the table. That is why there is an option in Data Compare that says do not use transactions. I beleive that's what you want to use when you're running synchronization scripts.

mago

Dear Brian:

Yes. I found the parameter in sql data compare.
Sorry again and again.
Thanks a lot.

Regards,
Mago[/b]

Brian Donahue

No problems! :-D