question: potential db lock up with Sybase SQL 'select' statement that takes long

[eliquant]

question: potential db lock up with Sybase SQL 'select' statement that takes long time to execute ....

here it is although almost irrelevant to my question:

"select a, b, c, d, SUM(e) as f from A where a = '080429' and appl b = 'something' and d = 111111
group by a, b ,c, d

So the question is with Sybase (and I don't have the version but am using 3.11 drivers), as with the usual readers/writers problem with lock outs in multi threading programming will Sybase then lock out all other users for the entire duration of my SQL statement that wish to perform a SQL write on table 'A' of my SQL above for the entire duration of my 'select' command above which is being executed ?

Or is Sybase a bit smarter than that ..?

remember my SQL above will be ran on the client side i.e. not a stored procedure whereas other user's SQL WILL be typically ran on stored procs.

(tbh: I think I know the answer already as I've seen the result on old SQL server once before).

[DiscoStu]

Sorry, this is in the wrong area. This is the section for bikini line discussion.

[DimPrawn]

question: potential db lock up with Sybase SQL 'select' statement that takes long time to execute ....

here it is although almost irrelevant to my question:

"select a, b, c, d, SUM(e) as f from A where a = '080429' and appl b = 'something' and d = 111111
group by a, b ,c, d

So the question is with Sybase (and I don't have the version but am using 3.11 drivers), as with the usual readers/writers problem with lock outs in multi threading programming will Sybase then lock out all other users for the entire duration of my SQL statement that wish to perform a SQL write on table 'A' of my SQL above for the entire duration of my 'select' command above which is being executed ?

Or is Sybase a bit smarter than that ..?

remember my SQL above will be ran on the client side i.e. not a stored procedure whereas other user's SQL WILL be typically ran on stored procs.

(tbh: I think I know the answer already as I've seen the result on old SQL server once before).

First off, what do you mean "SQL above will be ran on the client side"? Regardless of whether it is plain SQL or executing a stored proc, the SQL is run on the server.

Secondly, the locks involved will depend on the isolation level applied to the SQL being executed by any other users, how many records are involved in any updates, how many pages are affected and whether new pages must be created to accommodate an update.

Probably best you get a good DBA involved in montoring of lock objects and whether any lock hints are being applied in any of the sprocs running at the same time.

HTH

[WotNxt]

question: potential db lock up with Sybase SQL 'select' statement that takes long time to execute ....

here it is although almost irrelevant to my question:

"select a, b, c, d, SUM(e) as f from A where a = '080429' and appl b = 'something' and d = 111111
group by a, b ,c, d

So the question is with Sybase (and I don't have the version but am using 3.11 drivers), as with the usual readers/writers problem with lock outs in multi threading programming will Sybase then lock out all other users for the entire duration of my SQL statement that wish to perform a SQL write on table 'A' of my SQL above for the entire duration of my 'select' command above which is being executed ?

Or is Sybase a bit smarter than that ..?

remember my SQL above will be ran on the client side i.e. not a stored procedure whereas other user's SQL WILL be typically ran on stored procs.

(tbh: I think I know the answer already as I've seen the result on old SQL server once before).

No idea about Sybase but with SQL Server you could just add "WITH (NOLOCK)" and then you wouldn't be creating any locks.

e.g.

select a, b, c, d, SUM(e) as f from A WITH (NOLOCK) where a = '080429' and appl b = 'something' and d = 111111
group by a, b ,c, d

[eliquant]

cheers.. I've read up on NOLOCK and it looks like the right thing.

btw in sybase its just NOLOCK without the 'with'.

[Ruprect]

First off, what do you mean "SQL above will be ran on the client side"? Regardless of whether it is plain SQL or executing a stored proc, the SQL is run on the server.

Secondly, the locks involved will depend on the isolation level applied to the SQL being executed by any other users, how many records are involved in any updates, how many pages are affected and whether new pages must be created to accommodate an update.

Probably best you get a good DBA involved in montoring of lock objects and whether any lock hints are being applied in any of the sprocs running at the same time.

HTH

Blimey prawn, that was actually a helpful post. Are you feeling OK?