Re: Perl DBI(ODBC) to MSSQL

Oracle lets you do a select for update which locks the table it reads from until the end of the transaction. Maybe MSSQL has somethingl ike that?

___________
Eric Hodges

Comment on Re: Perl DBI(ODBC) to MSSQL Download Code

Replies are listed 'Best First'.
Re^2: Perl DBI(ODBC) to MSSQL by Somni (Friar) on Oct 13, 2007 at 00:59 UTC
You have to be careful and read the docs regarding exactly what `SELECT ... FOR UPDATE` locks. Specifically, with Postgres, `SELECT ... FOR UPDATE` is a row level lock on the returned rows. As a result you can't use aggregates. So, you have to analyze carefully what your entire transaction needs to do, then decide if you need to lock or the natural transaction consistency will suffice. If you do need to lock, make sure to lock as little as possible, but be sure to lock it such that you can avoid deadlocks. For example, with a recent application I was writing that uses Postgres, I needed to do some aggregating to determine what to insert. I could've locked with SHARE mode. However, two sessions locking with SHARE mode don't conflict, so the subsequent implicit ROW EXCLUSIVE lock performed by the insert later might've resulted in a deadlock.	[reply] [d/l] [select]

Replies are listed 'Best First'.

Re^2: Perl DBI(ODBC) to MSSQL
by Somni (Friar) on Oct 13, 2007 at 00:59 UTC

SELECT ... FOR UPDATE

Specifically, with Postgres, SELECT ... FOR UPDATE is a row level lock on the returned rows. As a result you can't use aggregates.

So, you have to analyze carefully what your entire transaction needs to do, then decide if you need to lock or the natural transaction consistency will suffice. If you do need to lock, make sure to lock as little as possible, but be sure to lock it such that you can avoid deadlocks.

For example, with a recent application I was writing that uses Postgres, I needed to do some aggregating to determine what to insert. I could've locked with SHARE mode. However, two sessions locking with SHARE mode don't conflict, so the subsequent implicit ROW EXCLUSIVE lock performed by the insert later might've resulted in a deadlock.

[reply]
[d/l]
[select]